How to run SQLscripts using crontab?

Suppose that you want to run your stored procedure at the particular time on your Oracle database.

Create the following file (my_job.sh) under your home directory and don't forget to change execute permission on this file:

#!/bin/ksh

export ORACLE_SID=

export ORACLE_HOME=

export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib

export TMPDIR=/tmp

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PATH=$ORACLE_HOME/bin:.

sqlplus -s << EOF < your_username > / < your_password >

exec ;

exit
EOF

Warning! - set correct ORACLE_SID and ORACLE_HOME.

Schedule this job using crontab - see reference on crontab

For example the following listing of crontab shows that my_job.sh will run at 5:00:AM every day:

#--------------------------------------------------------------
# min hour daymo month daywk cmd
#--------------------------------------------------------------
# My Job
#--------------------------------------------------------------
0 5 * * * /home/oracle/my_job.sh
#*****************************************************************