2 Error Handling and Diagnostics

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 run-time 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);

SQLSTATE Values

SQLSTATE status codes consist of a 2-character class code followed by a 3-character subclass code. Except class code 00 ("successful completion"), the class code denotes a category of exceptions. And, except 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 the SQL standard). 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

Description of "Figure 2-1 SQLSTATE Coding Scheme"

Table 2-1 shows the classes predefined by the SQL standard.

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.

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 numbers listed in the following table:

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.

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 run-time 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 run-time 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.

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

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

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.

/* 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;

/* 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: 
...

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;

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 run-time options by setting flags in the ORACA.

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

What's in the ORACA?

The ORACA contains option settings, system statistics, and extended diagnostics. The preceding 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 Oracle Database Programmer's Guide to the Oracle Precompilers.