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



The Eucharistic Miracles of the World

  1. Create a PLSQL procedure
create or replace procedure pr_dtl (a in varchar2) is
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.

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_home
sqlplus scott/tiger > /home/oracle/cron/pr_dtl.log < EOF
exec pr_dtl('James');

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

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


Website Stats


Post a Comment


Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2) Copyright 2011-23 All Rights Reserved | Site Map | Contact | Disclaimer