Pro*COBOL Precompiler Programmer's Guide Release 8.0 A58232-01 |
Library | Product | Contents | Index

# 9
Error Handling and Diagnostics

An application program must anticipate runtime errors and attempt to recover from them. This chapter provides an in-depth discussion of error reporting and recovery. You learn how to handle warnings and errors using the status variables SQLCODE, SQLSTATE, and SQLCA (SQL Communications Area), and the WHENEVER statement. You also learn how to diagnose problems using the status variable ORACA (Oracle Communications Area). The following topics are discussed:

- The Need for Error Handling
- Error Handling Alternatives
- Using Status Variables when MODE={ANSI|ANSI14}
- Using the SQL Communications Area
- Using the Oracle Communications Area

## The Need for Error Handling

A significant part of every application program must be devoted to error handling. The main benefit of error handling is that it allows your program to continue operating in the presence of errors. Errors arise from design faults, coding mistakes, hardware failures, invalid user input, and many other sources

You cannot anticipate all possible errors, but you can plan to handle certain kinds of errors meaningful to your program. For Pro*COBOL, error handling means detecting and recovering from SQL statement execution errors.

You can also prepare to handle warnings such as "value truncated" and status changes such as "end of data." It is especially important to check for error and warning conditions after every data manipulation statement, because an INSERT, UPDATE, or DELETE statement might fail before processing all eligible rows in a table.

## Error Handling Alternatives

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

- SQLCODE
- SQLSTATE
- SQLCA (using the WHENEVER statement)
- ORACA

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" on page 9-35.

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" on page 9-19.

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}" on page 9-4.

### SQLCODE and SQLSTATE

With Release 1.5 of Pro*COBOL, 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 Release 1.6 of Pro*COBOL), 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 Oracle8 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 interpretability (the ability of systems to exchange information easily), SQL92 pre-defines all the common SQL exceptions.

### SQLCA

The SQLCA is a record-like, host-language data structure. Oracle8 updates the SQLCA after every executable SQL statement. (SQLCA values are undefined after a declarative statement.) By checking Oracle8 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

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.

#### Nested Programs

In nested programs, the included SQLCA definition provided will be declared as global, so the declaration of SQLCA will only be required within the higher-level program. SQLCA can change every time a new SQL statement is executed. The SQLCA provided can always be modified to remove the global specification by the user if the user wishes to declare additional SQLCAs in the nested programs. This applies to SQLDA and ORACA.

### 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" on page 9-35.

## 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:

- SQLCODE
- SQLSTATE
- SQLCA

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" on page 9-19.

### Some Historical Information

The treatment of status variables and variable combinations by Pro*COBOL 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 fact, Pro*COBOL 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. Pro*COBOL requires that at least one of 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:

- It is declared with exactly the right datatype.
- Pro*COBOL finds no other status variable.

If Pro*COBOL finds a SQLSTATE declaration (of exactly the right type of course) 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.

#### Release 8.0

Beginning with release 8.0, the Declare Section is now optional. For details of the ASSUME_SQLCODE option, see "ASSUME_SQLCODE" on page 7-12.

### Declaring Status Variables

This section describes how to declare SQLCODE and SQLSTATE. For information about declaring the SQLCA status variable, see "Declaring the SQLCA" on page 9-20.

#### 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, Oracle8 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, Pro*COBOL 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, 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:

- which variables are declared
- declaration placement (inside or outside the Declare Section)
- ASSUME_SQLCODE setting

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

For both Tables 9-1 and 9-2: 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.

##### Table 9-1 Status Variable Behavior with ASSUME_SQLCODE=NO and MODE=ANSI | ANSI14 and DECLARE_SECTION=YES

Declare Section (IN/OUT/-) | Behavior
--- | ---
SQLCODE | SQLSTATE | SQLCA |
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 9-2 Status Variable Behavior with ASSUME_SQLCODE=YES and MODE=ANSI | ANSI14 and DECLARE_SECTION=YES

Declare Section (IN/OUT/-) | Behavior
--- | ---
SQLCODE | SQLSTATE | SQLCA |
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 when ASSUME_SQLCODE=YES.

### Status Variable Values

This section describes the values for the SQLCODE and SQLSTATE status variables. For information about the SQLCA status variable, see "Key Components of Error Reporting" on page 9-21.

#### SQLCODE Values

After every SQL operation, Oracle8 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, Pro*COBOL allocates an internal SQLCA for that unit. Your host program cannot access the internal SQLCA.

Note: When MODE={ORACLE|ANSI13}, declarations of SQLCODE are ignored.

#### 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 9-1 shows the coding scheme.

#### Figure 9-1 SQLSTATE Coding Scheme

Table 9-3 shows the classes predefined by SQL92

##### Table 9-3 Predifined Classes

Class | Condition
--- | ---
00 | successful 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 9-4 shows how Oracle8 errors map to SQLSTATE status codes. In some cases, several Oracle8 errors map to the status code. In other cases, no Oracle8 error maps to the status code (so the last column is empty). Status codes in the range 60000 .. 99999 are implementation-defined.

##### Table 9-4 SQLSTATE Codes

Code | Condition | Oracle8 Error
--- | --- | ---
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 fields |
07008 | invalid descriptor count | SQL-02126
07009 | invalid descriptor index |
08000 | connection exception |
08001 | SQL client unable to establish SQL connection |
08002 | connection name in 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-01401 ORA-01406
22002 | null value - no indicator parameter | ORA-01405 SQL-02124
22003 | numeric value out of range | ORA-01426 ORA-01438 ORA-01455 ORA-01457
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 ORA-01425
22021 | character not in repertoire |
22022 | indicator overflow | ORA-01411
22023 | invalid parameter value | ORA-01025 ORA-01488 ORA-04000 .. 04019
22024 | unterminated C string | ORA-01479 .. 01480
22025 | invalid escape sequence | ORA-01424
22026 | string data - length mismatch |
22027 | trim error |
23000 | integrity constraint violation | ORA-00001 ORA-02290 .. 02299
24000 | invalid cursor state | ORA-01001 .. 01003 ORA-01410 ORA-08006 SQL-02114 SQL-02117 SQL-02118 SQL-02122
25000 | invalid transaction state |
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 .. 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 errors | ORA-00370 .. 00429 ORA-00600 .. 00899 ORA-06430 .. 06449 ORA-07200 .. 07999 ORA-09700 .. 09999
61000 | resource error | 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 SQL-02128 ORA-02700 .. 02899 ORA-03100 .. 03199 ORA-06200 .. 06249 SQL-02128
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-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: unit cursor/global cursor mismatch | SQL-02101
82102 | inconsistent cursor cache: no global cursor entry | SQL-02102
82103 | inconsistent cursor cache: out of range cursor cache reference | SQL-02103
82104 | inconsistent host cache: no cursor cache available | SQL-02104
82105 | inconsistent cursor cache: global cursor not found | SQL-02105
82106 | inconsistent cursor cache: invalid Oracle8 cursor number | SQL-02106
82107 | program too old for runtime library | SQL-02107
82108 | invalid descriptor passed to runtime library | SQL-02108
82109 | inconsistent host cache: host reference is out of range | SQL-02109
82110 | inconsistent host cache: invalid host cache entry type | SQL-02110
82111 | heap consistency error | SQL-02111
82112 | unable to open message file | SQL-02113
82113 | code generation internal consistency failed | SQL-02115
82114 | reentrant code generator gave invalid context | SQL-02116
82115 | invalid hstdef argument | SQL-02119
82116 | first and second arguments to sqlrcn both null | SQL-02120
82117 | invalid OPEN or PREPARE for this connection | SQL-02122
82118 | application context not found | SQL-02123
82119 | connect error; can't get error text | SQL-02125
82120 | precompiler/SQLLIB version mismatch. | SQL-02127
82121 | FETCHed number of bytes is odd | SQL-02129
82122 | EXEC TOOLS interface is not available | SQL-02130
82123 | runtime context in use | SQL-02131
82124 | unable to allocate runtime context | SQL-02131
82125 | unable to initialize process for use with threads | SQL-02133
82126 | invalid runtime context | SQL-02134
90000 | debug events | ORA-10000 .. 10999
99999 | catch all | all others
HZ000 | remote database access |

## Using the SQL Communications Area

Oracle8 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. Its fields contain error, warning, and status information updated by Oracle8 whenever a SQL statement is executed.To determine that outcome, you can check variables in the SQLCA explicitly with your own COBOL code or implicitly with the WHENEVER statement.

  Note: When your application uses SQL*Net to access a combination of local and remote databases concurrently, all the databases write to one SQLCA. There is not a different SQLCA for each database. For more information, see "Concurrent Logons" on page 3-46.

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.

Note: If you declare SQLCODE instead of the SQLCA in a particular compilation unit, Pro*COBOL allocates an internal SQLCA for that unit. Your host program cannot access the internal SQLCA.

When MODE={ANSI|ANSI14}, you must declare either SQLSTATE (see "Declaring SQLSTATE" on page 9-6) or SQLCODE (see "Declaring SQLCODE" on page 9-5) 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.

### What's in the SQLCA?

The SQLCA contains runtime information about the execution of SQL statements, such as Oracle8 error codes, warning flags, event information, rows-processed count, and diagnostics.

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

#### Figure 9-2 SQLCA Variable Declarations for Pro*COBOL

### 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 Oracle8 to communicate with the program.

  Attention: When using multi-byte NCHAR 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 Oracle8.

#### 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, Oracle8 must parse it; that is, examine it to make sure it follows syntax rules and refers to valid database objects. If Oracle8 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, Oracle8 sets SQLERRD(5) to zero. Oracle8 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 Oracle8 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.

### SQLCA Structure

This section describes the structure of the SQLCA, its fields, and the values they can store.

#### 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:

#####

0 | Oracle8 executed the statement without detecting an error or exception.
> 0 | Oracle8 executed the statement but detected an exception. This occurs when Oracle8 cannot find a row that meets your WHERE-clause search condition or when a SELECT INTO or FETCH returns no rows.
< 0 | When MODE={ANSI|ANSI14|ANSI113}, +100 is returned to SQLCODE after an INSERT of no rows. This can happen when a subquery returns no rows to process.Oracle8 did not execute the statement because of a database, system, network, or application error. Such errors can be fatal. When they occur, the current transaction should, in most cases, be rolled back. Negative return codes correspond to error codes listed in Oracle8 Server Messages.

#### SQLERRM

This sub-record contains the following two fields:

#####

SQLERRML | This integer field holds the length of the message text stored in SQLERRMC.
SQLERRMC | This string field holds the message text for the error code stored in SQLCODE and can store up to 70 characters. For the full text of messages longer than 70 characters, use the SQLGLM function.Verify SQLCODE is negative before you reference SQLERRMC. If you reference SQLERRMC when SQLCODE is zero, you get the message text associated with a prior SQL statement.

#### 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:

#####

SQLERRD(1) | This field is reserved for future use.
SQLERRD(2) | This field is reserved for future use.
SQLERRD(3) | This field holds the number of rows processed by the most recently executed SQL statement. However, if the SQL statement failed, the value of SQLERRD(3) is undefined, with one exception. If the error occurred during a table operation, processing stops at the row that caused the error, so SQLERRD(3) gives the number of rows processed successfully. The rows-processed count is zeroed after an OPEN statement and incremented after a FETCH statement. For the EXECUTE, INSERT, UPDATE, DELETE, and SELECT INTO statements, the count reflects the number of rows processed successfully. The count does not include rows processed by an update or delete cascade. For example, if 20 rows are deleted because they meet WHERE-clause criteria, and 5 more rows are deleted because they now (after the primary delete) violate column constraints, the count is 20 not 25.
SQLERRD(4) | This field is reserved for future use.
SQLERRD(5) | This field holds an offset that specifies the character position at which a parse error begins in the most recently executed SQL statement. The first character occupies position zero.
SQLERRD(6) | This field is reserved for future use.

This table of single characters has eight elements. They are used as warning flags. Oracle8 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 Oracle8 assigns a truncated column value to an output host variable.

  Note: While Figure 9-2 illustrates SQLWARN as a table, it is implemented in Pro*COBOL as a group item with elementary PIC X items named SQLWARN0 through SQLWARN7. .

Descriptions of the fields in SQLWARN follow:

#####

SQLWARN(0) | This flag is set if another warning flag is set.
SQLWARN(1) | This flag is set if a truncated column value was assigned to an output host variable. This applies only to character data. Oracle8 truncates certain numeric data without setting a warning or returning a negative SQLCODE value.To find out if a column value was truncated and by how much, check the indicator variable associated with the output host variable. The (positive) integer returned by an indicator variable is the original length of the column value. You can increase the length of the host variable accordingly.
SQLWARN(2) | This flag is set if one or more nulls were ignored in the evaluation of a SQL group function such as AVG, COUNT, or MAX. This behavior is expected because, except for COUNT(*), all group functions ignore nulls. If necessary, you can use the SQL function NVL to temporarily assign values (zeros, for example) to the null column entries.
SQLWARN(3) | This flag is set if the number of columns in a query select list does not equal the number of host variables in the INTO clause of the SELECT or FETCH statement. The number of items returned is the lesser of the two.
SQLWARN(4) | This flag is set if every row in a table was processed by an UPDATE or DELETE statement without a WHERE clause. An update or deletion is called unconditional if no search condition restricts the number of rows processed. Such updates and deletions are unusual, so Oracle8 sets this warning flag. That way, you can roll back the transaction if necessary
SQLWARN(5) | This flag is set when an EXEC SQL CREATE {PROCEDURE|FUNCTION|PACKAGE|PACKAGE BODY} statement fails because of a PL/SQL compilation error.
SQLWARN(6) | This flag is no longer in use.
SQLWARN(7) | This flag is no longer in use.

#### SQLEXT

This string field is reserved for future use.

### PL/SQL Considerations

When your Pro*COBOL program executes an embedded PL/SQL block, not all fields in the SQLCA are set. For example, if the block fetches several rows, the rows-processed count, sqlerrd(3), is set to 1, not the actual number of rows fetched. So, you should rely only on the SQLCODE and SQLERRM fields in the SQLCA after executing a PL/SQL block.

### 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 Oracle8, 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. (Oracle8 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 Oracle8 stores the actual length of the error message.

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

### DSNTIAR

DB2 provides an assembler routine called DSNTIAR to obtain a form of the SQLCA that can be displayed. For users migrating to Oracle8 from DB2, Pro*COBOL provides DSNTIAR. DSNTIAR's 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:

0 | successful execution
4 | more data was available than could fit into the provided message
8 | the logical record length (LRECL) was not between 72 and 240
12 | the message area was not large enough (greater than 240)

### Using the WHENEVER Statement

By default, Pro*COBOL ignores Oracle8 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 Oracle8 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.

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

#### Conditions

#### SQLWARNING

SQLWARN(0) is set because Oracle8 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 Oracle8 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 because Oracle8 returned an error.

#### NOT FOUND or NOTFOUND

SQLCODE has a value of +1403 (+100 when MODE={ANSI|ANSI14| ANSI13}), because Oracle8 could not find a row that meets the search condition of a WHERE clause, or a SELECT INTO or FETCH returned no rows. When MODE={ANSI|ANSI14|ANSI13}, +100 is returned to SQLCODE after an INSERT of no rows.

Since DB2 returns a SQLCODE value of 100 when an END-OF-FETCH condition occurs after a SQL statement execution, Pro*COBOL 2 provides a new command line option for explicit control over the value returned when the END-OF-FETCH condition occurs. This option is:

    END_OF_FETCH = 100 | 1403 (default 1403)

The END_OF_FETCH option must be used on the command line or in a configuration file. For more details, see "END_OF_FETCH" on page 7-19

If the user specifies MODE=ANSI in a configuration file, Pro*COBOL 2 will implement the 100 at the END_OF_FETCH, overriding the default END_OF_FETCH=1403. If the user specifies MODE=ANSI and END_OF_FETCH=1403 in the configuration file, then Pro*COBOL 2 will implement the 1403 at the END_OF_FETCH. If the user specifies MODE=ANSI in the configuration file and END_OF_FETCH=1403 on the command line, Pro*COBOL 2 will again implement the 1403 at the END_OF_FETCH.

When Oracle8 detects one of the preceding conditions, you can have your program take any of the following actions.

#### 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 PERFORM

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

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

#### GOTO or GO TO

Your program branches to a labeled statement.

#### STOP

Your program stops running and uncommitted work is rolled back.

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

### Using the WHENEVER Statement in COBOL

Code the WHENEVER statement using the following syntax:

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

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" Oracle8 error
         IF SQLCA.SQLCODE = -1
         ...
    *    Check for "value too large" Oracle8 error
         ELSE IF SQLCA.SQLCODE = -1401
         ...
         ELSE
         ...
         END-IF.
         ...
     DEL-ERROR.
    *    Check for the number of rows processed.
         IF SQLCA.SQLERRD(3) = 0
         ...
         ELSE
         ...
         END-IF.
         ...

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

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

### Getting the Text of SQL Statements

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 (see Table 9-7) for the SQL command used in the statement

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 9-6 shows the host-language datatypes available for the parameters in the SQLGLS argument list.

Parameter | Datatype
--- | ---
SQLSTM | PIC X(n)
STMLEN | PIC S9(9) COMP
SQLFC | PIC S9(9) COMP

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. Table 9-7 shows the function code for each SQL command.

There are no SQL function codes for these statements:

- CONNECT
- COMMIT
- FETCH
- ROLLBACK
- RELEASE

Code | SQL Function | Code | SQL Function
--- | --- | --- | ---
01 | CREATE TABLE | 39 | AUDIT
02 | SET ROLE | 40 | NOAUDIT
03 | INSERT | 41 | ALTER INDEX
04 | SELECT | 42 | CREATE EXTERNAL DATABASE
05 | UPDATE | 43 | DROP EXTERNAL DATABASE
06 | DROP ROLE | 44 | CREATE DATABASE
07 | DROP VIEW | 45 | ALTER DATABASE
08 | DROP TABLE | 46 | CREATE ROLLBACK SEGMENT
09 | DELETE | 47 | ALTER ROLLBACK SEGMENT
10 | CREATE VIEW | 48 | DROP ROLLBACK SEGMENT
11 | DROP USER | 49 | CREATE TABLESPACE
12 | CREATE ROLE | 50 | ALTER TABLESPACE
13 | CREATE SEQUENCE | 51 | DROP TABLESPACE
14 | ALTER SEQUENCE | 52 | ALTER SESSION
15 | (not used) | 53 | ALTER USER
16 | DROP SEQUENCE | 54 | COMMIT
17 | CREATE SCHEMA | 55 | ROLLBACK
18 | CREATE CLUSTER | 56 | SAVEPOINT
19 | CREATE USER | 57 | CREATE CONTROL FILE
20 | CREATE INDEX | 58 | ALTER TRACING
21 | DROP INDEX | 59 | CREATE TRIGGER
22 | DROP CLUSTER | 60 | ALTER TRIGGER
23 | VALIDATE INDEX | 61 | DROP TRIGGER
24 | CREATE PROCEDURE | 62 | ANALYZE TABLE
25 | ALTER PROCEDURE | 63 | ANALYZE INDEX
26 | ALTER TABLE | 64 | ANALYZE CLUSTER
27 | EXPLAIN | 65 | CREATE PROFILE
28 | GRANT | 66 | DROP PROFILE
29 | REVOKE | 67 | ALTER PROFILE
30 | CREATE SYNONYM | 68 | DROP PROCEDURE
31 | DROP SYNONYM | 69 | (not used)
32 | ALTER SYSTEM SWITCH LOG | 70 | ALTER RESOURCE COST
33 | SET TRANSACTION | 71 | CREATE SNAPSHOT LOG
34 | PL/SQL EXECUTE | 72 | ALTER SNAPSHOT LOG
35 | LOCK TABLE | 73 | DROP SNAPSHOT LOG
36 | (not used) | 74 | CREATE SNAPSHOT
37 | RENAME | 75 | ALTER SNAPSHOT
38 | COMMENT | 76 | DROP SNAPSHOT

## 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 Oracle8-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 Oracle8 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 9-3 shows all the variables in the ORACA.

#### Figure 9-3 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.

### Choosing Runtime Options

The ORACA includes several option flags. Setting these flags by assigning them non-zero values allows 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 descriptions below will help you choose the options you need.

### ORACA Structure

This section describes the structure of the ORACA, its fields, and the values they can store.

#### 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 Oracle8 runtime library does the consistency checking and might issue error messages, which are listed in Oracle8 Error Messages. They are returned to the SQLCA just like Oracle8 error messages.

This flag has the following settings:

#####

0 | Disable cache consistency checking (the default).
1 | Enable cache consistency checking.

#### ORADBGF

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

#####

0 | Disable all DEBUG operations (the default).
1 | Enable all DEBUG operations.

#### ORAHCHF

If the master DEBUG flag (ORADBGF) is set, this flag tells the Oracle8 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:

#####

0 | Disable all DEBUG operations (the default).
1 | Enable all DEBUG operations.

#### ORASTXTF

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

#####

0 | Never save the SQL statement text (the default).
1 | Save the SQL statement text on SQLERROR only.
2 | Save the SQL statement text on SQLERROR or SQLWARNING.
3 | Always save the SQL statement text.

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 Oracle8. It contains the following two fields:

#####

ORASTXTL | This integer field holds the length of the current SQL statement.
ORASTXTC | This string field holds the text of the current SQL statement. At most, the first 70 characters of text are saved.

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:

#####

ORASFNML | This integer field holds the length of the filename stored in ORASFNMC.
ORASFNMC | This string field holds the filename. At most, the first 70 characters are stored.

#### ORASLNR

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

#### Cursor Cache Statistics

The variables below 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 Oracle8 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 Oracle8 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 D.

### ORACA Example

The following program prompts for a department number, inserts the name and salary of each employee in that department into one of two tables, then displays diagnostic information from the ORACA:

     IDENTIFICATION DIVISION.
     PROGRAM-ID. ORACAEX.
     ENVIRONMENT DIVISION.
     DATA DIVISION.
     WORKING-STORAGE SECTION.
         EXEC SQL INCLUDE SQLCA END-EXEC.
         EXEC SQL INCLUDE ORACA END-EXEC.

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

         EXEC SQL BEGIN DECLARE SECTION END-EXEC.
            01 USERNAME       PIC X(20).
            01 PASSWORD       PIC X(20).
            01 EMP-NAME       PIC X(10) VARYING.
            01 DEPT-NUMBER    PIC S9(4) COMP.
            01 SALARY         PIC S9(6)V99
                              DISPLAY SIGN LEADING SEPARATE.
         EXEC SQL END DECLARE SECTION END-EXEC.

     PROCEDURE DIVISION.
         DISPLAY "Username? " WITH NO ADVANCING.
         ACCEPT USERNAME.
         DISPLAY "Password? " WITH NO ADVANCING.
         ACCEPT PASSWORD.
         EXEC SQL
             WHENEVER SQLERROR GOTO SQL-ERROR
         END-EXEC.
         EXEC SQL
             CONNECT :USERNAME IDENTIFIED BY :PASSWORD
         END-EXEC.
         DISPLAY "Connected to Oracle".

    * -- set flags in the ORACA
    * -- enable debug operations
         MOVE 1 TO ORADBGF.
    * -- enable cursor cache consistency check
         MOVE 1 TO ORACCHF.
    * -- always save the SQL statement
         MOVE 3 TO ORASTXTF.
         DISPLAY "Department number? " WITH NO ADVANCING.
         ACCEPT DEPT-NUMBER.
         EXEC SQL DECLARE EMPCURSOR CURSOR FOR
             SELECT ENAME, SAL + NVL(COMM,0)
             FROM EMP
             WHERE DEPTNO = :DEPT-NUMBER
         END-EXEC.
         EXEC SQL OPEN EMPCURSOR END-EXEC.
         EXEC SQL
             WHENEVER NOT FOUND GOTO NO-MORE
         END-EXEC.
     LOOP.
         EXEC SQL
             FETCH EMPCURSOR INTO :EMP-NAME, :SALARY
         END-EXEC.
         IF SALARY < 2500
             EXEC SQL
                 INSERT INTO PAY1 VALUES (:EMP-NAME, :SALARY)
             END-EXEC
         ELSE
             EXEC SQL
                 INSERT INTO PAY2 VALUES (:EMP-NAME, :SALARY)
             END-EXEC
         END-IF.
         GO TO LOOP.

     NO-MORE.
         EXEC SQL CLOSE EMPCURSOR END-EXEC.
         EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
         EXEC SQL COMMIT WORK RELEASE END-EXEC.
         DISPLAY "(NO-MORE.) Last SQL statement: ", ORASTXTC.
         DISPLAY "... at or near line number:  ", ORASLNR.
         DISPLAY  " ".
         DISPLAY "          Cursor Cache Statistics".
         DISPLAY "-------------------------------------------".
         DISPLAY "Maximum value of MAXOPENCURSORS     ", ORAHOC.
         DISPLAY "Maximum open cursors required:      ", ORAMOC.
         DISPLAY "Current number of open cursors:     ", ORACOC.
         DISPLAY "Number of cache reassignments:      ", ORANOR.
         DISPLAY "Number of SQL statement parses:     ", ORANPR.
         DISPLAY "Number of SQL statement executions: ", ORANEX.
         STOP RUN.

     SQL-ERROR.
         EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
         EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
         DISPLAY "(SQL-ERROR.) Last SQL statement: ", ORASTXTC.
         DISPLAY "... at or near line number:  ", ORASLNR.
         DISPLAY " ".
         DISPLAY "          Cursor Cache Statistics".
         DISPLAY "-------------------------------------------".
         DISPLAY "MAXIMUM VALUE OF MAXOPENCURSORS     ", ORAHOC.
         DISPLAY "Maximum open cursors required:      ", ORAMOC.
         DISPLAY "Current number of open cursors:     ", ORACOC.
         DISPLAY "Number of cache reassignments:      ", ORANOR.
         DISPLAY "Number of SQL statement parses:     ", ORANPR.
         DISPLAY "Number of SQL statement executions: ", ORANEX.
         STOP RUN.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index