Oracle parameterized views

Parmeterized views are different from normal oracle views as predicates are pushed down into the view which has two benefits:-

1) Increase performance in some cases.
2) No need to write application code to append predicates. You still need to wite some code to set paremeters either in application or in database depending upon the design.

There are two methods to setup parameterized view. Choice of method once again depends on your design/requirements:-

1) Using dbms_application_info package:- You need not to create your own CONTEXT to use this method. You can use 'userenv' namespace available by defualt in oracle. You use dbms_application_info package to set CLIENT_INFO attribute and retrive this value using 'userenv' or 'sys_context' function to create predicates in view. In this method You can set different values for CLIENT_INFO in different sessions but you have only one attribute to set and use per session at any specific time. There is no way for you to create parameterized view which requires more than one runtime parameters in this method.This is the only option available for oracle versions earlier than oracle 8i. SYS_CONTEXT function is also available only in Oracle 8i or later versions.

2) Using dbms_session package:- You need to create your own CONTEXT and package to set/clear attributes in your CONTEXT. You specify package to be used to access CONTEXT while creating CONTEXT. Package uses dbms_session package to create procedures/functions to set/clear values in your CONTEXT.You can create as many attibutes as required in your conetxt and hence it is possible to create parameterized view which requires more than one runtime parameters in this method.This method is available only for oracle 8i or later versions.

We will see both methods in action now:-

Setup:-
We will create EMP table in our schema for our example.

create table emp as select * from scott.emp;

1) Using dbms_application_info package:-

architk@TESTDB10g>create or replace view emp_par
2 as
3 select
4 * from
5 emp
6 where empno=to_number(userenv('client_info'));
View created.

architk@TESTDB10g>exec dbms_application_info.SET_CLIENT_INFO(7839);
PL/SQL procedure successfully completed.

architk@TESTDB10g>select * from emp_par;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------ ----------
COMM DEPTNO
---------- ----------
7839 KING PRESIDENT 17-Nov-81 00:00:00 5000
10

Note:- You can use 'sys_context' function instead of 'userenv' as follows:-

architk@TESTDB10g>create or replace view emp_par
2 as
3 select
4 * from
5 emp
6 where empno=to_number(sys_context('userenv','client_info'));
View created.

2) Using dbms_session package:-

-- Create CONTEXT associated with a package you want to use to maintain CONTEXT

architk@TESTDB10g>create or replace context testcontext using testcontext_pkg;
Context created.

--- Create package using dbms_session procedures/functions

architk@TESTDB10g>create or replace package testcontext_pkg
2 as
3 procedure set_value(v_name IN varchar2,v_value IN varchar2);
4 function get_value(v_name varchar2) return varchar2;
5 procedure clear_value(v_name IN varchar2);
6 end testcontext_pkg;
7 /
Package created.

architk@TESTDB10g>create or replace package body testcontext_pkg
2 as
3 procedure set_value(v_name IN varchar2,v_value IN varchar2) is
4 begin
5 dbms_session.SET_CONTEXT('TESTCONTEXT',v_name,v_value);
6 end set_value;
7 function get_value(v_name varchar2) return varchar2 is
8 v_value varchar2(4000);
9 begin
10 v_value:=sys_context('TESTCONTEXT',v_name);
11 return v_value;
12 end get_value;
13 procedure clear_value(v_name IN varchar2) is
14 begin
15 dbms_session.CLEAR_CONTEXT('TESTCONTEXT',null,v_name);
16 end clear_value;
17 end testcontext_pkg;
18 /
Package body created.

-- Create paramterized view using CONTEXT attibutes
architk@TESTDB10g>create or replace view emp_par
2 as
3 select
4 * from
5 emp
6 where job=testcontext_pkg.get_value('job'))
7 and deptno=to_number(sys_context('testcontext','deptno'));
View created.

-- Set attibutes in CONTEXT
architk@TESTDB10g>exec testcontext_pkg.set_value('job','SALESMAN');
PL/SQL procedure successfully completed.

architk@TESTDB10g>exec testcontext_pkg.set_value('deptno','30');
PL/SQL procedure successfully completed.

-- Select data from parameterized view
architk@TESTDB10g>select * from emp_par;
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------ ----------
COMM DEPTNO
---------- ----------
7499 ALLEN SALESMAN 7698 20-Feb-81 00:00:00 1600
300 30
7521 WARD SALESMAN 7698 22-Feb-81 00:00:00 1250
500 30
7654 MARTIN SALESMAN 7698 28-Sep-81 00:00:00 1250
1400 30
EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- --------- ---------- ------------------ ----------
COMM DEPTNO
---------- ----------
7844 TURNER SALESMAN 7698 08-Sep-81 00:00:00 1500
0 30

-- Clear attibutes in CONTEXT
architk@TESTDB10g>exec testcontext_pkg.CLEAR_value('deptno');
PL/SQL procedure successfully completed.

architk@TESTDB10g>exec testcontext_pkg.CLEAR_value('job');
PL/SQL procedure successfully completed.

architk@TESTDB10g>select * from emp_par;
no rows selected

Going ahead:-

Examples here are just to show basic things required to create parameterized view. There are other things to be considered (like connection pooling, accesing remote table using contexts) before deciding upon the implementation. Please refer to oracle documentation or following links to get more idea about these concepts:-
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1448404423206
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:906341500346611919
http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/toc.htm