Release 1.8
Part Number A87540-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Pro*PL/I Supplement to the Oracle Precompilers Guide Release 1.8 Part Number A87540-01 |
|
This chapter discusses error reporting and recovery. You learn how to handle errors and status changes using SQLSTATE, the SQLCA, SQLCODE and the WHENEVER statement. You also learn how to diagnose problems using the ORACA.
Release 1.6 provides forward and backward compatibility with regard to checking the outcome of executing SQL statements. The SQLCA data structure containing status information and SQLCODE status variable can be used in the same manner as in previous releases. The SQLSTATE status variable is introduced in release 1.6.
When MODE=ANSI, you must declare SQLSTATE or SQLCODE. Declaring the SQLCA is optional. When MODE=ORACLE, not declaring the SQLCA causes compile time warnings and runtime errors.
Unlike SQLCODE, which stores signed integers and can be declared outside the Declare Section, SQLSTATE stores 5-character strings and must be declared inside the Declare Section. You declare SQLSTATE as:
DCL SQLSTATE CHAR(5);
Note: SQLSTATE must be declared with exactly 5 characters.
SQLSTATE status codes consist of a 2-character class code followed by a 3-character subclass code. Aside from class code 00 ("successful completion"), the class code denotes a category of exceptions. And, 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 subconditions. All other subclass codes are reserved for implementation-defined subconditions. Figure 2-1 shows the coding scheme.
Table 2-1 shows the classes predefined by SQL92.
Note: The class code HZ is reserved for conditions defined in International Standard ISO/IEC DIS 9579-2, Remote Database Access.
Table 2-2 shows how SQLSTATE status codes and conditions are mapped to Oracle errors. Status codes in the range 60000 .. 99999 are implementation-defined.
The following rules apply to using SQLSTATE with SQLCODE or the SQLCA when you precompile with the option setting MODE=ANSI. SQLSTATE must be declared inside a Declare Section; otherwise, it is ignored.
If you declare SQLSTATE
If you do not declare SQLSTATE
You can learn the outcome of the most recent executable SQL statement by checking SQLSTATE explicitly with your own code or implicitly with the WHENEVER SQLERROR statement. Check SQLSTATE only after executable SQL statements and PL/SQL statements.
When MODE={ANSI | ANSI14} and you have not declared SQLSTATE, you must declare a long integer variable named SQLCODE inside or outside the Declare Section. Upper case is required. An example follows:
/* Declare host and indicator variables */ EXEC SQL BEGIN DECLARE SECTION; ... EXEC SQL END DECLARE SECTION; /* Declare status variable */ DCL SQLCODE FIXED BIN(31);
After every SQL operation, Oracle 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, the precompiler allocates an internal SQLCA for that unit. Your host program cannot access the internal SQLCA. If you declare the SQLCA and SQLCODE, Oracle returns the same status code to both after every SQL operation.
When MODE={ANSI13 | Oracle}, if you declare SQLCODE, it is not used.
Oracle uses the SQLCA to store status information passed to your program at run time. The SQLCA 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 PL/I code, or implicitly with the WHENEVER statement.
When MODE=ORACLE (the default) or MODE=ANSI13, you must declare the SQLCA by hardcoding it or by copying it into your program with the INCLUDE statement.
When MODE={ANSI | ANSI14}, declaring the SQLCA is optional. However, you must declare a status variable named SQLCODE. SQL92 specifies a similar status variable named SQLSTATE, which you can use with or without SQLCODE.
After executing a SQL statement, the Oracle Server returns a status code to the SQLSTATE variable currently in scope. The status code indicates whether the 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.
Unlike SQLCODE, which stores only error codes, SQLSTATE stores error and warning codes. Furthermore, the SQLSTATE reporting mechanism uses a standardized coding scheme. Thus, SQLSTATE is the preferred status variable. Under SQL92, SQLCODE is a "deprecated feature" retained only for compatibility with SQL89 and likely to be removed from future versions of the standard.
To declare the SQLCA, copy it into your program with the
INCLUDE statement
EXEC SQL INCLUDE SQLCA;
or hardcode it as shown below:
DCL 1 SQLCA,
2 SQLCAID CHAR(8) INIT('SQLCA'),
2 SQLCABC FIXED BIN (31) INIT(136),
2 SQLCODE FIXED BIN (31),
2 SQLERRM CHAR (70) VAR,
2 SQLERRP CHAR (8) INIT('SQLERRP'),
2 SQLERRD (6) FIXED BIN (31),
2 SQLWARN,
3 SQLWARN0 CHAR (1),
3 SQLWARN1 CHAR (1),
3 SQLWARN2 CHAR (1),
3 SQLWARN3 CHAR (1),
3 SQLWARN4 CHAR (1),
3 SQLWARN5 CHAR (1),
3 SQLWARN6 CHAR (1),
3 SQLWARN7 CHAR (1),
2 SQLEXT, CHAR (8) INIT('SQLEXT');
Not declaring the SQLCA when MODE=Oracle results in compile time warnings, and causes runtime errors.
Your Pro*PL/I program can have more than one SQLCA. The SQLCA should not be INCLUDEd outside of a procedure block, since the elements in it are not declared with the STATIC storage class. Oracle returns information to the SQLCA that is in the scope of the SQL statement that caused the error or warning condition. The name of this structure must be SQLCA, since it is referred to by precompiler-generated code.
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.
The key components of Pro*PL/I error reporting depend on several fields in the SQLCA.
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 PL/I code.
Warning flags are returned in the SQLCA variables SQLWARN0 through SQLWARN7, which you can check implicitly with WHENEVER SQLWARNING, or explicitly with your own PL/I code. These warning flags are useful for detecting runtime conditions not considered errors by Oracle.
The number of rows processed by the most recently executed SQL statement is recorded 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.
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 10th 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.
The error code and message for Oracle errors are available in the SQLCA variable SQLERRM. For example, you might place the following statements in an error-handling routine:
/* Handle SQL execution errors. */ PUT EDIT(SQLCA.SQLERRM)(A(70)); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK WORK RELEASE ...
At most, the first 70 characters of message text are stored. For messages longer than 70 characters, you must call the SQLGLM function.
The SQLCA can accommodate error messages of up to 70 characters in length. To get the full text of longer (or nested) error messages, you need the SQLGLM procedure. If connected to Oracle, you can call SQLGLM using the syntax
CALL SQLGLM (MSG_BUF, BUF_SIZE, MSG_LENGTH);
where:
Is the buffer in which you want Oracle to store the error message. Oracle blank-pads to the end of this buffer.
Is an integer variable that specifies the maximum length of MSG_BUF in bytes.
Is an integer variable in which Oracle stores the actual length of the error message.
The maximum length of an Oracle error message is 196 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 you specify for BUF_SIZE. In the following example, you use SQLGLM to get an error message of up to 200 characters in length:
TEST: PROC OPTIONS(MAIN); /* Declare variables for the function call. */ DCL MSG_BUF CHAR(200), /* buffer for message text */ BUF_SIZE FIXED BIN(31) INIT(200), /* size in bytes */ MSG_LEN FIXED BIN(31); /* length of message text */ WHENEVER SQLERROR GOTO ERROR_PRINT; ... ERROR_PRINT: /* Get full text of error message. */ CALL SQLGLM(MSG_BUF, BUF_SIZE, MSG_LEN); /* Print the text. */ PUT SKIP EDIT (MSG_BUF) (A(MSG_LEN)); ...
Notice that SQLGLM is called only when a SQL error has occurred. Always make sure SQLCA.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.
By default, the Pro*PL/I 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, calling a procedure, branching to a labeled statement, or stopping.
You code the WHENEVER statement using the following syntax:
EXEC SQL WHENEVER <condition> <action>
You can have Oracle automatically check the SQLCA for any of the following conditions:
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 entering and exiting a procedure apply. However, passing parameters to the subroutine is not allowed. Furthermore, the subroutine must not return a value.
In the following example, you use WHENEVER SQLERROR DO statements to handle specific errors:
... EXEC SQL WHENEVER SQLERROR DO CALL INSERT_ERROR; ... EXEC SQL INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES(:MY_EMPNO, :MY_ENAME, :MY_DEPTNO); ... INSERT_ERROR: PROCEDURE; /* test for "duplicate key value" Oracle error */ IF (SQLCA.SQLCODE = -1) THEN DO; ... /* test for "value too large" Oracle error */ ELSE IF (SQLCA.SQLCODE = -1401) DO; ... /* etc. */ END; END INSERT_ERROR
Notice how the procedure checks variables in the SQLCA to determine a course of action.
For more information about the WHENEVER conditions and actions, see Chapter 7 of the Programmer's Guide to the Oracle Precompilers.
Because WHENEVER is a declarative statement, its scope is positional, not logical. That is, it tests all executable SQL statements that physically (not logically) follow it in your program. 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.
You might want to place WHENEVER statements at the beginning of each block that contains SQL statements. That way, SQL statements in one block will not reference WHENEVER actions in another block, causing errors at compile or run time.
Careless use of WHENEVER can cause problems. For example, the following code enters an infinite loop if the DELETE statement sets NOT FOUND because no rows meet the search condition:
/* Improper use of WHENEVER */ EXEC SQL WHENEVER NOT FOUND GOTO DO_DELETE; DO J = 1 TO N_FETCH; EXEC SQL FETCH EMP_CURSOR INTO :MY_ENAME, :MY_SAL; ... END; DO_DELETE: EXEC SQL DELETE FROM EMP WHERE EMPNO = :MY_EMPNO;
In the next example, you handle the NOT FOUND condition properly by resetting the GOTO target:
/* Proper use of WHENEVER */ EXEC SQL WHENEVER NOT FOUND GOTO DO_DELETE; DO J = 1 TO N_FETCH; EXEC SQL FETCH EMP_CURSOR INTO :MY_ENAME, :MY_SAL; ... END; DO_DELETE: EXEC SQL WHENEVER NOT FOUND GOTO WHAT_NEXT; EXEC SQL DELETE FROM EMP WHERE EMPNO = :MY_EMPNO; ... WHAT_NEXT: ...
Also, make sure all SQL statements governed by a WHENEVER ... GOTO statement can branch to the GOTO label. The following code results in a compile time error because the UPDATE statement in PROC2 is not within the scope of LABEL_A in PROC1:
PROC1: PROC(); ... EXEC SQL WHENEVER SQLERROR GOTO LABEL_A; EXEC SQL DELETE FROM EMP WHERE DEPTNO = :DEPT_NUMBER; ... LABEL_A: PUT SKIP LIST ('Error occurred'); END PROC1; PROC2: PROC(); ... EXEC SQL UPDATE EMP SET SAL = SAL * 1.20 WHERE JOB = 'PROGRAMMER'; ... END PROC2;
The SQLCA handles standard SQL communications. The ORACA is a similar data structure copied or hardcoded into 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.
To declare the ORACA, you can copy it into your main program with the INCLUDE statement, as follows:
/* Copy in the Oracle Communications Area (ORACA). */ EXEC SQL INCLUDE ORACA;
Alternatively, you can hardcode it as follows:
DCL 1 ORACA 2 ORACAID CHAR (8) INIT ('ORACA') 2 ORACABC FIXED BIN (31) INIT (176) 2 ORACCHF FIXED BIN (31) INIT (0) 2 ORADBGF FIXED BIN (31) INIT (0) 2 ORAHCHF FIXED BIN (31) INIT (0) 2 ORASTXTF FIXED BIN (31) INIT (0) 2 ORASTXT CHAR (70) VAR INIT ('') 2 ORASFNM CHAR (70) VAR INIT ('') 2 ORASLNR FIXED BIN (31) INIT (0) 2 ORAHOC FIXED BIN (31) INIT (0) 2 ORAMOC FIXED BIN (31) INIT (0) 2 ORACOC FIXED BIN (31) INIT (0) 2 ORANOR FIXED BIN (31) INIT (0) 2 ORANPR FIXED BIN (31) INIT (0) 2 ORANEX FIXED BIN (31) INIT (0)
To enable the ORACA, you must set the ORACA precompiler option to YES, either on the command line with
ORACA=YES
or inline with
/* Enable the ORACA. */ EXEC Oracle OPTION (ORACA=YES);
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 contains option settings, system statistics, and extended diagnostics. The listing above shows all the variables in the ORACA.
For a full description of the ORACA, its fields, and the values the fields can store, see Chapter 7 of the Programmer's Guide to the Oracle Precompilers.
|
 Copyright © 1996-2001 Oracle Corporation. All Rights Reserved. |
|