Parameterized View - Passing Parameters in Views

Oracle does not support parameters with Views, but we can always find a workaround. In the case of parameterized views there could be various workarounds. In this post I am giving a neat and simple example to create a parameterized view, it is my personal favorite workaround. I am using SCOTT schema for this example.

Lets first have a normal view

SQL> create view emp_dept_v as
  2  select e.empno, e.ename, d.deptno, d.dname, e.sal
  3  from emp e, dept d
  4  where e.deptno = d.deptno;
View created.

So far so good, Just one requirement to add. I want to have d.deptno passed as parameter in emp_dept_v view. For this requirement I would be using Package to give a clean interface for getting and setting parameter values.

SQL> create or replace package pkg_global_params as
  2
  3    g_deptno     dept.deptno%type;
  4
  5  end pkg_global_params;
  6  /
Package created.

SQL> create or replace package pkg_param_manager as
  2
  3    procedure set_deptno(p_deptno dept.deptno%type);
  4
  5    function get_deptno return dept.deptno%type;
  6
  7  end pkg_param_manager;
  8  /
Package created.

SQL> create or replace package body pkg_param_manager as
  2
  3    procedure set_deptno(p_deptno dept.deptno%type)
  4    as
  5    begin
  6      pkg_global_params.g_deptno := p_deptno;
  7    end;
  8
  9    function get_deptno return dept.deptno%type
 10    is
 11    begin
 12      return pkg_global_params.g_deptno;
 13    end;
 14
 15  end pkg_param_manager;
 16  /
Package body created.

pkg_param_manager is pretty simple. It has one procedure to set package level variable of pkg_global_params package and one function to retrieve it. I defined global variable and getter/setter code in separate packages to avoid the issue with package state.  Now we simply need to use pkg_param_manager package in our view definition to make it parameterized view as

SQL> create or replace view emp_dept_v as
  2  select e.empno, e.ename, d.deptno, d.dname, e.sal
  3  from emp e, dept d
  4  where e.deptno = d.deptno
  5  and d.deptno = nvl(pkg_param_manager.get_deptno, d.deptno);
View created.

To get the data from parameterized view, we need to first set the package level variable and then get the data from the view.

SQL> exec pkg_param_manager.set_deptno(10);
PL/SQL procedure successfully completed.

SQL> select * from emp_dept_v;
     EMPNO ENAME          DEPTNO DNAME                 SAL
---------- ---------- ---------- -------------- ----------
      7782 CLARK              10 ACCOUNTING           2450
      7839 KING               10 ACCOUNTING           5000
      7934 MILLER             10 ACCOUNTING           1300

Let's test our view for deptno 20
SQL> exec pkg_param_manager.set_deptno(20);
PL/SQL procedure successfully completed.

SQL> select * from emp_dept_v;
     EMPNO ENAME          DEPTNO DNAME                 SAL
---------- ---------- ---------- -------------- ----------
      7566 JONES              20 RESEARCH             2975
      7902 FORD               20 RESEARCH             3000
      7876 ADAMS              20 RESEARCH             1100
      7369 SMITH              20 RESEARCH              800
      7788 SCOTT              20 RESEARCH             3000

Looks great !!! What if we want to get all the records from the parameterized view? Remember the predicate in our parameterized view, we are filtering d.depto with nvl(pkg_param_manager.get_deptno, d.deptno). So we just need to set package variable to NULL to get all the records. Lets try

SQL> exec pkg_param_manager.set_deptno(NULL);
PL/SQL procedure successfully completed.

SQL> select * from emp_dept_v;
     EMPNO ENAME          DEPTNO DNAME                 SAL
---------- ---------- ---------- -------------- ----------
      7782 CLARK              10 ACCOUNTING           2450
      7839 KING               10 ACCOUNTING           5000
      7934 MILLER             10 ACCOUNTING           1300
      7566 JONES              20 RESEARCH             2975
      7902 FORD               20 RESEARCH             3000
      7876 ADAMS              20 RESEARCH             1100
      7369 SMITH              20 RESEARCH              800
      7788 SCOTT              20 RESEARCH             3000
      7521 WARD               30 SALES                1250
      7844 TURNER             30 SALES                1500
      7499 ALLEN              30 SALES                1600
      7900 JAMES              30 SALES                 950
      7698 BLAKE              30 SALES                2850
      7654 MARTIN             30 SALES                1250
14 rows selected.

It worked !!! I hope that you would like this little tweak to create parameterized view and have enjoyed reading this article. Feedback are well appreciated.

Related Posts:
- SQL Interview Question Answers
- Oracle Database 12c New Features for Developers
- Cursor with Parameters in Oracle

12 comments:

  1. You can use application context. I believe Oracle uses it for fine grain access. I used it in views and it worked nice. You have to set ctx value of course before you can retrieve data.

    ReplyDelete
  2. Nice work around thanq

    ReplyDelete
  3. In procedure, where p_deptno is input param:

    execute immediate '
    create or replace view emp_dept_v as
    select e.empno, e.ename, d.deptno, d.dname, e.sal
    from emp e, dept d
    where e.deptno = ' ||p_deptno;

    ReplyDelete
  4. Alexander PolivanyMay 12, 2016 at 4:00 PM

    It is more effective to use application contexts instead of global package variables, AFAIK. You can read about them here: http://docs.oracle.com/database/121/DBSEG/app_context.htm#DBSEG70071

    Oracle treats a SYS_CONTEXT call in the SQL statement as a bind variable. To achieve the same with your own function, you may use something like scalar subquery caching.

    ReplyDelete
    Replies
    1. This comment is completely true. Using the application context approach is the preferred (if not required) way to go. Note: it will change very little (nothing really) in the way the view and package are engaged with. But it will improve performance and reduce dependency on PL/SQL (including ORA-4068 error).

      kind regards,
      Lucas

      Delete
  5. Classic. One more thing: I think the function should be declared deterministic.

    ReplyDelete
  6. Narendra Reddy KandukuriMay 17, 2016 at 8:51 PM

    very simple example...

    ReplyDelete
  7. Nice one ! We are using the same mechanism to pulling/pushing data for different countries from the same table.

    ReplyDelete
  8. What about pipelined functions? they are parametrixed views ?

    ReplyDelete