Oracle Parameterized View

Oracle has this feature because sometimes parameterized view can increase the performance because here predicate are compiled within the view; thus for this predicate the query will be always soft parsed.

Here is a simple example -

create view myview as

select * from t where x = userenv('client_info');


Here in this view definition, user's default namespace "userenv " and
its attribute "client_info" is used. To retrieve data from this view
you have to set this attribute by execute -

exec dbms_application_info.set_client_info
('client_info_value') ;

Some remarks on Oracle context -

1. "Userenv" is the default namespace or context.
   You can create your own context also if you have the privilege.
   
   
2. Every context(namespace) has a package within which
   you can write the code to set any attribute of your context.
   You cannot set the attribute outside the package.
   If you want to do it outside you will get error -
   ORA-01031: insufficient privileges.
   
   
3. To retrieve data from this package, we should use sys_context function.

So, you can use sys_context function also for parameterized view by calling
this function in view definition and before query the data set
the attribute used in view definition. Thus you can easily write view like user_tables -

select * from dba_tables
where owner = sys_context('USERENV', 'SESSION_USER');

Related links -
http://forums.oracle.com
http://asktom.oracle.com