Simpler Navigation for Servers and Operating Systems
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.
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?
2 REPLIES
Brian Crabtree
Honored Contributor

Re: CSH script for Oracle job

sqlplus will have to run the entire file, unless you define exit statments in your code to terminate.

Something like the following would probably work at the top of your "test.sql", and then enclose your program with a "if dbdown = 'N' then" statement.

declare
numcount varchar2(5);
dbdown varchar2(5);
begin
dbdown:='N';
select count(*) into numcount from dba_data_files;
exception
when others then
dbdown:='Y';
end;

Although, the only way you can login to the database when it is down from sqlplus is using "internal" or a "sysdba" account. You might not want to do this under most circumstances.

Brian
samd_1
Super Advisor

Re: CSH script for Oracle job

Not totally sure I understand. Are you saying to put the following in the SQL Plus script? How do I feed back the status to the shell script?