Shell Script to Execute Oracle Sql Script in Crontab for a Specified SID


The Eucharistic Miracles of the World
For general shell script to execute SQL statements does not require the environment as those required parameter might be set already for the sessions. But when it comes to Crontab job, script will run independently from other sessions and it needs all the required parameter to be set inside the script.
What are the required parameters to run a SQL in a Crontab shell script?

1.   Oracle SID
export ORACLE_SID=
2.   Oracle Base(optional) & Oracle Home
3.   Set the Path for sqlplus executable or directly call the sqlplus from ORACLE_HOME/bin directory.
e.g: export PATH=/u00/app/oracle/product/
Call sqlplus with absolute path
Create a sql script with the commands to be executed.
$ vi gath.sql
set echo on
set feedback on
set timing on
set time on
select name from v$database;

Create a shell script with required parameters
$ vi
export ORACLE_SID=transprd
export ORACLE_BASE=/u00/app/oracle
export ORACLE_HOME=/u00/app/oracle/product/
export PATH=/u00/app/oracle/product/
sqlplus scott/tiger << EOF > /home/oracle/st/gath.log

Add a crontab entry using crontab –e command with required execution timings.
14 11 * * * /home/oracle/st/ > /home/oracle/st/cron.log


Website Stats


Post a Comment Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer