Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

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
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
Occasional Visitor

Re: CSH script for Oracle job

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