Pro*COBOL Supplement to the Oracle Precompilers Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Error Handling and Diagnostics


This chapter supplements Chapter 8 of the Programmer's Guide to the Oracle Precompilers. It discusses error reporting and recovery as it applies to Pro*COBOL.

You learn how to declare and use the SQLSTATE status variable, the SQL Communications Area (SQLCA), and the SQLCODE status variable. You also learn how to declare and enable the Oracle Communications Area (ORACA).


Error Handling Alternatives

The Pro*COBOL Precompiler supports four status variables that serve as error handling mechanisms:

The precompiler MODE option governs ANSI/ISO compliance. The availability of the SQLCODE, SQLSTATE, and SQLCA variables depends on the MODE setting. You can declare and use the ORACA variable regardless of the MODE setting. For more information, see "Using the Oracle Communications Area" [*].

When MODE={ORACLE|ANSI13}, you must declare the SQLCA status variable. SQLCODE and SQLSTATE declarations are accepted (not recommended) but are not recognized as status variables. For more information, see "Using the SQL Communications Area" [*].

When MODE={ANSI|ANSI14}, you can use any one, two, or all three of the SQLCODE, SQLSTATE, and SQLCA variables. To determine which variable (or variable combination) is best for your application, see "Using Status Variables when MODE={ANSI|ANSI14}" [*].

SQLCODE and SQLSTATE

With Pro*COBOL, Release 1.5, the SQLCODE status variable was introduced as the SQL89 standard ANSI/ISO error reporting mechanism. The SQL92 standard listed SQLCODE as a deprecated feature and defined a new status variable, SQLSTATE (introduced with Pro*COBOL, Release 1.6), as the preferred ANSI/ISO error reporting mechanism.

SQLCODE stores error codes and the "not found" condition. It is retained only for compatibility with SQL89 and is likely to be removed from future versions of the standard.

Unlike SQLCODE, SQLSTATE stores error and warning codes and uses a standardized coding scheme. After executing a SQL statement, the Oracle server returns a status code to the SQLSTATE variable currently in scope. The status code indicates whether a 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.

SQLCA

The SQLCA is a record-like, host-language data structure. Oracle updates the SQLCA after every executable SQL statement. (SQLCA values are undefined after a declarative statement.) By checking Oracle return codes stored in the SQLCA, your program can determine the outcome of a SQL statement. This can be done in two ways:

You can use WHENEVER statements, code explicit checks on SQLCA variables, or do both. Generally, using WHENEVER statements is preferable because it is easier, more portable, and ANSI-compliant.

ORACA

When more information is needed about runtime errors than the SQLCA provides, you can use the ORACA, which contains cursor statistics, SQL statement data, option settings, and system statistics.

The ORACA is optional and can be declared regardless of the MODE setting. For more information about the ORACA status variable, see "Using the Oracle Communications Area" [*].


Using Status Variables when MODE={ANSI|ANSI14}

When MODE={ANSI|ANSI14}, you must declare at least one -- you may declare two or all three -- of the following status variables:

You cannot declare SQLCODE if SQLCA is declared. Likewise, you cannot declare SQLCA if SQLCODE is declared. The field in the SQLCA data structure that stores the error code for is also called SQLCODE, so errors will occur if both status variables are declared.

Your program can get the outcome of the most recent executable SQL statement by checking SQLCODE and/or SQLSTATE explicitly with your own code after executable SQL and PL/SQL statements. Your program can also check SQLCA implicitly (with the WHENEVER SQLERROR and WHENEVER SQLWARNING statements) or it can check the SQLCA variables explicitly.

Note: When MODE={ORACLE|ANSI13|ANSI14}, you must declare the SQLCA status variable. For more information, see "Using the SQL Communications Area" [*].

Some Historical Information

The treatment of status variables and variable combinations by the Oracle Pro*COBOL Precompiler has evolved beginning with Release 1.5.

Release 1.5

Pro*COBOL, Release 1.5, presumed there was a status variable SQLCODE whether or not it was declared in a Declare Section; in fact, the precompiler never noted whether SQLCODE was declared or not -- it just presumed it was. SQLCA would be used as a status variable if and only if there was an INCLUDE of the SQLCA.

Release 1.6

Beginning with Pro*COBOL, Release 1.6, the precompiler no longer presumes that there is a SQLCODE status variable and it is not required. The precompiler requires that at least one of SQLCA, SQLCODE, or SQLSTATE be declared.

SQLCODE is recognized as a status variable if and only if at least one of the following criteria is satisfied:

If the precompiler finds a SQLSTATE declaration (of exactly the right type of course) in a Declare Section or finds an INCLUDE of the SQLCA, it will not presume SQLCODE is declared.

Release 1.7

Because Pro*COBOL, Release 1.5, allowed the SQLCODE variable to be declared outside of a Declare Section while also declaring SQLCA, Pro*COBOL, Release 1.6 and greater, is presented with a compatibility problem. A new option, ASSUME_SQLCODE={YES|NO} (default NO), was added to fix this in Release 1.6.7 and is documented as a new feature in Release 1.7.

When ASSUME_SQLCODE=YES, and when SQLSTATE is declared as a status variable, the precompiler presumes SQLCODE is declared whether or not it is declared in a Declare Section or of the proper type. This causes Releases 1.6.7 and later to act like Release 1.5 in this regard. For information about the precompiler option ASSUME_SQLCODE, see Chapter 6 in the Programmer's Guide to the Oracle Precompilers.

Declaring Status Variables

This section describes how to declare SQLCODE and SQLSTATE. For information about declaring the SQLCA status variable, see "Declaring the SQLCA" [*].

Declaring SQLCODE

SQLCODE must be declared as a 4-byte integer variable either inside or outside the Declare Section, as shown in the following example:

*    Declare host and indicator variables. 
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         ... 
     EXEC SQL END DECLARE SECTION END-EXEC. 
*    Declare the SQLCODE status variable. 
     01  SQLCODE  PIC S9(9) COMP. 

If declared outside the Declare Section, SQLCODE is recognized as a status variable if and only if ASSUME_SQLCODE=YES. When MODE={ORACLE|ANSI13|ANSI14}, declarations of the SQLCODE variable are ignored.

Warning: Do not declare SQLCODE if SQLCA is declared. Likewise, do not declare SQLCA if SQLCODE is declared. The status variable declared by the SQLCA structure is also called SQLCODE, so errors will occur if both error-reporting mechanisms are used.

After every SQL operation, Oracle returns a status code to the SQLCODE variable. So, your program can learn the outcome of the most recent SQL operation by checking SQLCODE explicitly, or implicitly with the WHENEVER statement.

When you declare SQLCODE instead of the SQLCA in a particular compilation unit, the precompiler allocates an internal SQLCA for that unit. Your host program cannot access the internal SQLCA.

Declaring SQLSTATE

SQLSTATE must be declared as a five-character alphanumeric string inside the Declare Section, as shown in the following example:

*    Declare the SQLSTATE status variable.
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         ... 
     01 SQLSTATE PIC X(5). 
         ... 
     EXEC SQL END DECLARE SECTION END-EXEC. 

When MODE={ORACLE|ANSI13|ANSI14}, SQLSTATE declarations are ignored. Declaring the SQLCA is optional.

Status Variable Combinations

When MODE={ANSI|ANSI14}, the behavior of the status variables depends on the following:

Table 2 - 1 and Table 2 - 2 describe the resulting behavior of each status variable combination when ASSUME_SQLCODE=NO and when ASSUME_SQLCODE=YES, respectively.

Declare Section (IN/OUT/ --)
SQLCODE SQLSTATE SQLCA
Behavior
OUT -- -- SQLCODE is declared and is presumed to be a status variable.
OUT -- OUT This status variable configuration is not supported.
OUT -- IN This status variable configuration is not supported.
OUT OUT -- SQLCODE is declared and is presumed to be a status variable, and SQLSTATE is declared but is not recognized as a status variable.
OUT OUT OUT This status variable configuration is not supported.
OUT OUT IN This status variable configuration is not supported.
OUT IN -- SQLSTATE is declared as a status variable, and SQLCODE is declared but is not recognized as a status variable.
OUT IN OUT This status variable configuration is not supported.
OUT IN IN This status variable configuration is not supported.
IN -- -- SQLCODE is declared as a status variable.
IN -- OUT This status variable configuration is not supported.
IN -- IN This status variable configuration is not supported.
IN OUT -- SQLCODE is declared as a status variable, and SQLSTATE is declared but is not recognized as a status variable.
IN OUT OUT This status variable configuration is not supported.
IN OUT IN This status variable configuration is not supported.
IN IN -- SQLCODE and SQLSTATE are declared as a status variables.
IN IN OUT This status variable configuration is not supported.
IN IN IN This status variable configuration is not supported.
-- -- -- This status variable configuration is not supported.
-- -- OUT SQLCA is declared as a status variable.
-- -- IN SQLCA is declared as a status host variable.
-- OUT -- This status variable configuration is not supported.
-- OUT OUT SQLCA is declared as a status variable, and SQLSTATE is declared but is not recognized as a status variable.
-- OUT IN SQLCA is declared as a status host variable, and SQLSTATE is declared but is not recognized as a status variable.
-- IN -- SQLSTATE is declared as a status variable.
-- IN OUT SQLSTATE and SQLCA are declared as status variables.
-- IN IN SQLSTATE and SQLCA are declared as status host variables.
Table 2 - 1. Status Variable Behavior with ASSUME_SQLCODE=NO and MODE=ANSI|ANSI14



Declare Section (IN/OUT/ --)
SQLCODE SQLSTATE SQLCA
Behavior
OUT -- -- SQLCODE is declared and is presumed to be a status variable.
OUT -- OUT This status variable configuration is not supported.
OUT -- IN This status variable configuration is not supported.
OUT OUT -- SQLCODE is declared and is presumed to be a status variable, and SQLSTATE is declared but is not recognized as a status variable.
OUT OUT OUT This status variable configuration is not supported.
OUT OUT IN This status variable configuration is not supported.
OUT IN -- SQLSTATE is declared as a status variable, and SQLCODE is declared and is presumed to be a status variable.
OUT IN OUT This status variable configuration is not supported.
OUT IN IN This status variable configuration is not supported.
IN -- -- SQLCODE is declared as a status variable.
IN -- OUT This status variable configuration is not supported.
IN -- IN This status variable configuration is not supported.
IN OUT -- SQLCODE is declared as a status variable, and SQLSTATE is declared but not as a status variable.
IN OUT OUT This status variable configuration is not supported.
IN OUT IN This status variable configuration is not supported.
IN IN -- SQLCODE and SQLSTATE are declared as a status variables.
IN IN OUT This status variable configuration is not supported.
IN IN IN This status variable configuration is not supported.
-- -- -- -- -- -- -- -- -- -- -- -- OUT OUT OUT IN IN IN -- OUT IN -- OUT IN -- OUT IN These status variable configurations are not supported. SQLCODE must be declared either inside or outside the Declare Section when ASSUME_SQLCODE=YES.
Table 2 - 2. Status Variable Behavior with ASSUME_SQLCODE=YES and MODE=ANSI|ANSI14




Using the SQL Communications Area

Oracle uses the SQL Communications Area (SQLCA) to store status information passed to your program at run time. The SQLCA is a record-like, COBOL data structure that is a updated after each executable SQL statement, so it 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 COBOL code or implicitly with the WHENEVER statement.

When MODE={ORACLE|ANSI13}, the SQLCA is required; if the SQLCA is not declared, compile-time errors will occur. The SQLCA is optional when MODE={ANSI|ANSI14}, but you cannot use the WHENEVER SQLWARNING statement without the SQLCA. So, if you want to use the WHENEVER SQLWARNING statement, you must declare the SQLCA.

When MODE={ANSI|ANSI14}, you must declare either SQLSTATE (see "Declaring SQLSTATE" [*]) or SQLCODE (see "Declaring SQLCODE" [*]) or both. The SQLSTATE status variable supports the SQLSTATE status variable specified by the SQL92 standard. You can use the SQLSTATE status variable with or without SQLCODE. For more information see Chapter 8 of the Programmer's Guide to the Oracle Precompilers.

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.

Figure 2 - 1 shows all the variables in the SQLCA. However, SQLWARN2, SQLWARN5, SQLWARN6, SQLWARN7, and SQLEXT are not currently in use.

Figure 2 - 1. SQLCA Variable Declarations for Pro*COBOL

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

Declaring the SQLCA

To declare the SQLCA, simply include it (using an EXEC SQL INCLUDE statement) in your Pro*COBOL source file outside the Declare Section as follows:

*    Include the SQL Communications Area (SQLCA). 
     EXEC SQL INCLUDE SQLCA END-EXEC. 

The SQLCA must be declared outside the Declare Section.

Warning: Do not declare SQLCODE if SQLCA is declared. Likewise, do not declare SQLCA if SQLCODE is declared. The status variable declared by the SQLCA structure is also called SQLCODE, so errors will occur if both error-reporting mechanisms are used.

When you precompile your program, the INCLUDE SQLCA statement is replaced by several variable declarations that allow Oracle to communicate with the program.

Attention: When using multi-byte NLS host variables, the SQLCA must be included.

Key Components of Error Reporting

The key components of Pro*COBOL 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 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 by Oracle.

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, 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 tenth 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 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 next.

Getting the Full Text of Error Messages

The SQLCA can accommodate error messages up to 70 characters long. To get the full text of longer (or nested) error messages, you need the SQLGLM subroutine.

If connected to Oracle, you can call SQLGLM using the syntax

     CALL "SQLGLM" USING MSG-TEXT, MAX-SIZE, MSG-LENGTH

where:

MSG-TEXT

is the field in which to store the error message. (Oracle blank-pads to the end of this field.)

MAX-SIZE

is an integer that specifies the maximum size of the MSG-TEXT field 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 512 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 specified for MAX-SIZE.

The following example uses SQLGLM to get an error message of up to 200 characters in length:

 WORKING-STORAGE SECTION. 
     ... 
*    Declare variables for the SQL-ERROR subroutine call. 
     01  MSG-TEXT    PIC X(200). 
     01  MAX-SIZE    PIC S9(9) COMP VALUE 200. 
     01  MSG-LENGTH  PIC S9(9) COMP. 
     ... 
 PROCEDURE DIVISION. 
 MAIN. 
     EXEC SQL WHENEVER SQLERROR GOTO SQL-ERROR END-EXEC. 
     ... 
 SQL-ERROR. 
*    Clear the previous message text. 
     MOVE SPACES TO MSG-TEXT. 
*    Get the full text of the error message. 
     CALL "SQLGLM" USING MSG-TEXT, MAX-SIZE, MSG-LENGTH. 
     DISPLAY MSG-TEXT. 

In the example, SQLGLM is called only when a SQL error has occurred. Always make sure 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.

Note: If your application calls SQLGLM to get message text or your Oracle*Forms user exit calls SQLIEM to display a failure message, the message length must be passed. Do not use the SQLCA variable SQLERRML; SQLERRML is a PIC S9(4) COMP integer while SQLGLM and SQLIEM expect a PIC S9(9) COMP integer. Instead, use another variable declared as PIC S9(9) COMP.

Using the WHENEVER Statement

By default, the Pro*COBOL 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, PERFORMing a paragraph, branching to a paragraph, or stopping.

Code the WHENEVER statement using the following syntax:

     EXEC SQL 
         WHENEVER <condition> <action> 
     END-EXEC. 

You can have Oracle automatically check the SQLCA for any of the following conditions, which are described in the Programmer's Guide to the Oracle Precompilers.

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 PERFORMing a paragraph 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 statements are used to handle specific errors:

 PROCEDURE DIVISION. 
 MAIN. 
     ... 
     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. 
     ... 
*    Error-handling paragraphs.
 INS-ERROR. 
*    Check for "duplicate key value" Oracle error 
     IF SQLCODE IN SQLCA = -1 
         ... 
*    Check for "value too large" Oracle error 
     ELSE IF SQLCODE IN SQLCA = -1401 
         ... 
     ELSE 
         ... 
     END-IF.
     ...
 DEL-ERROR. 
*    Check for the number of rows processed.
     IF SQLERRD(3) IN SQLCA = 0 
         ... 
     ELSE 
         ...
     END-IF. 
     ...

Notice how the paragraphs check variables in the SQLCA to determine a course of action. For more information about the WHENEVER conditions and actions, see Chapter 8 of the Programmer's Guide to the Oracle Precompilers.

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 might want to 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. 
     ... 


Using the Oracle Communications Area

The SQLCA handles standard SQL communications. The Oracle Communications Area (ORACA) is a similar structure that you can include in 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.

What's in the ORACA?

The ORACA contains option settings, system statistics, and extended diagnostics. Figure 2 - 2 shows all the variables in the ORACA.

Figure 2 - 2. ORACA Variable Declarations for Pro*COBOL

Declaring the ORACA

To declare the ORACA, simply include it (using an EXEC SQL INCLUDE statement) in your Pro*COBOL source file outside the Declare Section as follows:

*    Include the Oracle Communications Area (ORACA). 
     EXEC SQL INCLUDE ORACA END-EXEC. 

Enabling the ORACA

To enable the ORACA, you must set the ORACA precompiler option to YES on the command line or in a configuration file with

ORACA=YES 

or inline with

     EXEC Oracle OPTION (ORACA=YES) END-EXEC. 

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.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index