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:

  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;

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;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-Dec-80 800 20
7566 JONES MANAGER 7839 02-Apr-81 2975 20
7788 SCOTT ANALYST 7566 19-Apr-87 3000 20
7839 KING PRESIDENT 17-Nov-81 5000 10
7876 ADAMS CLERK 7788 23-May-87 1100 20
7902 FORD ANALYST 7566 03-Dec-81 3000 20

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;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7566 JONES MANAGER 7839 02-Apr-81 2975 20
7698 BLAKE MANAGER 7839 01-May-81 2850 30
7788 SCOTT ANALYST 7566 19-Apr-87 3000 20
7902 FORD ANALYST 7566 03-Dec-81 3000 20

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

Advertisements

5 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Nicely done and nicely explained. Thanks for posting this.

  2. […] retrieve directory information, and then writes the file details to a database table. I then used a pipelined function to call the Java code. The piplined function makes it possible to then use the function […]

  3. this post is one of the best ever on the all oracle blogs.
    but there is a problem
    lots of users accessing the view with different where clause and values specially using application/Bind Variables! how it will handle? can you please share the idea

    • Thanks Adnan. No need to worry about multiple users. Apex will handle the individual requests with session state.

      • can you please elaborate or share post for me and all to understand in a better way


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: