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:

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

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:

  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.

  1. Create the EMP_VW view

You can now select from the view:

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:

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.

5 thoughts on “Using a pipelined function for a “flexible” view.

  • December 13, 2011 at 12:21 pm
    Permalink

    Nicely done and nicely explained. Thanks for posting this.

    Reply
  • Pingback:Query up a list of files from OS directory « Christoph's 2 Oracle Cents

  • July 5, 2013 at 6:39 am
    Permalink

    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

    Reply
    • July 5, 2013 at 1:08 pm
      Permalink

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

      Reply
      • July 6, 2013 at 12:51 am
        Permalink

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

        Reply

Leave a Reply

Your email address will not be published. Required fields are marked *