Using a pipelined function for a “flexible” view.

I have come across a situation where I needed to have a view whose where clause can be changed without having to recompile the view.

This could be for an application provided to multiple customers, where the application needs to have a view whose where clause may need to be different from one customer to the next. Or you may want the customer to be able to change the where clause (or some other part of the view) without having him/her doing any DML. In order to do this I chose to use a pipelined function. This function constructs a cursor with dynamic SQL and then pipes the rows of the cursor back to the calling SELECT statement.

Example: I have a view which is defined in the following way:

CREATE OR REPLACE VIEW emp_vw AS
SELECT * FROM emp WHERE ename = 'KING';

Now you may want to change the where clause of this view to the following:

CREATE OR REPLACE VIEW emp_vw AS
SELECT * FROM emp WHERE ename = 'KING' or deptno = 20;

If you simply redefine the view, you may invalidate objects that reference the view. Also, you may not want the user to do any DDL and/or provide him with an easy interface to change the where clause on the fly.

I handled this situation by basing the EMP_VW view on a pipelined function with the following approach:

1. Create a table (EMP_WHERE) that holds the where clause for the view.
2. Create a pipelined function that constructs a REF CURSOR with the where clause from EMP_WHERE.
3. Create the view EMP_VW based on the pipelined function.

Please note that this example is very basic and may need additional functionality to be useful in a production environment. This code has been tested on Oracle 11.1.

1. Create the EMP_WHERE table:

CREATE TABLE emp_where (where_clause VARCHAR2(4000));
INSERT INTO emp_where (where_clause)
VALUES ('ename=''KING'' or deptno = 20');
commit;

1. Create the piplined function

This requires two Oracle user defined types: An object based on the EMP record, and a table based on the EMP record object.

create 
or 
replace type t_emp_rec as object 
( 
  empno    number(4), 
  ename    varchar2(10), 
  job      varchar2(9), 
  mgr      number(4), 
  hiredate date, 
  sal      number(7,2), 
  comm     number(7,2), 
  deptno   number(2) ); 
create 
or 
replace type t_emp_tab as table of t_emp_rec; 
create or replace function emp_fn 
  return t_emp_tab pipelined 
is 
  l_sql   varchar2(32767); 
  l_where varchar2(4000); 
type l_cur_type 
is 
  ref 
  cursor; 
    l_cur l_cur_type; 
    l_rec emp%rowtype; 
  begin 
    select where_clause 
    into   l_where 
    from   emp_where; 

l_sql := 'SELECT * FROM emp WHERE ' 
|| l_where; 
open l_cur for l_sql; 
loop 
  fetch l_cur 
  into  l_rec; 
   
  EXIT 
when l_cur%notfound; 
  pipe row(t_emp_rec(empno => l_rec.empno , 
                     ename => l_rec.ename , 
                     job => l_rec.job , 
                     mgr => l_rec.mgr , 
                     hiredate => l_rec.hiredate , 
                     sal => l_rec.sal , 
                     comm => l_rec.comm , 
                     deptno => l_rec.deptno)); 
end loop; 
return; 
exception 
when others then 
  raise_application_error(-20000, sqlerrm 
  || chr(10) 
  || l_sql); 
end;
/

1. Create the EMP_VW view

CREATE OR REPLACE VIEW emp_vw AS
SELECT * FROM TABLE(emp_fn);

You can now select from the view:

SELECT * FROM emp_vw;

Change the where clause and select again:

UPDATE emp_where SET where_clause = 'SAL BETWEEN 2000 AND 4000 AND DEPTNO != 10';
COMMIT;

SELECT * FROM emp_vw;

We now have completely changed the where clause without invalidating any objects.