How to store views related to package-CodePudding

I have a package which I use to create some tables based on data in other tables. At the end of that package, I query the information I need via views I have already created. However, I have a bunch of views and I would like to keep them in order somewhere in that package or that has relation to that package. What would be the way to achieve this?

CodePudding user response：

If you want to keep them "somewhere in the package", then it would be a comment you'd write on your own and take care about maintaining it.

Another - let's call it "dynamic" option - is to query user_dependencies any time you want. Here's an example:

This is a view:

SQL> create view v_emps as
  2    select d.dname, e.ename, e.job
  3    from emp e join dept d on e.deptno = d.deptno;

View created.

Package that does something with the view:

SQL> create or replace package pkg_test
  2  as
  3    procedure p_test;
  4  end;
  5  /

Package created.

SQL> create or replace package body pkg_test
  2  as
  3    procedure p_test is
  4      l_cnt number;
  5    begin
  6      select count(*)
  7        into l_cnt
  8        from v_emps;
  9    end;
 10  end;
 11  /

Package body created.

Query user_dependencies:

SQL> select name, referenced_name, referenced_type
  2  from user_dependencies
  3  where name = 'PKG_TEST'
  4    and referenced_owner = 'SCOTT';

NAME                           REFERENCED_NAME      REFERENCED_TYPE
------------------------------ -------------------- ------------------
PKG_TEST                       PKG_TEST             PACKAGE
PKG_TEST                       V_EMPS               VIEW

SQL>

If you want to restrict it to views only, add yet another condition (that would be line #5 in query):

and referenced_type = 'VIEW'

CodePudding user response：

Views and packages are atomic Oracle objects and cannot be created inside each other.

However Oracle offers another select only PL/SQL "view like" structure - the pipelined function. This may prove useful if grouping of "views" is important in your use case.

You can create many pipelined functions within a single package, each function conceptually creating a different "view". A pipelined function can be referenced in a normal select statement by wrapping it in a table() statement as shown in the example below.

To illustrate I have created a very simple package called "views" which contains a single pipelined function "customers" which returns the customer ids and customer names from the customer table. I could of course include other pipelined functions in my grouping "views" package eg. "invoices", "sales" etc etc. (these functions can also take parameters like any normal package function if desired)

Here follows the simple illustration:

A. create your package specification for your pipelined table "customer view"

create or replace package views as

type t_cust_rec is record(id            customer.id%type
                         ,name          customer.name%type
                         );                   
type t_cust_cur is ref cursor return t_cust_rec;
type t_cust_tab is table of t_cust_rec;

function customers
         return t_cust_tab pipelined;

function sales ....;

function invoices ...;

end views;

B. create your package body for your pipelined table "customer view"

create or replace package body views as

function customers
         return t_cust_tab pipelined
         is 
  sRows t_cust_cur;
  rRow  t_cust_tab;
begin
  open sRows for
    select id
          ,name
    from customer;
  loop
    fetch sRows into rRow;
    exit when sRows%notfound;
    pipe row (rRow);
  end loop;
end;

function sales ....

function invoices ...

end views;

C. use your pipelined table function in a normal select statement

select *
from table(views.customers)
where name like 'Bob%';

select *
from table(views.sales)
where amount > 10000.00;

select sum(total)
from table(views.invoices);