Pro*COBOL Supplement to the Oracle Precompilers | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
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).
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 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.
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" .
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" .
SQLCODE is recognized as a status variable if and only if at least one of the following criteria is satisfied:
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.
* 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.
* 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.
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.
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.
* 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.
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.
* 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.
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.
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.
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.
* 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.
...
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.
Figure 2 - 2. ORACA Variable Declarations for Pro*COBOL
* Include the Oracle Communications Area (ORACA).
EXEC SQL INCLUDE ORACA END-EXEC.
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.
![]() ![]() Prev Next |
![]() Copyright © 1997 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |