Programmer's Guide to the Pro*Ada Precompiler Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



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

Handling Runtime Errors


An application program is more reliable if it anticipates runtime errors and attempts to recover from them. This chapter provides an in-depth discussion of Pro*Ada error reporting and recovery. You learn how to handle errors and status changes using status indicator variables, the WHENEVER statement, and the SQL Communications Area (SQLCA). The following topics are discussed:


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*Ada, 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 "no more 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

Error handling involves detecting errors in SQL statements as they occur, and making the error messages available to the program's user. To detect errors, you can either:

It is almost always easier to use the WHENEVER statement. However, there are cases where you might need to check explicitly for certain error conditions after a SQL statement. For example, suppose that in a block in your program you want to raise the exception named SQL_ERROR whenever a SQL statement causes an error condition. You put the declarative SQL statement

EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; 

near the beginning of the block, before the first executable SQL statement. Then you code the SQL_ERROR exception at the end of the block. Any runtime error in processing a SQL statement in the block causes the SQL_ERROR exception to be raised.

However, there are occasions when you might not want a SQL statement to raise the exception. For example, if you are dropping a table

EXEC SQL DROP TABLE my_table; 

and the table does not exist, Oracle will return the error "table or view does not exist" (Oracle error number -942), and will raise the SQL_ERROR exception. To avoid raising an error in this case, turn off general error checking just before the DROP TABLE statement, check explicitly for an error, raise the SQL_ERROR exception if the error is not -942, and then reinstate general error handling with another EXEC SQL WHENEVER statement. The steps to do this are

-- first part of block, with no SQL statements 
... 
-- set up error detection 
EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; 
... -- some SQL statements... 
 
-- prepare to drop a table 
-- first turn off automatic error detection 
EXEC SQL WHENEVER SQLERROR CONTINUE; 
EXEC SQL DROP TABLE my_table; 
-- after the statement, check the SQL Communications Area 
-- error code 
if SQLCA.SQLCODE /= -942 then 
    raise SQL_ERROR;          -- raise the exception if error 
-- other than 942 (table not found) 
else 
    PUT_LINE("Table MY_TABLE dropped."); 
end if; 
 
-- reinstate automatic error detection 
EXEC SQL WHENEVER SQLERROR raise SQL_ERROR; 

The WHENEVER statement and the SQL Communications Area (SQLCA), along with the functions and procedures that provide error messages, are explained in detail in the remaining sections of this chapter.

Error Functions

Pro*Ada provides a set of predefined procedures and functions that return error or warning status information, by accessing components of the SQLCA. These procedures and functions are included in the ORACLE.ERROR package, and are described in this chapter together with the relevant components of SQLCA.


Key Components of Error Reporting

There are several alternatives that you can use to detect errors and status changes in an application. This chapter describes these alternatives. However, no specific recommendations are made about what method you should use. The method is dictated by the design of the application program or tool that you are building.

Status Variables

You can declare a separate status variable, SQLSTATE or SQLCODE, examine its value after each executable SQL statement, and take the appropriate action. The action might include calling an error-reporting function, then exiting the program if the error detected is unrecoverable. Alternatively, you might be able to adjust data, or control variables, and retry the action. See "SQLSTATE Status Variable" below, and "Declaring SQLCODE" [*] for more information.

The SQLCA

The SQLCA is a record whose components contain error, warning, and status information updated by Oracle whenever a SQL statement is executed. Thus, the SQLCA always reflects the outcome of the most recent SQL operation. To determine the outcome, you can check variables in the SQLCA. See "Using the SQL Communications Area (SQLCA)" [*] for further information.


SQLSTATE Status Variable

The precompiler command-line option MODE governs ANSI/ISO compliance within your Pro*Ada program. When MODE=ANSI or MODE=ANSI14, declaring the SQLCA data structure is optional. However, you must declare a status variable named SQLCODE. SQL92 specifies a similar status variable named SQLSTATE that 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 and the not found condition, 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.

Declaring SQLSTATE

When MODE=ANSI or MODE=ANSI14, you must declare SQLSTATE or SQLCODE. Declaring the SQLCA is optional. When MODE=ANSI13 or MODE=ORACLE, SQLSTATE is ignored whether or not it is declared.

Unlike SQLCODE, which stores signed integers and can be declared outside the Declare Section, SQLSTATE stores five-character alphanumeric strings and must be declared inside the Declare Section.

You declare SQLSTATE as shown below.

EXEC SQL BEGIN DECLARE SECTION;
...
SQLSTATE        : SQL_STANDARD.SQLSTATE_TYPE;
...
EXEC SQL END DECLARE SECTION;

You can then display the SQLSTATE code as shown below.

EXEC SQL ...;
put("The SQLSTATE code is: ");
put_line(string(SQLSTATE));

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 subconditions. All other subclass codes are reserved for implementation-defined subconditions. Figure 5 - 1 shows the coding scheme.

Figure 5 - 1. SQLSTATE Coding Scheme

Table 5 - 1 shows the classes predefined by SQL92.

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
Table 5 - 1. Predefined Classes

Note: The class code HZ is reserved for conditions defined in International Standard ISO/IEC DIS 9579-2, Remote Database Access.

Table 5 - 2 lists SQLSTATE status codes and conditions. In some cases, several Oracle errors map to the status code. In other cases, no Oracle error maps to the status code (so the last column is empty). Status codes in the range 60000 .. 99999 are implementation defined.

Code Condition Oracle Error(s)
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 ORA-02700 .. 02899 ORA-03100 .. 03199 ORA-06200 .. 06249
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 (UCE/CUC mismatch) SQL-02101
82102 inconsistent cursor cache (no CUC entry for UCE) SQL-02102
82103 inconsistent cursor cache (out-of-range CUC ref) SQL-02103
82104 inconsistent cursor cache (no CUC available) SQL-02104
82105 inconsistent cursor cache (no CUC entry in cache) SQL-02105
82106 inconsistent cursor cache (invalid 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 (out-of-range SIT ref) SQL-02109
82110 inconsistent host cache (invalid SQI 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
90000 debug events ORA-10000 .. 10999
99999 catch all all others
HZ000 remote database access
Table 5 - 2. SQLSTATE Codes

Using SQLSTATE

Table 5 - 3 gives the rules that apply to using SQLSTATE with SQLCODE or the SQLCA.

SQLSTATE MODE=ANSI or MODE=ANSI14 MODE=ORACLE
declared inside Declare Section Declaring SQLCODE is optional. If you declare SQLCODE inside the Declare Section, Oracle returns status codes to SQLSTATE and SQLCODE after every SQL operation. However, if you declare SQLCODE outside the Declare Section, Oracle returns a status code only to SQLSTATE. Oracle ignores SQLSTATE and SQLCODE.
declared outside Declare Section Oracle ignores SQLSTATE. You must declare SQLCODE inside or outside the Declare Section. Oracle returns a status code to SQLCODE after every SQL operation. Oracle ignores SQLSTATE and SQLCODE.
not declared You must declare SQLCODE inside or outside the Declare Section. Oracle returns a status code to SQLCODE after every SQL operation. Oracle ignores SQLCODE.
Table 5 - 3. SQLSTATE Rules

You can learn the outcome of the most recent SQL operation by checking SQLSTATE explicitly with your own code or implicitly with the WHENEVER SQLERROR and WHENEVER SQLWARNING statements.


Using the SQL Communications Area (SQLCA)

The SQLCA is a record, whose components contain error, warning, and status information updated by Oracle whenever a SQL statement is executed. Thus, the SQLCA always reflects the outcome of the most recent SQL operation. To determine the outcome, you can check variables in the SQLCA.

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

The SQLCA is a record that Pro*Ada defines, as follows:

type sqlca is record 
     sqlcaid   : string(1..8)        := "SQLCA   "; 
     sqlabc    : integer; 
     sqlcode   : integer             := 0; 
     sqlerrml  : unsigned_short      := 0; 
     sqlerrmc  : string(1..MAX_SQLCA_MSG_LEN) := 
                       (1..MAX_SQLCA_MSG_LEN => ' '); 
     sqlerrp   : string(1..8)        := "        "; 
     sqlerrd   : integer_array(1..6) := (1..6 => 0); 
     sqlwarn   : string(1..8)        := "        "; 
     sqlext    : string(1..8); 
end record; 

Note: The SQLCA is automatically declared in a Pro*Ada program.

After each embedded SQL statement is executed, a status code is returned to the SQLCA.SQLCODE variable. If Oracle detects an error while processing the SQL statement, a negative value is returned in SQLCODE. If an exceptional, but not necessarily fatal, condition is detected, a positive value greater than zero is returned in SQLCODE. If SQLCODE contains a zero just after the SQL statement executes, no error or warning condition occurred.

Oracle error and warning codes are listed in Oracle7 Server Messages. In addition to Oracle errors, the Pro*Ada runtime library may detect error conditions, and return an error code in SQLCODE. For more information on these error codes, see the Oracle7 Server Messages manual.

SQLCA values are undefined after a declarative statement. Oracle updates the SQLCA after every executable SQL statement. By checking Oracle return codes stored in the SQLCA, your program can determine the outcome of a SQL statement.

Warning Flags

Warning flags are returned in the SQLCA variables SQLWARN(1) through SQLWARN(8), which you can check implicitly or explicitly. These warning flags are useful for runtime conditions not considered errors by Oracle. For example, if an indicator variable is available, Oracle signals a warning after assigning a truncated column value to a host variable. If no indicator variable is available, Oracle issues an error message. See the sample program [*] for an example that shows how to access the warning flags.

Rows-Processed Count

The number of rows processed by the most recently executed SQL statement is returned in the SQLCA variable SQLCA.SQLERRD(3), which you can check explicitly.

Strictly speaking, this variable is not for error reporting, but it can help you avoid mistakes. For example, suppose you expect to delete about 10 rows from a table. After the deletion, you check SQLERRD(3) and find that 75 rows were processed. To be safe, you might want to roll back the deletion and examine your WHERE-clause search condition.

Parse Error Offset

Before executing a SQL statement, Oracle must parse it. That is, Oracle must 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 SQLCA.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.

By default, static SQL statements are checked for syntactic errors at precompile time. So, SQLCA.SQLERRD(5) is most useful for debugging dynamic SQL statements that your program accepts or builds at runtime.

Parse errors arise from missing, misplaced, or misspelled keywords, invalid options, nonexistent tables, and the like. For example, the dynamic SQL statement

UPDATE EMP SET jib = :JOB_TITLE WHERE empno = :EMP_NUMBER 

causes the parse error

ORA-00904: invalid column name 

because the column name job is misspelled. The value of SQLERRD(5) is 15 because the erroneous column name jib begins at the 16th character.

If your SQL statement does not cause a parse error, Oracle sets SQLCA.SQLERRD(5) to zero. Oracle also sets SQLCA.SQLERRD(5) to zero if a parse error begins at the first character (position zero). Check SQLCA.SQLERRD(5) only if SQLCA.SQLCODE is negative, which means that an error has occurred.

Obtaining Error Message Text

The error message for an Oracle error is available by using the MESSAGE functions and procedures defined in the ORACLE.ERROR package. The syntax for these subprograms is defined below.

To return the message string corresponding to the most recent error:

function MESSAGE return string 

To return the message string associated with an Oracle error code:

function MESSAGE (ERROR_CODE : integer) return string 

To return the message string associated with a non-negative Oracle error code:

function MESSAGE (NON_NEGATIVE_ERROR_CODE : natural) return string 

To obtain a VARCHAR error message corresponding to the most recent error:

procedure MESSAGE (MSG      : in out varchar) 

To obtain a message string and the length of the error message for the most recent error:

procedure MESSAGE (LENGTH   : out integer, 
                   MSG      : out string) 

To obtain a VARCHAR error message associated with an Oracle error code:

procedure MESSAGE (CODE     : integer, 
                   MSG      : in out varchar) 

To obtain a message string and the length of the string associated with an Oracle error code:

procedure MESSAGE (CODE     : integer, 
                   LENGTH   : out integer, 
                   MSG      : out string) 

To obtain a VARCHAR error message associated with a non-negative Oracle error code:

procedure MESSAGE (CODE     : natural, 
                   MSG      : in out varchar) 

To obtain a message string and the length of the string associated with a non-negative Oracle error code:

procedure MESSAGE (CODE     : natural, 
                   LENGTH   : out integer, 
                   MSG      : out string) 

Use the error message subprogram that is best suited to your program's requirements.

Status Indications

The following functions can be used to return indications of error or warning conditions:

function WARNING return boolean 
 
function IF_WARNING return boolean 
 
function IF_ERROR return boolean 
 
function IF_NOT_FOUND return boolean 

SQLCA Components

The components of the SQLCA are described below.

SQLCA.SQLCAID

This string is initialized to "SQLCA" when the SQLCA is allocated.

SQLCA.SQLABC

This component is set to the length in bytes of the SQLCA record.

SQLCA.SQLCODE

This integer field holds the status code of the most recently executed SQL statement. The status code indicates the outcome of the SQL operation and can be any of the following:

0

Means that Oracle executed the statement without detecting an error or exception.

>0

Means that Oracle executed the statement but detected an exception. This occurs when Oracle cannot find a row that meets your WHERE-clause search condition, or when a SELECT INTO or FETCH returns no rows.

When MODE={ANSI14 | ANSI13}, +100 is returned to SQLCODE after an INSERT of no rows. This can happen when a subquery returns no rows to process.

<0

Means that Oracle 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 be rolled back in most cases.

Negative return codes correspond to error codes listed in Oracle7 Server Messages.

Table 5 - 4 shows how the value of SQLCODE depends on the MODE option:

SQLCODE
Oracle Error or Warning Mode Scalars Arrays
ORA-01403 ORACLE ANSI13 ANSI14 +1403 +100 +100 +1403 +100 0
ORA-01406: fetched column value was truncated ORACLE ANSI13 ANSI14 -1405 0 0 -1406 0 n/a
Table 5 - 4. Effects of the MODE Option

SQLCA.SQLERRML

The length in bytes of the text contained in SQLCA.SQLERRMC.

Note: For performance reasons, the type of this variable is now UNSIGNED_SHORT. Existing applications that use this component must be recoded to convert it to an integer as it is used.

SQLCA.SQLERRMC

A character string of maximum length 70, the text of the error message that corresponds to the error code in SQLCODE.

SQLCA.SQLERRP

This component is not currently used.

SQLCA.SQLERRD

This component is an array of six integers that are used to describe the internal state of the Oracle RDBMS. Only the third and fifth elements are currently used. SQLERRD(3) contains the number of rows processed by DML operations such as INSERT or UPDATE. The error function ORACLE.SQLROWS returns the current value of SQLERRD(3).

SQLERRD(5) contains the Parse Error Offset. If a SQL statement fails to parse (this would occur when it is executed, or a cursor for it is PREPAREd), the starting character position of the parse error is found in SQLERRD(5). The first position in the statement is zero, not one.

SQLCA.SQLWARN

This is an array composed of eight characters that indicate warnings. A warning is set, for example, when Oracle ignores a NULL in computing an average. A warning is indicated when the value of the character is `W'. If that warning did not occur when the most recent SQL statement was executed, the value is blank.

If you declare the SQLCA, you can learn the outcome of the most recent SQL operation by checking SQLWARN explicitly with your own code or implicitly with the WHENEVER SQLWARNING statement.

The meanings of each component are:

SQLWARN(1)

This flag is set if another warning flag is set.

SQLWARN(2)

This flag is set if a truncated column value was assigned to an output host variable. This applies only to character data. Oracle truncates certain numeric data without setting a warning or returning a negative SQLCODE.

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(3)

With previous Oracle Precompilers, SQLWARN(3) was not used. However, beginning with the Oracle Precompilers Release 1.6, SQLWARN(3) indicates that 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, use the SQL function NVL to temporarily assign values (zeros, for example) to the null column entries.

SQLWARN(4)

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(5)

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 Oracle sets this warning flag. That way, you can roll back the transaction if necessary.

SQLWARN(6)

This flag is reserved for future use.

SQLWARN(7)

This flag is no longer in use.

SQLWARN(8)

This flag is no longer in use.

SQLCA.SQLEXT

This component is not currently used.


Declaring SQLCODE

When MODE=ANSI, you must declare an integer variable named SQLCODE. The type of SQLCODE is SQL_STANDARD.SQLCODE_TYPE. The error indications described above are returned to the SQLCODE variable, as well as to SQLCA.SQLCODE.


Using the WHENEVER Statement

By default, precompiled programs ignore Oracle error and warning conditions, and continue 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 an exception handler, or branching to a labeled statement.

You code the WHENEVER statement using the following syntax:

EXEC SQL WHENEVER <condition> <action>; 

Conditions

Oracle can automatically check the SQLCA for any of the following conditions.

SQLWARNING

SQLWARN(1) is set because Oracle returned a warning (one of the warning flags, SQLWARN(2) through SQLWARN(8), is also set), or SQLCODE has a positive value other than 1403. For example, SQLWARN(1) is set when Oracle assigns a truncated column value to an output host variable.

If you specify the MODE=ANSI13 or MODE=ANSI14 option, the "no data found" error code returned to SQLCODE becomes +100 instead of 1403. Also, no error is generated when Oracle assigns a truncated column value to an output host variable.

For more information about the MODE option, see Chapter

11, "Running the Pro*Ada Precompiler."

SQLERROR

SQLCODE has a negative value because Oracle returned an error.

NOT FOUND

SQLCODE has a value of 1403 (+100 if you specify MODE=ANSI13 or MODE=ANSI14) because Oracle could not find a row that meets your WHERE-clause search condition, or a SELECT INTO or FETCH returned no rows.

When MODE=ANSI13 or MODE=ANSI14, +100 is returned to SQLCODE after an INSERT of no rows.

Actions

When Oracle detects one of the preceding conditions, you can have your program take any of the following 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.

RAISE EXCEPTION_NAME

Your program transfers control to the exception that is in scope, with the exception condition EXCEPTION_NAME. See the sample program [*]9 - 19 for several examples of ... EXEC SQL WHENEVER ... RAISE ...

DO ROUTINE_CALL

Your program invokes a subprogram.

GOTO LABEL_NAME

Your program branches to a labeled statement.

Some Examples

If you want your program to

simply code the following WHENEVER statements before the first executable SQL statement:

EXEC SQL WHENEVER SQLWARNING CONTINUE; 
EXEC SQL WHENEVER SQLERROR RAISE ERROR_HANDLER; 
EXEC SQL WHENEVER NOT FOUND GOTO CLOSE_CURSOR; 
EXEC SQL WHENEVER NOT FOUND DO SQL_ERROR; 

In the following example, you use WHENEVER...RAISE statements to handle specific errors:

... 
EXEC SQL WHENEVER SQLERROR RAISE INSERT_ERROR; 
EXEC SQL INSERT INTO emp (empno, ename, deptno) 
    VALUES (:EMP_NUMBER, :EMP_NAME, :DEPT_NUMBER); 
EXEC SQL WHENEVER SQLERROR RAISE DELETE_ERROR; 
EXEC SQL DELETE FROM dept WHERE deptno = :DEPT_NUMBER; 
... 
    exception 
        when INSERT_ERROR    => 
            if SQLCA.SQLCODE = -1 then     -- duplicate key value 
            ... 
            elsif SQLCA.SQLCODE = -1401 then  -- value too large 
            ... 
            end if; 
        when DELETE_ERROR    => 
            if SQLCA.SQLERRD(3) = 0 then 
            ...               -- no rows deleted 
            end if; 
        when others    => 
            PUT_LINE("Unhandled exception condition"); 

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

Scope of WHENEVER

Because WHENEVER is a declarative statement, its scope is positional, not logical. That is, it tests all executable SQL statements that physically 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.

The example below shows an insidious bug that is caused by the programmer not taking into account that WHENEVER has positional, not logical, scope.

-- following statement establishes an exception handler 
-- for SQL errors 
EXEC SQL WHENEVER SQLERROR RAISE SQL_ERROR_HANDLER; 
... 
if SPECIAL_CASE then 
    ... 
    -- turn off the error handling 
    EXEC SQL WHENEVER SQLERROR CONTINUE; 
    EXEC SQL ROLLBACK WORK RELEASE; 
    exit; 
end if; 
-- DANGER!!  At this point, WHENEVER SQLERROR checking is 
-- turned off, since the declarative WHENEVER ... CONTINUE 
-- statement in the "if" takes effect at precompile time. 
-- (That is, it is not dependent on the program logic.) 

You should always bear in mind that the declarative SQL statements are like pragmas. They take effect at precompile time, not at runtime. Also, they do not obey the logical flow of control of your program.

Guidelines

The following guidelines will help you avoid some common pitfalls.

Placing the Statements

In general, code a WHENEVER statement before the first executable SQL statement in your program. This ensures that all ensuing errors are trapped because WHENEVER statements stay in effect to the end of a file, or the next WHENEVER statement.

Handling End-of-Data Conditions

Your program should be prepared to handle an end-of-data condition when using a cursor to fetch rows. If a FETCH returns no data, the program should branch to a labeled section of code where a CLOSE command is issued, as follows:

EXEC SQL WHENEVER NOT FOUND GOTO NO_MORE; 
... 
<<NO_MORE>> 
    ... 
    EXEC SQL CLOSE my_cursor; 
    ... 

A better solution for handling the WHENEVER NOT FOUND condition is to use a local exception handler. See the example program [*]9 - 19.

Avoiding Infinite Loops

If a WHENEVER SQLERROR GOTO statement branches to an error handling routine that includes an executable SQL statement, your program might enter an infinite loop if the SQL statement fails with an error. You can avoid this by coding WHENEVER SQLERROR CONTINUE before the SQL statement, as shown in the following example:

EXEC SQL WHENEVER SQLERROR GOTO SQL_ERROR; 
... 
<<SQL_ERROR>> 
    EXEC SQL WHENEVER SQLERROR CONTINUE; 
    EXEC SQL ROLLBACK WORK RELEASE; 
    ... 

Without the WHENEVER SQLERROR CONTINUE statement, a ROLLBACK error would branch to SQL_ERROR again, starting an infinite loop.

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 NO_MORE; 
loop 
    EXEC SQL FETCH emp_cursor INTO :EMP_NAME, :SALARY; 
    ... 
end loop; 
<<NO_MORE>> 
    EXEC SQL DELETE FROM emp WHERE empno = :EMP_NUMBER; 
     ... 

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 NO_MORE; 
loop 
    EXEC SQL FETCH emp_cursor INTO :EMP_NAME, :SALARY; 
... 
end loop; 
<<NO_MORE>> 
EXEC SQL WHENEVER NOT FOUND GOTO NO_MATCH; 
EXEC SQL DELETE FROM emp WHERE empno = :EMP_NUMBER; 
... 
<<NO_MATCH>> 
.... 

Maintaining Addressability

Make sure all SQL statements governed by a WHENEVER GOTO statement can branch to the GOTO label. The following code results in an Ada compile-time error because LABEL_A in PROC_1 is not within the scope of the INSERT statement in PROC_2:

procedure PROC_1 is 
begin 
    EXEC SQL WHENEVER SQLERROR GOTO LABEL_A; 
    EXEC SQL DELETE FROM emp WHERE DEPTNO = :DEPT_NUMBER; 
... 
<<LABEL_A>> 
... 
end PROC_1; 
procedure PROC_2 is 
begin 
    EXEC SQL INSERT INTO foobar VALUES (42, 43, 44); 
... 
end PROC_2; 

The label to which a WHENEVER GOTO statement branches must, of course, be in the same precompilation unit as the statement.


Using Indicator Variables

You can associate every host variable with an optional indicator variable, which indicates the value or condition of the host variable.

In error handling, the main use for indicator variables is detecting null or truncated values in output host variables. The values assigned to an indicator variable by Oracle have the following meanings:

- 1

The column value is null, so the value of the host variable is indeterminate.

0

The column value was assigned intact to the host variable.

> 0

A truncated column value was assigned to the host variable. The integer returned by the indicator variable is the original length of the column value.

If you specify the MODE=ANSI13 or MODE=ANSI14 option, no error is generated when Oracle assigns a truncated column value to an output host variable. However, if you specify MODE=ORACLE (the default), an error is generated.

An Indicator Example

The following example shows how an indicator variable can monitor a host variable:

EMP_NAME    : string(1..20); 
EMP_NUMBER  : integer; 
COMMISSION  : float; 
IND_COMM    : indicator; 
... 
PUT("Employee number: "); 
GET(EMP_NUMBER); 
... 
EXEC SQL SELECT ename, comm 
    INTO :EMP_NAME, :COMMISSION :IND_COMM 
    FROM emp 
    WHERE empno = :EMP_NUMBER; 
... 
if IND_COMM = -1 then    -- commission is null 
    PUT_LINE("Commission for " & EMP_NAME & " is not available"); 
else 
    PUT("Commission for "); 
    PUT(EMP_NAME); 
    PUT(" is "); 
    PUT(COMMISSION); 
    NEW_LINE; 
end if; 


Example Program

The following program demonstrates how you can handle warning and error conditions in your program. This program is available online in the Pro*Ada demo directory.

-- Copyright (c) 1994 by Oracle Corporation 
--  DEMERROR :
--       1)   Log on to ORACLE
--       2)   DECLARE a cursor for a SELECT statement
--       3)   FETCH into a variable that is too small
--       4)   Re-OPEN the cursor
--       5)   FETCH all the rows, exiting on a NOT FOUND
--       6)   CLOSE the cursor and log off

with text_io, integer_text_io;

procedure DEMERROR is

use text_io, integer_text_io;

USER          : constant STRING := "SCOTT/TIGER";
BAD_ENAME     : STRING(1..2);
IND_ENAME     : ORACLE.INDICATOR;
ORACLE_ERROR  : exception;

    -- a procedure to print all the warning flags
    procedure PRINT_WARNINGS is
    begin
        for I in 1..8 loop
            case I is
                when 1 => PUT("WARNING                : ");
                when 2 => PUT("TRUNCATED              : ");
                when 3 => PUT("NULL_VALUE_IN_FUNCTION : ");
                when 4 => PUT("INTO_COUNT_ERROR       : ");
                when 5 => PUT("WHERE_MISSING          : ");
                when 6 => PUT("RESERVED               : ");
                when 7 => PUT("RROLLBACK_REQUIRED     : ");
                when 8 => PUT("ROW_CHANGE             : ");
            end case;
            if SQLCA.SQLWARN(I) = 'W' then
                PUT_LINE("  ON ");
            else
                PUT_LINE("  OFF");
            end if;
        end loop;
    end PRINT_WARNINGS;

-- The WHENEVER statement is the most important error checking
-- tool.  It should be used in all modules that contain
-- "EXEC SQL" statements.
-- Note:    the program could check for errors (in SQLCODE, for example)
--          after each SQL statement, but using the WHENEVER
--          clause makes debugging and maintenance easier.

EXEC SQL WHENEVER SQLERROR raise ORACLE_ERROR;

begin       -- ERROR_SAMPLE
    EXEC SQL CONNECT :USER;

    -- Assign a SELECT statement to a cursor
    EXEC SQL DECLARE CURS CURSOR FOR
        SELECT ename FROM emp;

    -- Open the cursor; execute the statement
    EXEC SQL OPEN CURS;

    -- The SQLWARNING flags are used to detect non-fatal "errors".
    -- For example, if ORACLE truncates a string because it would
    -- not fit in a host variable, this is considered an error.
    -- However, if an indicator variable is associated with the
    -- host variable, truncation is considered a warning
    -- condition.
    EXEC SQL WHENEVER SQLWARNING raise ORACLE_WARNING;

    -- Localize the SQL_WARN exception
    WATCH_WARN :
    declare ORACLE_WARNING : exception;
    begin
        EXEC SQL FETCH CURS INTO :BAD_ENAME :IND_ENAME;

        exception
            when ORACLE_WARNING =>
                PUT_LINE("WARNING occured");
                PRINT_WARNINGS;
                NEW_LINE;
                PUT("The indicator value was: ");
                PUT(INTEGER(IND_ENAME), WIDTH => 1);
                NEW_LINE;
    end WATCH_WARN;

    -- Turn off the warnings.  This must be done because ORACLE_WARNING
    -- is not known to the rest of the program.
    EXEC SQL WHENEVER SQLWARNING CONTINUE;
    -- CLOSE and re-OPEN the cursor. The first row was fetched,
    -- but not properly stored.  Therefor, to get the row back, we
    -- must re-execute the statement.
    EXEC SQL CLOSE CURS;

    NEW_LINE;
    PUT_LINE("Re-opening the cursor...");

    EXEC SQL OPEN CURS;

    EXEC SQL WHENEVER NOT FOUND raise ALL_FETCHED;

    -- turn warnings back on

    EXEC SQL WHENEVER SQLWARNING raise ORACLE_WARNING;

    FETCH_LOOP: loop
        declare ALL_FETCHED : exception;
        ORACLE_WARNING      : exception;
        DUMMY               : STRING(1..2);        
        IND_DUMMY           : ORACLE.INDICATOR;
    begin
        EXEC SQL FETCH CURS INTO :DUMMY :IND_DUMMY;
        exception
            when ORACLE_WARNING => PRINT_WARNINGS;
                NEW_LINE;
                PUT("The indicator value was: ");
                PUT(INTEGER(IND_DUMMY));
                NEW_LINE;
            when ALL_FETCHED => PUT(SQLCA.SQLERRD(3), 2);
                PUT_LINE(" rows were fetched.");
                NEW_LINE;
                EXEC SQL CLOSE CURS;
            exit;
        end;
    end loop FETCH_LOOP;

    EXEC SQL WHENEVER SQLWARNING CONTINUE;
    EXEC SQL COMMIT RELEASE;

    exception
        -- To prevent the RELEASE statement from raising an error:
        EXEC SQL WHENEVER SQLERROR CONTINUE;

        when ORACLE_ERROR =>
            PUT_LINE(ORACLE.ERROR.MESSAGE);
            EXEC SQL ROLLBACK RELEASE;
end DEMERROR;




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