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


  1. Create a PLSQL procedure
SQL>
create or replace procedure pr_dtl (a in varchar2) is
begin
dbms_output.put_line('Name:'||a);
end;
/
Procedure created.


  1. 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

  1. Change the permissions of the shell script to executable one.
chmod 744 crn.sh

  1. 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

  1. 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

  1. 6. You can see the log file content whether it is success or not.
$cat pr_dtl.log

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

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


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.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google