Using a pipelined function with parameters in a view

In the Oracle database you can use pipelined functions to improve performance or decrease complexity. With the table function a pipelined functions can be called:

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