Skip Headers
Pro*PL/1® Supplement to the Oracle Precompilers Guide
10g Release 2 (10.2)

Part Number B14353-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

2 Error Handling and Diagnostics

This chapter discusses error reporting and recovery. You learn how to handle errors and status changes using SQLSTATE, the SQLCA, SQLCODE and the WHENEVER statement. You also learn how to diagnose problems using the ORACA.

SQLSTATE, the SQLCA, and SQLCODE

Release 1.6 provides forward and backward compatibility with regard to checking the outcome of executing SQL statements. The SQLCA data structure containing status information and SQLCODE status variable can be used in the same manner as in previous releases. The SQLSTATE status variable is introduced in release 1.6.

Declaring SQLSTATE

When MODE=ANSI, you must declare SQLSTATE or SQLCODE. Declaring the SQLCA is optional. When MODE=ORACLE, not declaring the SQLCA causes compile time warnings and runtime errors.

Unlike SQLCODE, which stores signed integers and can be declared outside the Declare Section, SQLSTATE stores 5-character strings and must be declared inside the Declare Section. You declare SQLSTATE as:

DCL SQLSTATE CHAR(5);

Note:

SQLSTATE must be declared with exactly 5 characters.

SQLSTATE Values

SQLSTATE status codes consist of a 2-character class code followed by a 3-character subclass code. Aside from class code 00 ("successful completion"), the class code denotes a category of exceptions. And, aside from subclass code 000 ("not applicable"), the subclass code denotes a specific exception within that category. For example, the SQLSTATE value '22012' consists of class code 22 ("data exception") and subclass code 012 ("division by zero").

Each of the five characters in a SQLSTATE value is a digit (0..9) or an uppercase Latin letter (A..Z). Class codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined conditions (those defined in SQL92). All other class codes are reserved for implementation-defined conditions. Within predefined classes, subclass codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined subconditions. All other subclass codes are reserved for implementation-defined subconditions. shows the coding scheme.

Figure 2-1 SQLSTATE Coding Scheme

SQLSTATE coding scheme
Description of "Figure 2-1 SQLSTATE Coding Scheme"

Table 2-1 shows the classes predefined by SQL92.

Table 2-1 Predefined Classes

Class Condition

00

success completion

01

warning

02

no data

07

dynamic SQL error

08

connection exception

0A

feature not supported

21

cardinality violation

22

data exception

23

integrity constraint violation

24

invalid cursor state

25

invalid transaction state

26

invalid SQL statement name

27

triggered data change violation

28

invalid authorization specification

2A

direct SQL syntax error or access rule violation

2B

dependent privilege descriptors still exist

2C

invalid character set name

2D

invalid transaction termination

2E

invalid connection name

33

invalid SQL descriptor name

34

invalid cursor name

35

invalid condition number

37

dynamic SQL syntax error or access rule violation

3C

ambiguous cursor name

3D

invalid catalog name

3F

invalid schema name

40

transaction rollback

42

syntax error or access rule violation

44

with check option violation

HZ

remote database access


Note:

The class code HZ is reserved for conditions defined in International Standard ISO/IEC DIS 9579-2, Remote Database Access.

Table 2-2 shows how SQLSTATE status codes and conditions are mapped to Oracle errors. Status codes in the range 60000 .. 99999 are implementation-defined.

Table 2-2 SQLSTATE Status Codes

Code Condition OracleError(s)

00000

successful completion

ORA-00000

01000

warning


01001

cursor operation conflict


01002

disconnect error


01003

null value eliminated in set function


01004

string data-right truncation


01005

insufficient item descriptor areas


01006

privilege not revoked


01007

privilege not granted


01008

implicit zero-bit padding


01009

search condition too long for info schema


0100A

query expression too long for info schema


02000

no data

ORA-01095 ORA-01403

07000

dynamic SQL error


07001

using clause does not match parameter specs


07002

using clause does not match target specs


07003

cursor specification cannot be executed


07004

using clause required for dynamic parameters


07005

prepared statement not a cursor specification


07006

restricted datatype attribute violation


07007

using clause required for result components invalid descriptor count


07008

invalid descriptor count

SQL-02126

07009

invalid descriptor index


08000

connection exception


08001

SQL-client unable to establish SQL-connection


08002

connection name is use


08003

connection does not exist

SQL-02121

08004

SQL-server rejected SQL-connection


08006

connection failure


08007

transaction resolution unknown


0A000

feature not supported

ORA-03000..03099

0A001

multiple server transactions


21000

cardinality violation

ORA-01427 SQL-02112

22000

data exception


22001

string data - right truncation

ORA-01406

22002

null value-no indicator parameter

SQL-02124

22003

numeric value out of range

ORA-01426

22005

error in assignment


22007

invalid datetime format


22008

datetime field overflow

ORA-01800..01899

22009

invalid time zone displacement value


22011

substring error


22012

division by zero

ORA-01476

22015

interval field overflow


22018

invalid character value for cast


22019

invalid escape character

ORA-00911

22021

character not in repertoire


22022

indicator overflow

ORA-01411

22023

invalid parameter value

ORA-01025 ORA-04000..04019

22024

unterminated C string

ORA-01479 ORA-01480

22025

invalid escape sequence

ORA-01424 ORA-01425

22026

string data-length mismatch

ORA-01401

22027

trim error


23000

integrity constraint violation

ORA-02290..02299

24000

invalid cursor state

ORA-001002 ORA-001003 SQL-02114 SQL-02117

25000

invalid transaction state

SQL-02118

26000

invalid SQL statement name


27000

triggered data change violation


28000

invalid authorization specification


2A000

direct SQL syntax error or access rule violation


2B000

dependent privilege descriptors still exist


2C000

invalid character set name


2D000

invalid transaction termination


2E000

invalid connection name


33000

invalid SQL descriptor name


34000

invalid cursor name


35000

invalid condition number


37000

dynamic SQL syntax error or access rule violation


3C000

ambiguous cursor name


3D000

invalid catalog name


3F000

invalid schema name


40000

transaction rollback

ORA-02091 ORA-02092

40001

serialization failure


40002

integrity constraint violation


40003

statement completion unknown


42000

syntax error or access rule violation

ORA-00022 ORA-00251 ORA-00900..00999 ORA-01031 ORA-01490..01493 ORA-01700..01799 ORA-01900..02099 ORA-02140..02289 ORA-02420..02424 ORA-02450..02499 ORA-03276..03299 ORA-04040..04059 ORA-04070..04099

44000

with check option violation

ORA-01402

60000

system error

ORA-00370..00429 ORA-00600..00899 ORA-06430..06449 ORA-07200..07999 ORA-09700..09999

61000

multi-threaded server and detached process errors

ORA-00018..00035 ORA-00050..00068 ORA-02376..02399 ORA-04020..04039

62000

multi-threaded server and detached process errors

ORA-00100..00120 ORA-00440..00569

63000

Oracle*XA and two-task interface errors

ORA-00150..00159 ORA-02700..02899 ORA-03100..03199 ORA-06200..06249

64000

control file, database file, and redo file errors; archival and media recovery errors

ORA-00200..00369 ORA-01100..01250

65000

PL/SQL errors

ORA-06500..06599

66000

SQL*Net driver errors

ORA-06000..06149 ORA-06250..06429 ORA-06600..06999 ORA-12100..12299 ORA-12500..12599

67000

licensing errors

ORA-00430..00439

69000

SQL*Connect errors

ORA-00570..00599 ORA-07000..07199

72000

SQL execute phase errors

ORA-00001 ORA-01000..01099 ORA-01400..01489 ORA-01495..01499 ORA-01500..01699 ORA-02400..02419 ORA-02425..02449 ORA-04060..04069 ORA-08000..08190 ORA-12000..12019 ORA-12300..12499 ORA-12700..21999

82100

out of memory (could not allocate)

SQL-02100

82101

inconsistent cursor cache (UCE/CUC mismatch)

SQL-02101

82102

inconsistent cursor cache (no CUC entry for UCE)

SQL-02102

82103

inconsistent cursor cache (out-or-range CUC ref)

SQL-02103

82104

inconsistent cursor cache (no CUC available)

SQL-02104

82105

inconsistent cursor cache (no CUC entry in cache)

SQL-02105

82106

inconsistent cursor cache (invalid cursor number)

SQL-02106

82107

program too old for runtime library; re-precompile

SQL-02107

82108

invalid descriptor passed to runtime library

SQL-02108

82109

inconsistent host cache (out-or-range SIT ref)

SQL-02109

82110

inconsistent host cache (invalid SQL type)

SQL-02110

82111

heap consistency error

SQL-02111

82113

code generation internal consistency failed

SQL-02115

82114

reentrant code generator gave invalid context

SQL-02116

82117

invalid OPEN or PREPARE for this connection

SQL-02122

82118

application context not found

SQL-02123

HZ000

remote database access



Using SQLSTATE

The following rules apply to using SQLSTATE with SQLCODE or the SQLCA when you precompile with the option setting MODE=ANSI. SQLSTATE must be declared inside a Declare Section; otherwise, it is ignored.

If you declare SQLSTATE

  • Declaring SQLCODE is optional. If you declare SQLCODE inside the Declare Section, the Oracle Server returns status codes to SQLSTATE and SQLCODE after every SQL operation. However, if you declare SQLCODE outside the Declare Section, Oracle returns a status code only to SQLSTATE.

  • Declaring the SQLCA is optional. If you declare the SQLCA, Oracle returns status codes to SQLSTATE and the SQLCA. In this case, to avoid compilation errors, do not declare SQLCODE.

If you do not declare SQLSTATE

  • You must declare SQLCODE inside or outside the Declare Section. The Oracle Server returns a status code to SQLCODE after every SQL operation.

  • Declaring the SQLCA is optional. If you declare the SQLCA, Oracle returns status codes to SQLCODE and the SQLCA.

You can learn the outcome of the most recent executable SQL statement by checking SQLSTATE explicitly with your own code or implicitly with the WHENEVER SQLERROR statement. Check SQLSTATE only after executable SQL statements and PL/SQL statements.

Declaring SQLCODE

When MODE={ANSI | ANSI14} and you have not declared SQLSTATE, you must declare a long integer variable named SQLCODE inside or outside the Declare Section. Upper case is required. An example follows:

/* Declare host and indicator variables */ 
EXEC SQL BEGIN DECLARE SECTION;
 ...
EXEC SQL END DECLARE SECTION;

/* Declare status variable */
 DCL SQLCODE FIXED BIN(31);

After every SQL operation, Oracle returns a status code to the SQLCODE variable currently in scope. The status code, which indicates the outcome of the SQL operation, can be any of the following numbers:

Status Code Meaning
0 Means that Oracle executed the statement without detecting an error or exception.
> 0 Means that Oracle executed the statement but detected an exception. This occurs when Oracle cannot find a row that meets your WHERE-clause search condition or when a SELECT INTO or FETCH returns no rows.

When MODE={ANSI | ANSI14 | ANSI13}, +100 is returned to SQLCODE after an INSERT of no rows. This can happen when a subquery returns no rows to process.

< 0 Means that Oracle did not execute the statement because of a database, system, network, or application error. Such errors can be fatal. When they occur, the current transaction should, in most cases, be rolled back.

Negative return codes correspond to error codes listed in the Oracle database version 7 Server Messages and Codes Manual.


You can learn the outcome of the most recent SQL operation by checking SQLCODE explicitly with your own code or implicitly with the WHENEVER statement.

When you declare SQLCODE instead of the SQLCA in a particular precompilation unit, the precompiler allocates an internal SQLCA for that unit. Your host program cannot access the internal SQLCA. If you declare the SQLCA and SQLCODE, Oracle returns the same status code to both after every SQL operation.

When MODE={ANSI13 | Oracle}, if you declare SQLCODE, it is not used.

Using the SQLCA

Oracle uses the SQLCA to store status information passed to your program at run time. The SQLCA always reflects the outcome of the most recent SQL operation. To determine that outcome, you can check variables in the SQLCA explicitly with your own PL/1 code, or implicitly with the WHENEVER statement.

When MODE=ORACLE (the default) or MODE=ANSI13, you must declare the SQLCA by hardcoding it or by copying it into your program with the INCLUDE statement.

When MODE={ANSI | ANSI14}, declaring the SQLCA is optional. However, you must declare a status variable named SQLCODE. SQL92 specifies a similar status variable named SQLSTATE, which you can use with or without SQLCODE.

After executing a SQL statement, the Oracle Server returns a status code to the SQLSTATE variable currently in scope. The status code indicates whether the SQL statement executed successfully or raised an exception (error or warning condition). To promote interoperability (the ability of systems to exchange information easily), SQL92 predefines all the common SQL exceptions.

Unlike SQLCODE, which stores only error codes, SQLSTATE stores error and warning codes. Furthermore, the SQLSTATE reporting mechanism uses a standardized coding scheme. Thus, SQLSTATE is the preferred status variable. Under SQL92, SQLCODE is a "deprecated feature" retained only for compatibility with SQL89 and likely to be removed from future versions of the standard.

Declaring the SQLCA

To declare the SQLCA, copy it into your program with the INCLUDE statement

EXEC SQL INCLUDE SQLCA;

or hardcode it as shown:

DCL 1 SQLCA,  2 SQLCAID CHAR(8) INIT('SQLCA'),  2 SQLCABC FIXED BIN (31) INIT(136),  2 SQLCODE FIXED BIN (31),  2 SQLERRM CHAR (70) VAR,  2 SQLERRP CHAR (8) INIT('SQLERRP'),  2 SQLERRD (6) FIXED BIN (31),  2 SQLWARN,  3 SQLWARN0 CHAR (1),  3 SQLWARN1 CHAR (1),  3 SQLWARN2 CHAR (1),  3 SQLWARN3 CHAR (1),  3 SQLWARN4 CHAR (1),  3 SQLWARN5 CHAR (1),  3 SQLWARN6 CHAR (1),  3 SQLWARN7 CHAR (1),  2 SQLEXT, CHAR (8) INIT('SQLEXT');

Not declaring the SQLCA when MODE=Oracle results in compile time warnings, and causes runtime errors.

Your Pro*PL/1 program can have more than one SQLCA. The SQLCA should not be INCLUDEd outside of a procedure block, since the elements in it are not declared with the STATIC storage class. Oracle returns information to the SQLCA that is in the scope of the SQL statement that caused the error or warning condition. The name of this structure must be SQLCA, since it is referred to by precompiler-generated code.

What's in the SQLCA?

The SQLCA contains runtime information about the execution of SQL statements, such as Oracle error codes, warning flags, event information, rows-processed count, and diagnostics.

Key Components of Error Reporting

The key components of Pro*PL/1 error reporting depend on several fields in the SQLCA.

Status Codes

Every executable SQL statement returns a status code in the SQLCA variable SQLCODE, which you can check implicitly with WHENEVER SQLERROR, or explicitly with your own PL/1 code.

Warning Flags

Warning flags are returned in the SQLCA variables SQLWARN0 through SQLWARN7, which you can check implicitly with WHENEVER SQLWARNING, or explicitly with your own PL/1 code. These warning flags are useful for detecting runtime conditions not considered errors by Oracle.

Rows-processed Count

The number of rows processed by the most recently executed SQL statement is recorded in the SQLCA variable SQLERRD(3). For repeated FETCHes on an OPEN cursor, SQLERRD(3) keeps a running total of the number of rows fetched.

Parse Error Offset

Before executing a SQL statement, Oracle must parse it, that is, examine it to make sure it follows syntax rules and refers to valid database objects. If Oracle finds an error, an offset is stored in the SQLCA variable SQLERRD(5), which you can check explicitly. The offset specifies the character position in the SQL statement at which the parse error begins. The first character occupies position zero. For example, if the offset is 9, the parse error begins at the 10th character.

If your SQL statement does not cause a parse error, Oracle sets SQLERRD(5) to zero. Oracle also sets SQLERRD(5) to zero if a parse error begins at the first character, which occupies position zero. So, check SQLERRD(5) only if SQLCODE is negative, which means that an error has occurred.

Error Message Text

The error code and message for Oracle errors are available in the SQLCA variable SQLERRM. For example, you might place the following statements in an error-handling routine:

/* Handle SQL execution errors. */
PUT EDIT(SQLCA.SQLERRM)(A(70));

EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE
...

At most, the first 70 characters of message text are stored. For messages longer than 70 characters, you must call the SQLGLM function.

Getting the Full Text of Error Messages

The SQLCA can accommodate error messages of up to 70 characters in length. To get the full text of longer (or nested) error messages, you need the SQLGLM procedure. If connected to Oracle, you can call SQLGLM using the syntax

CALL SQLGLM (MSG_BUF, BUF_SIZE, MSG_LENGTH);

where:

MSG_BUF

Is the buffer in which you want Oracle to store the error message. Oracle blank-pads to the end of this buffer.

BUF_SIZE

Is an integer variable that specifies the maximum length of MSG_BUF in bytes.

MSG_LENGTH

Is an integer variable in which Oracle stores the actual length of the error message.

The maximum length of an Oracle error message is 196 characters including the error code, nested messages, and message inserts such as table and column names. The maximum length of an error message returned by SQLGLM depends on the value you specify for BUF_SIZE. In the following example, you use SQLGLM to get an error message of up to 200 characters in length:

TEST: PROC OPTIONS(MAIN);

/* Declare variables for the function call. */
DCL MSG_BUF CHAR(200), /* buffer for message text */
BUF_SIZE FIXED BIN(31) INIT(200), /* size in bytes */
MSG_LEN FIXED BIN(31); /* length of message text */

WHENEVER SQLERROR GOTO ERROR_PRINT;

...

ERROR_PRINT:
/* Get full text of error message. */
CALL SQLGLM(MSG_BUF, BUF_SIZE, MSG_LEN);
/* Print the text. */
PUT SKIP EDIT (MSG_BUF) (A(MSG_LEN));
...

Notice that SQLGLM is called only when a SQL error has occurred. Always make sure SQLCA.SQLCODE is negative before calling SQLGLM. If you call SQLGLM when SQLCODE is zero, you get the message text associated with a prior SQL statement.

Using the WHENEVER Statement

By default, the Pro*PL/1 Precompiler ignores Oracle error and warning conditions and continues processing if possible. To do automatic condition checking and error handling, you need the WHENEVER statement.

With the WHENEVER statement you can specify actions to be taken when Oracle detects an error, warning condition, or "not found" condition. These actions include continuing with the next statement, calling a procedure, branching to a labeled statement, or stopping.

You code the WHENEVER statement using the following syntax:

EXEC SQL WHENEVER <condition> <action>

You can have Oracle automatically check the SQLCA for any of the following conditions:

When Oracle detects one of the preceding conditions, you can have your program take any of the following actions:

When using the WHENEVER ... DO statement, the usual rules for entering and exiting a procedure apply. However, passing parameters to the subroutine is not allowed. Furthermore, the subroutine must not return a value.

In the following example, you use WHENEVER SQLERROR DO statements to handle specific errors:

...
EXEC SQL WHENEVER SQLERROR DO CALL INSERT_ERROR;
...
EXEC SQL INSERT INTO EMP (EMPNO, ENAME, DEPTNO)
VALUES(:MY_EMPNO, :MY_ENAME, :MY_DEPTNO);
...
INSERT_ERROR: PROCEDURE;
/* test for "duplicate key value" Oracle error */
IF (SQLCA.SQLCODE = -1) THEN DO;
...
/* test for "value too large" Oracle error */
ELSE IF (SQLCA.SQLCODE = -1401) DO;
...
/* and so on. */
END;
END INSERT_ERROR

Notice how the procedure checks variables in the SQLCA to determine a course of action.

For more information about the WHENEVER conditions and actions, see Chapter 7 of the Programmer's Guide to the Oracle Precompilers.

Scope of WHENEVER

Because WHENEVER is a declarative statement, its scope is positional, not logical. That is, it tests all executable SQL statements that physically (not logically) follow it in your program. So, code the WHENEVER statement before the first executable SQL statement you want to test.

A WHENEVER statement stays in effect until superseded by another WHENEVER statement checking for the same condition.

Helpful Hint

You might want to place WHENEVER statements at the beginning of each block that contains SQL statements. That way, SQL statements in one block will not reference WHENEVER actions in another block, causing errors at compile or run time.

Caution

Careless use of WHENEVER can cause problems. For example, the following code enters an infinite loop if the DELETE statement sets NOT FOUND because no rows meet the search condition:

/* Improper use of WHENEVER */ 
EXEC SQL WHENEVER NOT FOUND GOTO DO_DELETE; 
DO J = 1 TO N_FETCH; 
EXEC SQL FETCH EMP_CURSOR INTO :MY_ENAME, :MY_SAL; 
... 
END; 
DO_DELETE: 
EXEC SQL DELETE FROM EMP WHERE EMPNO = :MY_EMPNO;

In the next example, you handle the NOT FOUND condition properly by resetting the GOTO target:

/* Proper use of WHENEVER */ 
EXEC SQL WHENEVER NOT FOUND GOTO DO_DELETE; 
DO J = 1 TO N_FETCH; 
EXEC SQL FETCH EMP_CURSOR INTO :MY_ENAME, :MY_SAL; 
... 
END; 
DO_DELETE: 
EXEC SQL WHENEVER NOT FOUND GOTO WHAT_NEXT; 
EXEC SQL DELETE FROM EMP WHERE EMPNO = :MY_EMPNO; 
... 
WHAT_NEXT: 
...

Also, make sure all SQL statements governed by a WHENEVER ... GOTO statement can branch to the GOTO label. The following code results in a compile time error because the UPDATE statement in PROC2 is not within the scope of LABEL_A in PROC1:

PROC1: PROC(); 
... 
EXEC SQL WHENEVER SQLERROR GOTO LABEL_A; 
EXEC SQL DELETE FROM EMP WHERE DEPTNO = :DEPT_NUMBER; 
... 
LABEL_A: 
PUT SKIP LIST ('Error occurred'); 
END PROC1; 
PROC2: PROC(); 
... 
EXEC SQL UPDATE EMP SET SAL = SAL * 1.20 
WHERE JOB = 'PROGRAMMER'; 
... 
END PROC2;

Using the ORACA

The SQLCA handles standard SQL communications. The ORACA is a similar data structure copied or hardcoded into your program to handle Oracle-specific communications. When you need more runtime information than the SQLCA provides, use the ORACA.

Besides helping you to diagnose problems, the ORACA lets you monitor your program's use of Oracle resources, such as the SQL Statement Executor and the cursor cache, an area of memory reserved for cursor management.

Declaring the ORACA

To declare the ORACA, you can copy it into your main program with the INCLUDE statement, as follows:

/* Copy in the Oracle Communications Area (ORACA). */
EXEC SQL INCLUDE ORACA;

Alternatively, you can hardcode it as follows:

DCL 1 ORACA
 2 ORACAID CHAR (8) INIT ('ORACA')
 2 ORACABC FIXED BIN (31) INIT (176)
 2 ORACCHF FIXED BIN (31) INIT (0)
 2 ORADBGF FIXED BIN (31) INIT (0)
 2 ORAHCHF FIXED BIN (31) INIT (0)
 2 ORASTXTF FIXED BIN (31) INIT (0)
 2 ORASTXT CHAR (70) VAR INIT ('')
 2 ORASFNM CHAR (70) VAR INIT ('')
 2 ORASLNR FIXED BIN (31) INIT (0)
 2 ORAHOC FIXED BIN (31) INIT (0)
 2 ORAMOC FIXED BIN (31) INIT (0)
 2 ORACOC FIXED BIN (31) INIT (0)
 2 ORANOR FIXED BIN (31) INIT (0)
 2 ORANPR FIXED BIN (31) INIT (0)
 2 ORANEX FIXED BIN (31) INIT (0)

Enabling the ORACA

To enable the ORACA, you must set the ORACA precompiler option to YES, either on the command line with

ORACA=YES

or inline with

/* Enable the ORACA. */ 
EXEC Oracle OPTION (ORACA=YES);

Then, you must choose appropriate runtime options by setting flags in the ORACA.

Enabling the ORACA is optional because it adds to runtime overhead. The default setting is ORACA=NO.

What's in the ORACA?

The ORACA contains option settings, system statistics, and extended diagnostics. The preceeding listing shows all the variables in the ORACA.

For a full description of the ORACA, its fields, and the values the fields can store, see Chapter 7 of the Programmer's Guide to the Oracle Precompilers.