Programmer's Guide to the Pro*Ada Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
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.
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.
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.
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));
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.
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.
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.
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.
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.
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.
function WARNING return boolean
function IF_WARNING return boolean
function IF_ERROR return boolean
function IF_NOT_FOUND return boolean
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:
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.
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>;
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.
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.
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.
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.
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.
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>>
....
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.
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.
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;
-- 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;
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |