topic Re: Running an Oracle SQL script fron cron in Operating System - HP-UX

Running an Oracle SQL script fron cron

Tom Grill — Tue, 05 Dec 2006 12:30:12 GMT

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

Re: Running an Oracle SQL script fron cron

Ivan Krastev — Tue, 05 Dec 2006 12:34:21 GMT

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

regards,
ivan

Re: Running an Oracle SQL script fron cron

Coolmar — Tue, 05 Dec 2006 13:09:25 GMT

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"

Re: Running an Oracle SQL script fron cron

spex — Tue, 05 Dec 2006 13:37:14 GMT

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

Re: Running an Oracle SQL script fron cron

Samir Pujara_1 — Wed, 06 Dec 2006 00:49:19 GMT

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

Re: Running an Oracle SQL script fron cron

Yogeeraj_1 — Wed, 06 Dec 2006 01:07:56 GMT

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

Re: Running an Oracle SQL script fron cron

Yogeeraj_1 — Wed, 06 Dec 2006 01:16:40 GMT

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

Re: Running an Oracle SQL script fron cron

Yogeeraj_1 — Wed, 06 Dec 2006 01:23:13 GMT

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

Re: Running an Oracle SQL script fron cron

Tom Grill — Tue, 12 Dec 2006 14:09:43 GMT

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?

Re: Running an Oracle SQL script fron cron

Peter Godron — Wed, 13 Dec 2006 03:17:31 GMT

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

Re: Running an Oracle SQL script fron cron

Yogeeraj_1 — Wed, 13 Dec 2006 04:02:35 GMT

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

Re: Running an Oracle SQL script fron cron

Tom Grill — Thu, 21 Dec 2006 14:42:58 GMT

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?

Re: Running an Oracle SQL script fron cron

Yogeeraj_1 — Fri, 22 Dec 2006 00:43:35 GMT

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

Re: Running an Oracle SQL script fron cron

spex — Fri, 22 Dec 2006 07:44:54 GMT

Hi Tom,

Try 'set head on'.

PCS

Re: Running an Oracle SQL script fron cron

adithya7 — Thu, 21 Jan 2016 12:50:32 GMT

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