How to Run / Schedule a PLSQL Procedure In Shell Script and Crontab?

advertisements

_____________________________________________________________________________________________________________________

SQL>

create or replace procedure pr_dtl (a in varchar2) is

begin

dbms_output.put_line('Name:'||a);

end;

Procedure created.

Create a Shell script with proper Oracle mandatory environment variables like ORACLE_HOME, ORACLE_SID and PATH variables. Sometimes you have to add LIBRARY_PATH and etc. Because when you run from the cron it is considered as a new session and where you have to set all environment parameters. Then add the procedure execute statement with sqlplus command.

$vi crn.sh

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home

export ORACLE_SID=DEVDB21

export PATH=$PATH:$ORACLE_HOME/bin

sqlplus scott/tiger > /home/oracle/cron/pr_dtl.log < EOF

exec pr_dtl('James');

EOF

chmod 744 crn.sh

Add a crontab entry with proper timings. In below example I have scheduled the script for everyday 2:51 AM in the morning.

crontab –e

51 2 * * * /home/oracle/cron/crn.sh > /home/oracle/cron/cronexec.out

Now you can see the logs in the /home/oracle/cron directory after successful execution.

-rw-r--r-- 1 oracle oinstall 654 May 22 02:51 pr_dtl.log

-rw-r--r-- 1 oracle oinstall 0 May 22 02:51 cronexec.out

$cat pr_dtl.log

SQL*Plus: Release 11.2.0.2.0 Production on Tue May 22 02:51:01 2012

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

SQL> SQL> Name:James

PL/SQL procedure successfully completed.

_____________________________________________________________________________________________________________________

Archive