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

• Implicit checking with the WHENEVER statement

• Explicit checking of SQLCA variables

When you use the WHENEVER statement to implicitly check the status of your SQL statements, Pro*COBOL automatically inserts error checking code after each executable statement. Alternatively, you can explicitly write your own code to test the value of the SQLCODE member of the SQLCA structure. Include SQLCA by using the embedded SQL INCLUDE statement:

EXEC SQL INCLUDE SQLCA END-EXEC.

When more information is needed about runtime errors than the SQLCA provides, you can use the ORACA, which contains cursor statistics, SQL statement text, certain option settings and system statistics. Include ORACA by using the embedded SQL INCLUDE statement:

EXEC SQL INCLUDE ORACA END-EXEC.

The ORACA is optional and can be declared regardless of the MODE setting.

This section describes how to declare SQLSTATE. SQLSTATE must be declared as a five-character alphanumeric string as 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. 

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.

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

Regardless of the setting of MODE, you can use SQLGLM to get the full text of error messages if you have explicitly declared SQLCODE and not included SQLCA. 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 a database, you can call SQLGLM using the syntax

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

where the parameters are:

All parameters must be passed by reference. This is usually the default parameter passing convention; you need not take special action.

The maximum length of an 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:

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

DB2 provides an assembler routine called DSNTIAR to obtain a form of the SQLCA that can be displayed. For users migrating to Oracle from DB2, Pro*COBOL provides DSNTIAR. The DSNTIAR implementation is a wrapper around SQLGLM. The DSNTIAR interface is as follows

     CALL 'DSNTIAR' USING SQLCA MESSAGE LRECL

where MESSAGE is the output message area, in VARCHAR form of size greater than or equal to 240, and LRECL is a full word containing the length of the output messages, between 72 and 240. The first half-word of the MESSAGE argument contains the length of the remaining area. The possible error codes returned by DSNTIAR are listed in the following table.

Code the WHENEVER statement using the following syntax:

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

In many Pro*COBOL applications, it is convenient to know the text of the statement being processed, its length, and the SQL command (such as INSERT or SELECT) that it contains. This is especially true for applications that use dynamic SQL.

The routine SQLGLS, which is part of the SQLLIB runtime library, returns the following information:

• The text of the most recently parsed SQL statement

• The length of the statement

• A function code

You can call SQLGLS after issuing a static SQL statement. With dynamic SQL Method 1, you can call SQLGLS after the SQL statement is executed. With dynamic SQL Method 2, 3, or 4, you can call SQLGLS after the statement is prepared.

To call SQLGLS, you use the following syntax:

     CALL "SQLGLS" USING SQLSTM STMLEN SQLFC.
 

Table 8-3 shows the host-language datatypes available for the parameters in the SQLGLS argument list.tt

All parameters must be passed by reference. This is usually the default parameter passing convention; you need not take special action.

The parameter SQLSTM is a blank-padded (not null-terminated) character buffer that holds the returned text of the SQL statement. Your program must statically declare the buffer or dynamically allocate memory for it.

The length parameter STMLEN is a four-byte integer. Before calling SQLGLS, set this parameter to the actual size (in bytes) of the SQLSTM buffer. When SQLGLS returns, the SQLSTM buffer contains the SQL statement text blank padded to the length of the buffer. STMLEN returns the actual number of bytes in the returned statement text, not counting the blank padding. However, STMLEN returns a zero if an error occurred.

Some possible errors follow:

• No SQL statement was parsed.

• You passed an invalid parameter (for example, a negative length value).

• An internal exception occurred in SQLLIB.

The parameter SQLFC is a four-byte integer that returns the SQL function code for the SQL command in the statement. A complete table of the function code for each SQL command is found in Oracle Call Interface Programmer's Guide.

There are no SQL function codes for these statements:

• CONNECT

• COMMIT

• FETCH

• ROLLBACK

• RELEASE

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. 

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.

The ORACA includes several option flags. Setting these flags by assigning them nonzero values enables you to:

• Save the text of SQL statements

• Enable DEBUG operations

• Check cursor cache consistency (the cursor cache is a continuously updated area of memory used for cursor management)

• Check heap consistency (the heap is an area of memory reserved for dynamic variables)

• Gather cursor statistics

The following descriptions will help you choose the options you need.

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

• Which variables are declared.

• Declaration placement (inside or outside the Declare Section).

• The ASSUME_SQLCODE setting.

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

For both Tables: when DECLARE_SECTION=NO, any declaration of a status variable is treated as IN as far as these tables are concerned.

Do not use ASSUME_SQLCODE=YES with DECLARE_SECTION=NO.