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.
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.
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 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.
Table 2-1 shows the classes predefined by SQL92.
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 |
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.
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:
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.
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.
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.
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.
The key components of Pro*PL/1 error reporting depend on several fields in the SQLCA.
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 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.
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.
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.
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.
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.
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:
SQLWARNING
SQLERROR
NOT FOUND
When Oracle detects one of the preceding conditions, you can have your program take any of the following actions:
CONTINUE
DO procedure_call
GOTO statement_label
STOP
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.
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.
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.
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;
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.
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)
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.
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.