8 Error Handling and Diagnostics

SQLSTATE Values

SQLSTATE status codes consist of a two-character class code followed by a three-character subclass code. Aside from class code 00 (successful completion), the class code denotes a category of exceptions. 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 sub-conditions. All other subclass codes are reserved for implementation-defined sub-conditions. Figure 8-1 shows the coding scheme:

Figure 8-1 SQLSTATE Coding Scheme

Description of "Figure 8-1 SQLSTATE Coding Scheme"

Table 8-1 shows the classes predefined by SQL92.

Table 8-4, "SQLSTATE Codes" shows how errors map to SQLSTATE status codes. In some cases, several errors map to the status code. In other cases, no error maps to the status code (so the last column is empty). Status codes in the range 60000..99999 are implementation-defined.

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 COBOL code.

Warning Flags

Warning flags are returned in the SQLCA variables SQLWARN0 through SQLWARN7, which you can check with WHENEVER SQLWARNING or with your own COBOL code. These warning flags are useful for detecting runtime conditions that are not considered errors.

Rows-Processed Count

The number of rows processed by the most recently executed SQL statement is returned 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, Oracle9i must parse it; that is, examine it to make sure it follows syntax rules and refers to valid database objects. If Oracle9i 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 tenth character.

If your SQL statement does not cause a parse error, Oracle9i sets SQLERRD(5) to zero. Oracle9i 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 errors are available in the SQLCA variable SQLERRMC. For example, you might place the following statements in an error-handling routine:

*    Handle SQL execution errors. 
     MOVE SQLERRMC TO ERROR-MESSAGE. 
     DISPLAY ERROR-MESSAGE. 

At most, the first 70 characters of message text are stored. For messages longer than 70 characters, you must call the SQLGLM subroutine, which is discussed in "Getting the Full Text of Error Messages".

SQLCAID

This string field is initialized to "SQLCA" to identify the SQL Communications Area.

SQLCABC

This integer field holds the length, in bytes, of the SQLCA structure.

SQLCODE

This integer field holds the status code of the most recently executed SQL statement. The status code, which indicates the outcome of the SQL operation, can be any of the following numbers:

SQLERRM

This sub-record contains the following two fields:

SQLERRP

This string field is reserved for future use.

SQLERRD

This table of binary integers has six elements. Descriptions of the fields in SQLERRD follow:

SQLWARN

This table of single characters has eight elements. They are used as warning flags. Oracle9i sets a flag by assigning it a 'W' (for warning) character value. The flags warn of exceptional conditions.

For example, a warning flag is set when Oracle9i assigns a truncated column value to an output host character variable.

Descriptions of the fields in SQLWARN follow:

SQLEXT

This string field is reserved for future use.

Conditions

You can have Oracle9i automatically check the SQLCA for any of the following conditions.

SQLWARNING

SQLWARN(0) is set because Oracle9i returned a warning (one of the warning flags, SQLWARN(1) through SQLWARN(7), is also set) or SQLCODE has a positive value other than +1403. For example, SQLWARN(1) is set when Oracle9 assigns a truncated column value to an output host variable.

Declaring the SQLCA is optional when MODE={ANSI | ANSI14}. To use WHENEVER SQLWARNING, however, you must declare the SQLCA.

SQLERROR

SQLCODE has a negative value if Oracle9i returns an error.

NOT FOUND or NOTFOUND

SQLCODE has a value of +1403 (or +100 when MODE={ANSI | ANSI14 | ANSI13} or when END_OF_FETCH=100) when the end of fetch has been reached. This can happen when all the rows that meet the search criteria have been fetched or no rows meet that criteria.

You may use the END_OF_FETCH option to override the value use by the MODE macro option.

END_OF_FETCH = 100 | 1403 (default 1403)

For more details, see "END_OF_FETCH".

Actions

You can use the WHENEVER statement to specify the following actions.

CONTINUE

Your program continues to run with the next statement if possible. This is the default action, equivalent to not using the WHENEVER statement. You can use it to "turn off" condition checking.

DO CALL

Your program calls a nested subprogram. When the end of the subprogram is reached, control transfers to the statement that follows the failed SQL statement.

DO PERFORM

Your program transfers control to a COBOL section or paragraph. When the end of the section is reached, control transfers to the statement that follows the failed SQL statement.

EXEC SQL                                                
         WHENEVER <condition> DO PERFORM <section_name>    
     END-EXEC.                                              

GOTO or GO TO

Your program branches to the specified paragraph or section.

STOP

Your program stops running and uncommitted work is rolled back.

Be careful. The STOP action displays no messages before logging off.

DO PERFORM

When using the WHENEVER ... DO PERFORM statement, the usual rules for PERFORMing a paragraph or section apply. However, you cannot use the THRU, TIMES, UNTIL, or VARYING clauses.

For example, the following WHENEVER ... DO statement is invalid:

PROCEDURE DIVISION. 
*    Invalid statement 
     EXEC SQL WHENEVER SQLERROR DO 
         PERFORM DISPLAY-ERROR THRU LOG-OFF 
     END-EXEC. 
     ... 
 DISPLAY-ERROR. 
     ... 
 LOG-OFF. 
     ... 

In the following example, WHENEVER SQLERROR DO PERFORM statements are used to handle specific errors:

PROCEDURE DIVISION. 
 MAIN SECTION.
 MSTART.
     ... 
     EXEC SQL 
         WHENEVER SQLERROR DO PERFORM INS-ERROR 
     END-EXEC. 
     EXEC SQL 
         INSERT INTO EMP (EMPNO, ENAME, DEPTNO) 
         VALUES (:EMP-NUMBER, :EMP-NAME, :DEPT-NUMBER) 
     END-EXEC.
     EXEC SQL 
         WHENEVER SQLERROR DO PERFORM DEL-ERROR 
     END-EXEC. 
     EXEC SQL 
         DELETE FROM DEPT 
         WHERE DEPTNO = :DEPT-NUMBER 
     END-EXEC. 
     ...
 MEXIT.
     STOP RUN.
 INS-ERROR SECTION.
 INSSTART.
*    Check for "duplicate key value" Oracle9 error 
     IF SQLCA.SQLCODE = -1 
     ... 
*    Check for "value too large" Oracle9 error 
     ELSE IF SQLCA.SQLCODE = -1401 
     ... 
     ELSE 
     ... 
     END-IF.
     ...
 INSEXIT.
     EXIT.
*
 DEL-ERROR SECTION.
 DSTART.
*    Check for the number of rows processed.
     IF SQLCA.SQLERRD(3) = 0 
     ... 
     ELSE 
     ...
     END-IF.
     ...
 DEXIT.
     EXIT.

Notice how the paragraphs check variables in the SQLCA to determine a course of action.

DO CALL

This clause calls an action subprogram. Here is the syntax of this clause:

EXEC SQL
         WHENEVER <condition> DO CALL <subprogram_name>
         [USING <param1> ...]
     END-EXEC.

The following restrictions or rules apply:

• You cannot use the RETURNING, ON_EXCEPTION, or OVER_FLOW phrases in the USING clause.

• You may have to enter the subprogram name followed by the keyword COMMON in the PROGRAM-ID statement of your COBOL source code.

• You must use a WHENEVER CONTINUE statement in the action subprogram.

• The action subprogram name may have to be in double quotes in the DO CALL clause of the WHENEVER directive.

Here is an example of a program that can call the error subprogram SQL-ERROR from inside the subprogram LOGON, or inside the MAIN program, without having to repeat code in two places, as when using the DO PERFORM clause:

IDENTIFICATION DIVISION.
            PROGRAM-ID. MAIN.
            ENVIRONMENT DIVISION.
        ...
            PROCEDURE DIVISION.
            BEGIN-PGM.
                EXEC SQL
                    WHENEVER SQLERROR DO CALL "SQL-ERROR"
                END-EXEC.
                CALL "LOGON".
        ...
            IDENTIFICATION DIVISION.
            PROGRAM-ID. LOGON.
            DATA DIVISION.
            WORKING-STORAGE SECTION.
            01  USERNAME          PIC X(15) VARYING.
            01  PASSWD            PIC X(15) VARYING.
            PROCEDURE DIVISION.
                MOVE "SCOTT" TO USERNAME-ARR.
                MOVE 5 TO USERNAME-LEN.
                MOVE "TIGER" TO PASSWD-ARR.
                MOVE 5 TO PASSWD-LEN.
                EXEC SQL
                   CONNECT :USERNAME IDENTIFIED BY :PASSWD
                END-EXEC.
                DISPLAY " ".
                DISPLAY "CONNECTED TO ORACLE AS USER:  ", USERNAME-ARR.
            END PROGRAM LOGON.
        ...
            IDENTIFICATION DIVISION.
            PROGRAM-ID. SQL-ERROR COMMON.
            PROCEDURE DIVISION.
                EXEC SQL
                    WHENEVER SQLERROR CONTINUE
                END-EXEC.
                DISPLAY " ".
                DISPLAY SQLERRMC.
                EXEC SQL
                    ROLLBACK WORK RELEASE
                END-EXEC.
                END PROGRAM SQL-ERROR.
            END PROGRAM MAIN.

Scope

Because WHENEVER is a declarative statement, its scope is positional, not logical. It tests all executable SQL statements that follow it in the source file, not in the flow of program logic. 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.

Suggestion: You can place WHENEVER statements at the beginning of each program unit that contains SQL statements. That way, SQL statements in one program unit will not reference WHENEVER actions in another program unit, causing errors at compile or run time.

Careless Usage: Examples

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

*    Improper use of WHENEVER. 
     EXEC SQL
         WHENEVER NOT FOUND GOTO NO-MORE
     END-EXEC. 
     PERFORM GET-ROWS UNTIL DONE = "YES". 
     ... 
 GET-ROWS. 
     EXEC SQL
         FETCH emp_cursor INTO :EMP-NAME, :SALARY
     END-EXEC. 
     ... 
 NO-MORE. 
     MOVE "YES" TO DONE. 
     EXEC SQL
         DELETE FROM EMP WHERE EMPNO = :EMP-NUMBER
     END-EXEC. 
     ... 

In the next example, the NOT FOUND condition is properly handled by resetting the GOTO target:

*    Proper use of WHENEVER. 
     EXEC SQL WHENEVER NOT FOUND GOTO NO-MORE END-EXEC. 
     PERFORM GET-ROWS UNTIL DONE = "YES". 
     ... 
 GET-ROWS. 
     EXEC SQL
         FETCH emp_cursor INTO :EMP-NAME, :SALARY
     END-EXEC. 
     ... 
 NO-MORE. 
     MOVE "YES" TO DONE. 
     EXEC SQL WHENEVER NOT FOUND GOTO NONE-FOUND END-EXEC. 
     EXEC SQL
        DELETE FROM EMP WHERE EMPNO = :EMP-NUMBER
     END-EXEC. 
     ... 
 NONE-FOUND. 
     ... 

ORACAID

This string field is initialized to ORACA to identify the Oracle Communications Area.

ORACABC

This integer field holds the length, expressed in bytes, of the ORACA data structure.

ORACCHF

If the master DEBUG flag (ORADBGF) is set, this flag lets you check the cursor cache for consistency before every cursor operation.

The runtime library does the consistency checking and can issue error messages, which are listed in Oracle Database Error Messages.

This flag has the following settings:

ORADBGF

This master flag lets you choose all the DEBUG options. It has the following settings:

ORAHCHF

If the master DEBUG flag (ORADBGF) is set, this flag tells the runtime library to check the heap for consistency every time Pro*COBOL dynamically allocates or frees memory. This is useful for detecting program bugs that upset memory.

This flag must be set before the CONNECT command is issued and, once set, cannot be cleared; subsequent change requests are ignored. It has the following settings:

ORASTXTF

This flag lets you specify when the text of the current SQL statement is saved. It has the following settings:

The SQL statement text is saved in the ORACA sub-record named ORASTXT.

Diagnostics

The ORACA provides an enhanced set of diagnostics; the following variables help you to locate errors quickly.

ORASTXT

This sub-record helps you find faulty SQL statements. It lets you save the text of the last SQL statement parsed by Oracle9i. It contains the following two fields:

Statements parsed by Pro*COBOL, such as CONNECT, FETCH, and COMMIT, are not saved in the ORACA.

ORASFNM

This sub-record identifies the file containing the current SQL statement and so helps you find errors when multiple files are precompiled for one application. It contains the following two fields:

ORASLNR

This integer field identifies the line at (or near) which the current SQL statement can be found.

Cursor Cache Statistics

The following variables let you gather cursor cache statistics. They are automatically set by every COMMIT or ROLLBACK statement your program issues. Internally, there is a set of these variables for each CONNECTed database. The current values in the ORACA pertain to the database against which the last commit or rollback was executed.

ORAHOC

This integer field records the highest value to which MAXOPENCURSORS was set during program execution.

ORAMOC

This integer field records the maximum number of open cursors required by your program. This number can be higher than ORAHOC if MAXOPENCURSORS was set too low, which forced Pro*COBOL to extend the cursor cache.

ORACOC

This integer field records the current number of open cursors required by your program.

ORANOR

This integer field records the number of cursor cache reassignments required by your program. This number shows the degree of "thrashing" in the cursor cache and should be kept as low as possible.

ORANPR

This integer field records the number of SQL statement parses required by your program.

ORANEX

This integer field records the number of SQL statement executions required by your program. The ratio of this number to the ORANPR number should be kept as high as possible. In other words, avoid unnecessary re-parsing. For help, see Appendix C, "Performance Tuning".