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

[Yogeeraj_1]

hi tom,

> How do I ouput only the results of my query (including the header) and get each record to appear on a single line?


The results of your query should be sent to a file which you can use later.

the command that you must include in your script is:

spool /
...
spool off

For each record to appear on a single line, you should use the command:
set linesize


hope this helps!

kind regards
yogeeraj

[Tom Grill]

My report spools fine with one exception. I'd like for the header to be saved. I did try 'set heading on' but that didn't work. If I output my select statement without spooling, the header appears. Any advice?

[Yogeeraj_1]

try to include the line:

set pagesize 1000

other setting that you may wish to look into that i usually use in our scripts are:
SET ARRAYSIZE 1
SET FEEDBACK off
SET SERVEROUT on
SET PAGESIZE 66
SET NEWPAGE 6
SET LINESIZE 75
SET PAUSE off
SET VERIFY off



hope this helps!

kind regards
yogeeraj

[spex]

Hi Tom,

Try 'set head on'.

PCS

[adithya7]

Hello Yogeeraj,

I want to schedule a cronjob to compile a package on every day at 7:00 AM with specified user.

Details:

Username : apps

pwd : Deployed#777

ALTER PACKAGE APPS.EQU_BIS_PK COMPILE

Please provide me the cronjob for the above details.

Thanks & Regards,

Adithya