Using a pipelined function for a “flexible” view.August 11, 2010 at 10:03 pm | Posted in Oracle Developement | 5 Comments
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:
- Create a table (EMP_WHERE) that holds the where clause for the view.
- Create a pipelined function that constructs a REF CURSOR with the where clause from EMP_WHERE.
- 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;
2. 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; /
3. 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.