Returning Error Codes from SQLPLUS

Submitted by dave on Thu, 04/19/2007 - 06:42

There may come a time when you need to return an error code from SQLPlus, either to a calling batch file, shell script, or Perl script.

SQLPlus has a WHENEVER directive available for handling errors it encounters. This command controls the behavior of SQLPlus when an OS or SQL error occurs. There are many options for this behavior.

More after the jump…

SQL errors include errors thrown by a single command entered into SQLPlus or an error raised by a PL/SQL block:

OS Errors include errors because of disk space and other OS type errors. This is not a common problem but can still cause a script run to fail:

10g Documentation for WHENEVER SQLERROR:
here

But alas all is not well. There are a certain class of errors (SP2 errors) that are thrown by SQLPLUS that are thrown by SQLPLUS but not caught nicely. These errors can only be caught by examining the output of SQLPLUS. More on that in future posts.

-- Dave

Tags: 
oracle
sqlplus

Comments

Submitted by Mastan (not verified) on Mon, 10/28/2013 - 11:25.

Hi dmann,

Kindly suggest me how can we handle SP2 errors.

Thanks & Regards,
Mastan

Submitted by dave on Wed, 01/29/2014 - 00:18.

Hi Mastan,

That is a great question. Sorry I never wrote my follow up post.

My method for catching SP2 errors is via analyzing the output with a text search tool.

In the Unix world this is usually with a pass of the GREP utility. I have a shell script that wraps the calls to SQLPlus. It uses GREP with a regular expression to trap the SP2-nnnn error text if it appears in the output log. Then my shell script acts

Oracle 11g SQLPlus does have a new error logging feature which writes errors to a table. This table can then be analyzed for errors including SP2 errors. More info is available here in Tom Kyte's blog post .

Add new comment