9 Handling Runtime Errors

9.2.1 Status Variables

You can declare a separate status variable, SQLSTATE or SQLCODE, examine its value after each executable SQL statement, and take appropriate action. The action might be calling an error-reporting function, then exiting the program if the error is unrecoverable. Or, you might be able to adjust data or control variables and retry the action.

9.2.2 The SQL Communications Area

Another alternative that you can use is to include the SQL Communications Area structure (sqlca) in your program. This structure contains components that are filled in at runtime after the SQL statement is processed by Oracle.

The SQLCA is defined in the header file sqlca.h, which you include in your program using either of the following statements:

• EXEC SQL INCLUDE SQLCA;

• #include <sqlca.h>

Oracle updates the SQLCA after every executable SQL statement. (SQLCA values are unchanged after a declarative statement.) By checking Oracle return codes stored in the SQLCA, your program can determine the outcome of a SQL statement. This can be done in the following two ways:

• Implicit checking with the WHENEVER directive

• Explicit checking of SQLCA components

You can use WHENEVER directives, code explicit checks on SQLCA components, or do both.

The most frequently-used components in the SQLCA are the status variable (sqlca.sqlcode), and the text associated with the error code (sqlca.sqlerrm.sqlerrmc). Other components contain warning flags and miscellaneous information about the processing of the SQL statement.

When more information is needed about runtime errors than the SQLCA provides, you can use the ORACA. The ORACA is a C struct that handles Oracle communication. It contains cursor statistics, information about the current SQL statement, option settings, and system statistics.

9.3.1 Declaring SQLSTATE

When MODE=ANSI, you must declare SQLSTATE or SQLCODE. Declaring the SQLCA is optional. When MODE=ORACLE, if you declare SQLSTATE, it is not used.

Unlike SQLCODE, which stores signed integers and can be declared outside the Declare Section, SQLSTATE stores 5-character null-terminated strings and must be declared inside the Declare Section. You declare SQLSTATE as

char  SQLSTATE[6];  /* Upper case is required. */ 

9.3.2 SQLSTATE Values

SQLSTATE status codes consist of a 2-character class code immediately 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 9-1 shows the coding scheme.

Figure 9-1 SQLSTATE Coding Scheme

Description of the illustration lnpcc008.gif

Table 9-1 shows the classes predefined by SQL92.

Table 9-2 shows how SQLSTATE status codes and conditions are mapped to Oracle errors. Status codes in the range 60000 to 99999 are implementation-defined.

9.3.3 Using SQLSTATE

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.

9.5.1 Status Codes

Every executable SQL statement returns a status code to the SQLCA variable sqlcode, which you can check implicitly with the WHENEVER directive or explicitly with your own code.

A zero status code means that Oracle executed the statement without detecting an error or exception. A positive status code means that Oracle executed the statement but detected an exception. A negative status code means that Oracle did not execute the SQL statement because of an error.

9.5.2 Warning Flags

Warning flags are returned in the SQLCA variables sqlwarn[0] through sqlwarn[7], which you can check implicitly or explicitly. These warning flags are useful for runtime conditions not considered errors by Oracle. If no indicator variable is available, Oracle issues an error message.

9.5.3 Rows-Processed Count

The number of rows processed by the most recently executed SQL statement is returned in the SQLCA variable sqlca.sqlerrd[2], 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 ten rows from a table. After the deletion, you check sqlca.sqlerrd[2] 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.

9.5.4 Parse Error Offsets

Before executing a SQL statement, Oracle must parse 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 sqlca.sqlerrd[4], which you can check explicitly. The offset specifies the character position in the SQL statement at which the parse error begins. As in a normal C string, the first character occupies position zero. For example, if the offset is 9, the parse error begins at the 10th character.

The parse error offset is used for situations where a separate prepare/parse is performed. This is typical for dynamic SQL statements.

Parse errors may arise from missing, misplaced, or misspelled keywords, invalid options, 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 sqlca.sqlerrd[4] 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[4] to zero. Oracle also sets sqlca.sqlerrd[4] to zero if a parse error begins at the first character (which occupies position zero). So, check sqlca.sqlerrd[4] only if sqlca.sqlcode is negative, which means that an error has occurred.

9.5.5 Error Message Text

The error code and message for Oracle errors are available in the SQLCA variable SQLERRMC. At most, the first 70 characters of text are stored. To get the full text of messages longer than 70 characters, you use the sqlglm() function.

9.6.1 Declaring the SQLCA

When MODE=ORACLE, declaring the SQLCA is required. To declare the SQLCA, you should copy it into your program with the INCLUDE or #include statement, as follows:

EXEC SQL INCLUDE SQLCA; 

or

#include <sqlca.h>

If you use a Declare Section, the SQLCA must be declared outside the Declare Section. Not declaring the SQLCA results in compile-time errors.

When you precompile your program, the INCLUDE SQLCA statement is replaced by several variable declarations that allow Oracle to communicate with the program.

When MODE=ANSI, declaring the SQLCA is optional. But in this case you must declare a SQLCODE or SQLSTATE status variable. The type of SQLCODE (upper case is required) is int. If you declare SQLCODE or SQLSTATE instead of the SQLCA in a particular compilation unit, the precompiler allocates an internal SQLCA for that unit. Your Pro*C/C++ 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.

9.6.2 SQLCA Contents

The SQLCA contains the following runtime information about the outcome of SQL statements:

• Oracle error codes

• Warning flags

• Event information

• Rows-processed count

• Diagnostics

The sqlca.h header file is:

/*
NAME
  SQLCA : SQL Communications Area.
FUNCTION
  Contains no code. Oracle fills in the SQLCA with status info
  during the execution of a SQL stmt.
NOTES
  **************************************************************
  ***                                                        ***
  *** This file is SOSD.  Porters must change the data types ***
  *** appropriately on their platform.  See notes/pcport.doc ***
  *** for more information.                                  ***
  ***                                                        ***
  **************************************************************

  If the symbol SQLCA_STORAGE_CLASS is defined, then the SQLCA
  will be defined to have this storage class. For example:
 
    #define SQLCA_STORAGE_CLASS extern
 
  will define the SQLCA as an extern.
 
  If the symbol SQLCA_INIT is defined, then the SQLCA will be
  statically initialized. Although this is not necessary in order
  to use the SQLCA, it is a good programing practice not to have
  unitialized variables. However, some C compilers/operating systems
  don't allow automatic variables to be initialized in this manner.
  Therefore, if you are INCLUDE'ing the SQLCA in a place where it
  would be an automatic AND your C compiler/operating system doesn't
  allow this style of initialization, then SQLCA_INIT should be left
  undefined -- all others can define SQLCA_INIT if they wish.

  If the symbol SQLCA_NONE is defined, then the SQLCA
  variable will not be defined at all.  The symbol SQLCA_NONE
  should not be defined in source modules that have embedded SQL.
  However, source modules that have no embedded SQL, but need to
  manipulate a sqlca struct passed in as a parameter, can set the
  SQLCA_NONE symbol to avoid creation of an extraneous sqlca
  variable. 
*/
#ifndef SQLCA
#define SQLCA 1
struct   sqlca
         {
         /* ub1 */ char    sqlcaid[8];
         /* b4  */ long    sqlabc;
         /* b4  */ long    sqlcode;
         struct
           {
           /* ub2 */ unsigned short sqlerrml;
           /* ub1 */ char           sqlerrmc[70];
           } sqlerrm;
         /* ub1 */ char    sqlerrp[8];
         /* b4  */ long    sqlerrd[6];
         /* ub1 */ char    sqlwarn[8];
         /* ub1 */ char    sqlext[8];
         };
#ifndef SQLCA_NONE 
#ifdef   SQLCA_STORAGE_CLASS
SQLCA_STORAGE_CLASS struct sqlca sqlca
#else
         struct sqlca sqlca
#endif
#ifdef  SQLCA_INIT
         = {
         {'S', 'Q', 'L', 'C', 'A', ' ', ' ', ' '},
         sizeof(struct sqlca),
         0,
         { 0, {0}},
         {'N', 'O', 'T', ' ', 'S', 'E', 'T', ' '},
         {0, 0, 0, 0, 0, 0},
         {0, 0, 0, 0, 0, 0, 0, 0},
         {0, 0, 0, 0, 0, 0, 0, 0}
         }
#endif
         ;
#endif
#endif

9.6.3 SQLCA Structure

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

9.6.4 PL/SQL Considerations

When the precompiler application executes an embedded PL/SQL block, not all components of the SQLCA are set. For example, if the block fetches several rows, the rows-processed count (sqlerrd[2]) is set to only 1. You should depend only on the sqlcode and sqlerrm components of the SQLCA after execution of a PL/SQL block.

9.8.1 WHENEVER Conditions

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

9.8.2 WHENEVER Actions

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

9.8.3 WHENEVER Examples

If you want your program to

• Go to close_cursor if a "no data found" condition occurs

• Continue with the next statement if a warning occurs

• Go to error_handler if an error occurs

you must code the following WHENEVER directives before the first executable SQL statement:

EXEC SQL WHENEVER NOT FOUND GOTO close_cursor; 
EXEC SQL WHENEVER SQLWARNING CONTINUE; 
EXEC SQL WHENEVER SQLERROR GOTO error_handler; 

In the following example, you use WHENEVER...DO directives to handle specific errors:

... 
EXEC SQL WHENEVER SQLERROR DO handle_insert_error("INSERT error"); 
EXEC SQL INSERT INTO emp (empno, ename, deptno) 
    VALUES (:emp_number, :emp_name, :dept_number); 
EXEC SQL WHENEVER SQLERROR DO handle_delete_error("DELETE error"); 
EXEC SQL DELETE FROM dept WHERE deptno = :dept_number; 
... 
handle_insert_error(char *stmt) 
{   switch(sqlca.sqlcode) 
    { 
    case -1: 
    /* duplicate key value */ 
        ... 
        break; 
    case -1401: 
    /* value too large */ 
        ... 
        break; 
    default: 
    /* do something here too */ 
        ... 
        break; 
    } 
} 
 
handle_delete_error(char *stmt) 
{ 
    printf("%s\n\n", stmt); 
    if (sqlca.sqlerrd[2] == 0) 
    { 
        /* no rows deleted */ 
        ... 
    } 
    else 
    {   ...
    } 
    ... 
} 

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

9.8.4 Use of DO BREAK and DO CONTINUE

This example illustrates how to display employee name, salary, and commission for only those employees who receive commissions:

#include <sqlca.h>
#include <stdio.h>

main()
{
    char *uid = "scott/tiger";
    struct { char ename[12]; float sal; float comm; } emp;

    /* Trap any connection error that might occur. */
    EXEC SQL WHENEVER SQLERROR GOTO whoops;
    EXEC SQL CONNECT :uid;

    EXEC SQL DECLARE c CURSOR FOR
        SELECT ename, sal, comm FROM EMP ORDER BY ENAME ASC;

    EXEC SQL OPEN c;

    /* Set up 'BREAK' condition to exit the loop. */
    EXEC SQL WHENEVER NOT FOUND DO BREAK;
   /* The DO CONTINUE makes the loop start at the next iteration when an error occurs.*/
    EXEC SQL WHENEVER SQLERROR DO CONTINUE;

    while (1)
      {
          EXEC SQL FETCH c INTO :emp;
   /* An ORA-1405 would cause the 'continue' to occur. So only employees with */
   /* non-NULL commissions will be displayed. */
          printf("%s  %7.2f  %9.2f\n", emp.ename, emp.sal, emp.comm);
       }

/* This 'CONTINUE' shuts off the 'DO CONTINUE' allowing the program to 
   proceed if any further errors do occur, specifically, with the CLOSE */
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    EXEC SQL CLOSE c;

    exit(EXIT_SUCCESS);

whoops:
    printf("%.*s\n", sqlca.sqlerrm.sqlerrml, sqlca.sqlerrm.sqlerrmc);
    exit(EXIT_FAILURE);
}

9.8.5 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 directive before the first executable SQL statement you want to test.

A WHENEVER directive stays in effect until superseded by another WHENEVER directive checking for the same condition.

In the following example, the first WHENEVER SQLERROR directive is superseded by a second, and so applies only to the CONNECT statement. The second WHENEVER SQLERROR directive applies to both the UPDATE and DROP statements, despite the flow of control from step1 to step3.

step1: 
    EXEC SQL WHENEVER SQLERROR STOP; 
    EXEC SQL CONNECT :username IDENTIFIED BY :password; 
    ... 
    goto step3; 
step2: 
    EXEC SQL WHENEVER SQLERROR CONTINUE; 
    EXEC SQL UPDATE emp SET sal = sal * 1.10; 
    ... 
step3: 
    EXEC SQL DROP INDEX emp_index; 
    ... 

9.8.6 Guidelines for WHENEVER

The following guidelines will help you avoid some common pitfalls.

EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
    EXEC SQL FETCH...
}
EXEC SQL CLOSE my_cursor; 
... 

EXEC SQL WHENEVER NOT FOUND DO break;
for(;;)
{
   EXEC SQL FETCH ...
   EXEC SQL WHENEVER NOT FOUND CONTINUE;
   EXEC SQL INSERT INTO ...
}
EXEC SQL CLOSE my_cursor;
...

EXEC SQL WHENEVER SQLERROR GOTO sql_error; 
... 
sql_error: 
    EXEC SQL WHENEVER SQLERROR CONTINUE; 
    EXEC SQL ROLLBACK WORK RELEASE; 
    ... 

/* improper use of WHENEVER */ 
... 
EXEC SQL WHENEVER NOT FOUND GOTO no_more; 
for (;;) 
{ 
    EXEC SQL FETCH emp_cursor INTO :emp_name, :salary; 
    ... 
} 
 
no_more: 
    EXEC SQL DELETE FROM emp WHERE empno = :emp_number; 
     ... 

/* proper use of WHENEVER */ 
... 
EXEC SQL WHENEVER NOT FOUND GOTO no_more; 
for (;;) 
{ 
    EXEC SQL FETCH emp_cursor INTO :emp_name, :salary; 
    ... 
} 
no_more: 
    EXEC SQL WHENEVER NOT FOUND GOTO no_match; 
    EXEC SQL DELETE FROM emp WHERE empno = :emp_number; 
    ... 
no_match: 
    ... 

func1() 
{ 
  
    EXEC SQL WHENEVER SQLERROR GOTO labelA; 
    EXEC SQL DELETE FROM emp WHERE deptno = :dept_number; 
    ... 
labelA: 
... 
} 
func2() 
{ 
  
    EXEC SQL INSERT INTO emp (job) VALUES (:job_title); 
    ... 
} 

... 
EXEC SQL UPDATE emp SET sal = sal * 1.10; 
if (sqlca.sqlcode < 0) 
{  /* handle error  */ 
 
EXEC SQL DROP INDEX emp_index;

9.9.1 Restrictions

SQLStmtGetText() does not return the text for statements that contain the following commands:

• CONNECT

• COMMIT

• ROLLBACK

• FETCH

There are no SQL function codes for these commands.

9.9.2 Example Program

The example program sqlvcp.pc, is available in the demo directory. It demonstrates how you can use the sqlgls() function.

9.10.1 Declaring the ORACA

To declare the ORACA, copy it into your program with the INCLUDE statement or the #include preprocessor directive, as follows:

EXEC SQL INCLUDE ORACA; 

or

#include <oraca.h> 

If your ORACA must be of the extern storage class, define ORACA_STORAGE_CLASS in your program as follows:

#define ORACA_STORAGE_CLASS extern

If the program uses a Declare Section, the ORACA must be defined outside it.

9.10.2 Enabling the ORACA

To enable the ORACA, you must specify the ORACA option, either on the command line with

ORACA=YES 

or inline with

EXEC ORACLE OPTION (ORACA=YES); 

Then, you must choose appropriate runtime options by setting flags in the ORACA.

9.10.3 ORACA Contents

The ORACA contains option settings, system statistics, and extended diagnostics such as

• SQL statement text (you can specify when to save the text)

• The name of the file in which an error occurred (useful when using subroutines)

• Location of the error in a file

• Cursor cache errors and statistics

A partial listing of oraca.h is

/*
NAME
  ORACA : Oracle Communications Area.

  If the symbol ORACA_NONE is defined, then there will be no ORACA
  *variable*, although there will still be a struct defined.  This
  macro should not normally be defined in application code.

  If the symbol ORACA_INIT is defined, then the ORACA will be
  statically initialized. Although this is not necessary in order
  to use the ORACA, it is a good pgming practice not to have
  unitialized variables. However, some C compilers/operating systems
  don't allow automatic variables to be init'd in this manner. Therefore,
  if you are INCLUDE'ing the ORACA in a place where it would be
  an automatic AND your C compiler/operating system doesn't allow this style
  of initialization, then ORACA_INIT should be left undefined --
  all others can define ORACA_INIT if they wish.
*/
 
#ifndef  ORACA
#define  ORACA      1
 
struct    oraca
{
    char oracaid[8];   /* Reserved               */
    long oracabc;      /* Reserved               */
 
/*    Flags which are setable by User. */
 
   long  oracchf;      /* <> 0 if "check cur cache consistncy"*/
   long  oradbgf;      /* <> 0 if "do DEBUG mode checking"    */
   long  orahchf;      /* <> 0 if "do Heap consistency check" */
   long  orastxtf;     /* SQL stmt text flag            */
#define  ORASTFNON 0   /* = don't save text of SQL stmt       */
#define  ORASTFERR 1   /* = only save on SQLERROR         */
#define  ORASTFWRN 2   /* = only save on SQLWARNING/SQLERROR  */
#define  ORASTFANY 3      /* = always save             */
    struct
      {
  unsigned short orastxtl;
  char  orastxtc[70];
      } orastxt;         /* text of last SQL stmt          */
    struct
      {
  unsigned short orasfnml;
  char      orasfnmc[70];
      } orasfnm;        /* name of file containing SQL stmt    */
  long   oraslnr;        /* line nr-within-file of SQL stmt     */
  long   orahoc;         /* highest max open OraCurs requested  */
  long   oramoc;         /* max open OraCursors required         */
  long   oracoc;         /* current OraCursors open         */
  long   oranor;         /* nr of OraCursor re-assignments      */
  long   oranpr;         /* nr of parses               */
  long   oranex;         /* nr of executes            */
    };

#ifndef ORACA_NONE

#ifdef ORACA_STORAGE_CLASS
ORACA_STORAGE_CLASS struct oraca oraca
#else
struct oraca oraca
#endif
#ifdef ORACA_INIT
    =
    {
    {'O','R','A','C','A',' ',' ',' '},
    sizeof(struct oraca),
    0,0,0,0,
    {0,{0}},
    {0,{0}},
    0,
    0,0,0,0,0,0
    }
#endif
    ;

#endif

#endif
/* end oraca.h */

9.10.4 Choosing Runtime Options

The ORACA includes several option flags. Setting these flags by assigning them nonzero values provides the ability to

• Save the text of SQL statements

• Enable DEBUG operations

• Check cursor cache consistency (the cursor cache is a continuously updated area of memory used for cursor management)

• Check heap consistency (the heap is an area of memory reserved for dynamic variables)

• Gather cursor statistics

The following descriptions will help you choose the options you need.

9.10.5 Structure of the ORACA

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

9.10.6 ORACA Example

The following program prompts for a department number, inserts the name and salary of each employee in that department into one of two tables, then displays diagnostic information from the ORACA. This program is available online in the demo directory, as oraca.pc.

/* oraca.pc
 * This sample program demonstrates how to
 * use the ORACA to determine various performance
 * parameters at runtime.
 */
#include <stdio.h> 
#include <string.h>
#include <sqlca.h>
#include <oraca.h> 

EXEC SQL BEGIN DECLARE SECTION;
char *userid = "SCOTT/TIGER"; 
char  emp_name[21];
int   dept_number; 
float salary; 
char SQLSTATE[6];
EXEC SQL END DECLARE SECTION;

void sql_error(); 

main() 
{ 
    char temp_buf[32];

    EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error");
    EXEC SQL CONNECT :userid; 
    
    EXEC ORACLE OPTION (ORACA=YES);

    oraca.oradbgf  = 1;             /* enable debug operations */ 
    oraca.oracchf  = 1;      /* gather cursor cache statistics */ 
    oraca.orastxtf = 3;       /* always save the SQL statement */ 

    printf("Enter department number: "); 
    gets(temp_buf);
    dept_number = atoi(temp_buf);

    
    EXEC SQL DECLARE emp_cursor CURSOR FOR 
      SELECT ename, sal + NVL(comm,0) AS sal_comm
        FROM emp 
        WHERE deptno = :dept_number
        ORDER BY sal_comm DESC;
    EXEC SQL OPEN emp_cursor; 
    EXEC SQL WHENEVER NOT FOUND DO sql_error("End of data");
    
    for (;;) 
    { 
        EXEC SQL FETCH emp_cursor INTO :emp_name, :salary; 
        printf("%.10s\n", emp_name);
        if (salary < 2500) 
            EXEC SQL INSERT INTO pay1 VALUES (:emp_name, :salary); 
        else 
            EXEC SQL INSERT INTO pay2 VALUES (:emp_name, :salary);    
    } 
} 

void 
sql_error(errmsg)
char *errmsg;
{ 
    char buf[6];

    strcpy(buf, SQLSTATE);
    EXEC SQL WHENEVER SQLERROR CONTINUE; 
    EXEC SQL COMMIT WORK RELEASE; 
    
    if (strncmp(errmsg, "Oracle error", 12) == 0)
        printf("\n%s, sqlstate is %s\n\n", errmsg, buf);
    else
        printf("\n%s\n\n", errmsg);

    printf("Last SQL statement: %.*s\n", 
    oraca.orastxt.orastxtl, oraca.orastxt.orastxtc); 
    printf("\nAt or near line number %d\n", oraca.oraslnr); 
    printf
("\nCursor Cache Statistics\n------------------------\n"); 
    printf
("Maximum value of MAXOPENCURSORS:    %d\n", oraca.orahoc); 
    printf
("Maximum open cursors required:      %d\n", oraca.oramoc); 
    printf
("Current number of open cursors:     %d\n", oraca.oracoc); 
    printf
("Number of cache reassignments:      %d\n", oraca.oranor); 
    printf
("Number of SQL statement parses:     %d\n", oraca.oranpr); 
    printf
("Number of SQL statement executions: %d\n", oraca.oranex); 
    exit(1); 
}