Operating System - HP-UX
1745793 Members
3667 Online
108722 Solutions
New Discussion юеВ

CSH script for Oracle job

 
samd_1
Super Advisor

CSH script for Oracle job

Running a cron job. However I need to figure out how to change it so that it detects any Oracle errors and exits the script or goes to a certain part if an Oracle error occur. The file included shows the part of the script I need modified. The problem is when the sqlplus script "test" runs(which actually just executes a procedure), and fails with an Oracle error because the database was down for instance, the script continues on instead of echoing "ERROR - Could not run test process". I'd like the script to do this just like it works when the return_cd($status) is not 0. What do I need to add to the if statement to check for the results of sqlplus statement?
3 REPLIES 3
VanZandt, David
Occasional Advisor

Re: CSH script for Oracle job

Your file did not appear, so regrets, I'm in the dark on specifics such as your version level, shell, etc. Using OS 10.20 & 11.0, with RDBMS 7.34 to 8i, I employ the SQLPlus qualifiers WHENEVER SQLERROR EXIT FAILURE, or less often WHENEVER OSERROR... -- thus the SQL statement returns a fatal status value when control pops back to the calling shell script. There it's a simple check to branch on the status, such as IF ( $? .ne 0)....

As you may expect, the parameters are robust and well documented. The following snippet is found in one of the Metalink forum messages, as another example.

echo `$ORACLE_HOME/bin/sqlplus -s / <whenever oserror exit sql.sqlcode commit
whenever sqlerror exit sql.sqlcode commit
alter database backup controlfile to '$ORABCK/control_01.ctl' reuse
exit;
EOF3` > log.lst
samd_1
Super Advisor

Re: CSH script for Oracle job

I'll have to check out Metalink to see.

Here is part of the shell script that runs the SQL Plus script

echo "Processing test extract..."
sqlplus ${userid} @${sql_dir}/test
set return_cd = $status
if (${return_cd} != 0) then
echo "Could not process test extract."
exit 1
endif
Raju_6
New Member

Re: CSH script for Oracle job

You should add an EXCEPTION clause in your main PL/SQL block to capture the ORACLE error.