Determine Return code for SQL Script invoked using a KSH script

[Milleniumlegend]

I would like to find out the return code for the SQL Script that has been invoked using a ksh unix script.

I am invoking sql plus from ksh and then passing the *.sql file to the sql plus. How can I determine if the *.sql script execute successfully.

If I use an $? at the time of invoking sql plus I will get the return code for sql plus and not for the script.

Any information would helpful.

Thanks

 

[PHV]

Take a look here:

http://www.tek-tips.com/faqs.cfm?fid=2218

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Milleniumlegend]

I have a list of *.sql files that I have to run. What I was looking for was execute a sql script using ksh and pass that to sql*plus. If the sql file runs without errors I get a return prompt saying it has executed successfully or failed.

Could you please provide some code snippets if you have any.

Appreciate your help.

 

[johnherman]

Maybe try one of the Oracle forums?

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[stefanhei]

Add

WHENEVER SQLERROR EXIT FAILURE

to your sqlscripts. $? will return 1 in case of errors.

PS.: this is SQL*Plus specific and won't work with other clients.

Stefan

 

[Milleniumlegend]

Thanks a lot stefanhei. I did try that and it works as needed. Thanks PHV for your help. The information you provided was indeed helpful.

I realised that if we use oracle errors that gives us error codes in excess of 255 which would be a problem because on Solaris OS v8 it does not accept error code more than 255 so we have to manually specify the error codes for that in the sql script that we run.

Also could you please tell me if I am able to just get the error codes output to the file with the date and time stamp on the file.

For Example:
The following error:

select * From *
*
ERROR at line 1:
ORA-00903: invalid table name