Operating System - HP-UX
1745853 Members
4455 Online
108723 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?
2 REPLIES 2
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?