select * from table(pf_demo(10));This way it is not possible to wrap the parameterised pipelined function in a view. You can only create a view for a specific parameter value:
create view pf_demo_view as select * from table(pf_demo(10));This is not very useful. Luckily there is a way to pass parameters to a pipelined function within a view. This solution uses package variables to store the parameter values and setter functions to set the values.
In our example we create the pipelined function in a package pf_demo_pkg. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. In the case of NULL-arguments the values of package variables are used. We create the view with a call to the pipelined function without parameters. The view can be queried with a call to the setter function(s) in the where clause. This way you can query the view from PL/SQL.
create view pf_demo_view as select * from table(pf_demo());
begin
pf_demo_pkg.set_dept(10);
for r in ( select * from pf_demo_view ) loop
...
end loop;
end;
Now there is also a solution to use such a view from SQL. This solution utilizes the fact that the where clause of a query is interpreted before the from clause. So when we call the setter(s) in the where-clause the view will pass these parameters to the piplined function. You can see this in action in the code below:SQL
SQL create type emp_rowtype
2 as object
3 ( empno number
4 , ename varchar2(10)
5 , hiredate date
6 , deptno number
7 );
8 /
Type created.
SQL
SQL create type emp_table_type as table of emp_rowtype;
2 /
Type created.
SQL
SQL
SQL create or replace package pf_demo is
2
3 function set_deptno ( p_deptno in number) return number;
4
5 function emp_pf (p_deptno in number default null)return emp_table_type pipelined;
6
7 end;
8 /
Package created.
SQL
SQL create or replace
2 package body pf_demo is
3
4 g_deptno number := null;
5
6 function set_deptno ( p_deptno in number) return number is
7 begin
8 g_deptno := p_deptno;
9 return(p_deptno);
10 end;
11
12 function emp_pf (p_deptno in number default null)return emp_table_type pipelined is
13 cursor c_emp (cp_deptno number) is
14 select empno
15 , ename
16 , hiredate
17 , deptno
18 from emp
19 where deptno = cp_deptno
20 ;
21 r_emp c_emp%rowtype;
22 r_rec emp_rowtype;
23 l_empno number := null;
24 l_ename varchar2(10) := null;
25 l_hiredate date := null;
26 l_deptno number := null;
27 begin
28 l_deptno := nvl(p_deptno,g_deptno);
29 open c_emp(l_deptno);
30 loop
31 fetch c_emp into l_empno
32 , l_ename
33 , l_hiredate
34 , l_deptno
35 ;
36 exit when c_emp%notfound;
37 r_rec := new emp_rowtype
38 ( l_empno
39 , l_ename
40 , l_hiredate
41 , l_deptno
42 );
43 pipe row (r_rec);
44 end loop;
45 close c_emp;
46 return;
47 end;
48
49 end pf_demo;
50 /
Package body created.
SQL
SQL
SQL select * from table(pf_demo.emp_pf(10));
EMPNO ENAME HIREDATE DEPTNO
---------- ---------- --------- ----------
7782 CLARK 09-JUN-81 10
7839 KING 17-NOV-81 10
7934 MILLER 23-JAN-82 10
SQL
SQL select * from table(pf_demo.emp_pf()) where pf_demo.set_deptno(20) is not null;
EMPNO ENAME HIREDATE DEPTNO
---------- ---------- --------- ----------
7369 SMITH 17-DEC-80 20
7566 JONES 02-APR-81 20
7788 SCOTT 09-DEC-82 20
7876 ADAMS 12-JAN-83 20
7902 FORD 03-DEC-81 20
SQL
SQL create view pf_demo_view as select * from table(pf_demo.emp_pf());
View created.
SQL
SQL select * from pf_demo_view where pf_demo.set_deptno(30) is not null;
EMPNO ENAME HIREDATE DEPTNO
---------- ---------- --------- ----------
7499 ALLEN 20-FEB-81 30
7521 WARD2 22-FEB-81 30
7654 MARTIN 28-SEP-81 30
7698 BLAKE 01-MAY-81 30
7844 TURNER 08-SEP-81 30
7900 JAMES 03-DEC-81 30
6 rows selected.
SQL
SQL spool off
Hope you can use this, happy coding
Dick
