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

Maintain shell script log in CSV

Suppose you want to log the start and end of your shell script execution. Also you want to know the total execution time. Then you should do the followings -

1. Put the start time in a variable at beginning of script;
   command - export start_date=`date +%m/%d/%Y,%H:%M:%S`.
   Be careful and use `` this operator to put the date function in a variable. Use the proper date format string as you need.
   
   
2. Put the end in a variable at the end of script;
   command - export end_date=`date +%m/%d/%Y,%H:%M:%S`.
   
   
3. Write the log and separate the field using ','.
   echo 'start,' $start_date ',end,' $end_date >> scriptexlog.csv
   
   
   

This is so simple!!