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