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

advertisements

_____________________________________________________________________________________________________________________

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

export ORACLE_BASE=

export 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/11.2.0.3/db_1/bin:.

Or

Call sqlplus with absolute path

/u00/app/oracle/product/11.2.0.3/db_1/bin/sqlplus

Example

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 sql.sh

#!/bin/bash
export ORACLE_SID=transprd

export ORACLE_BASE=/u00/app/oracle

export ORACLE_HOME=/u00/app/oracle/product/11.2.0.3/db_1

export PATH=/u00/app/oracle/product/11.2.0.3/db_1/bin:.

sqlplus scott/tiger << EOF > /home/oracle/st/gath.log

@/home/oracle/st/gath.sql

exit

EOF

Add a crontab entry using crontab –e command with required execution timings.

14 11 * * * /home/oracle/st/sql.sh > /home/oracle/st/cron.log

_____________________________________________________________________________________________________________________

Website Stats