Running an Oracle SQL script fron cron

[Tom Grill]

How do I execute a Oracle sql script as a cron job.

[Ivan Krastev]

Here is full example - http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=947212


regards,
ivan

[Coolmar]

If you want to run it from oracle's cron, make sure the oracle account is added to the cron.allow and then just add the script to the cron:

# crontab -e
# 00 00 * * * /script
# | | | | | |
# | | | | | script to run
# | | | | +---------- month
# | | | +------------ day
# | | +-------------- day of the week 0-6, 0 = sunday, 1-5 = mon to fri
# | +----------------- hour of the day 00 to 23
# +-------------------- minute 00 to 59

If you want to add it to root's crontab, do the following - same syntax as above but the script is different:

# 00 00 * * * su - oracle -c "/script"

[spex · Accepted solution]

Hi Tom,

Here's a basic template for running PL/SQL from within a shell script, which in turn can be scheduled to run via cron:

#!/usr/bin/sh
export ORACLE_SID=yoursid
export ORACLE_HOME=/path/to/home
export ORACLE_BIN=${ORACLE_HOME}/bin
export ORACLE_TERM=vt220
export NLS_LANG=AMERICAN_AMERICA.US7ASCII
export PATH=$PATH:${ORACLE_BIN}
sqlplus -S "un/pw" << _EOF_
set heading off;
select * from v$instance;
exit;
_EOF_
exit 0

Place your PL/SQL code between the _EOF_'s, save the resulting shell script, and schedule it to run via 'crontab -e '. Make sure appears in /var/adm/cron/cron.allow.

PCS

[Samir Pujara_1]

Tom,

Simple rule is that you need to define all the veriable and path used to execute your sqlplus statement. So only thing you need to do is define all veriables like ORACLE_HOME, ORACLE_SID and add ORACLE_HOME/bin in path.

Thats it. The script which runs under oracle login will work fine from cron as well with above minor changes.

Samir

[Yogeeraj_1]

hi,

if it is just a sql statement, i would prefer to use DBMS_JOB or DBMS_SCHEDULER (10g)

you are guarantee that the job will be run. Say you had scheduled the job to run at 7:00 am everyday. One day, you have an intervention during which the database downtime that has gone beyond 7:00 am. The database is up again at 7:05am. If scheduled using cron, it never gets run which which dbms_job or dbms_scheduler, the job run as soon as the database is up!


if you need any further assistance, do let us know.

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

If you want to use the CRON, one simple example is:

more /home/yogeeraj/testscript.sh
================================
#!/bin/sh
export PATH
unset USERNAME
export ORACLE_BASE=/d01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_2
export ORACLE_SID=mydb
export PATH=$PATH:$ORACLE_HOME/bin:/usr/X11R6/bin
umask 022
#
sqlplus /nolog << EOF
connect yogeeraj/password
select sysdate from dual;
EOF
#End script: /home/yogeeraj/testscript.sh
================================

and now in your crontab:


#*******************************************************************************
# min|hour |day |month|day |script
# | |of mo| |of wk|
#----|-----|-----|-----|-----|--------------------------------------------------
#*******************************************************************************

37 15 * * * echo "/home/yogeeraj/testscript.sh" | su - oracle10g 1>/home/yogeeraj/logfiles/output-testscript.crn 2>/home/yogeeraj/logfiles/error-testscript.crn
#*******************************************************************************
# END OF TABLE day0->Sunday day6->Saturday
#*******************************************************************************


hope this helps!

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

if you want to run a procedure (abc;) or package (you may as well write a plsql block there) using DBMS_JOB:

declare
l_job number;
begin
dbms_job.submit( l_job,
'abc;',
trunc(sysdate)+4/24,
'trunc(sysdate)+1+4/24' );
end;

will run the job at 4am every day.

make sure to set job_queue_processes and job_queue_interval (init.ora
parameters).

hope this helps too!

kind regards
yogeeraj

[Tom Grill]

I now have my sql script running as a cron job. How do I ouput only the results of my query (including the header) and get each record to appear on a single line?

[Peter Godron]

Tom,
look at the SQL spool command:
SQL>spool /tmp/a.lis
.
.
SQL>spool off

See:
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=303547
and
http://www.experts-exchange.com/Databases/Oracle/Q_21533352.html
and
http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a90842/ch7.htm#1005594

Other things to look at:
set linesize xxxxx
set pagesize xxxx