1047-1050

Previous Table of Contents Next

Page 1047

• Unless specified, any DML operation acquires a table-level lock.
• You can bypass a DML lock by explicitly requesting a row-level lock. See the following code example:

   /* LOCKS ALL ROWS */ EXEC SQL LOCK TABLE STUDENT     IN SHARE UPDATE MODE[NOWAIT]; /*   IN THE FOLLOWING SELECT STATE THE [NOWAIT] */ /* PARAMETER TELLS ORACLE NOT TO WAIT FOR THE TABLE */ /* IF IT HAS BEEN LOCKED BY ANOTHER USER */ EXEC SQL SELECT FNMAE     FROM STUDENT     WHERE BIRTHDATE < '01-JAN-60'     FOR UPDATE OF LNAME[NOWAIT]; 

Error Handling

Your program can encounter many types of errors. Error handling should be a major part of every application to gracefully handle anticipated errors. Because errors can occur from a variety of sources ”design fault, coding mistakes, hardware failure, and invalid user input ”it is advisable to handle them. This section looks at some general guidelines for error handling and three ways of handling errors: SQLCA variables, WHENEVER statements, and indicator variables .

General Guidelines

The following guidelines will help you avoid some common pitfalls:

• Code a WHENEVER statement before your first executable statement. This ensures that all ensuing errors are trapped because WHENEVER statements stay in effect to the end of the file.
• When using a cursor to fetch rows of data, your program should be able to handle an end-of-data condition.
• Avoid branching to error routines with GOTOs.

Oracle provides variables that help monitor when one of these guidelines is not followed or some other type of error occurs. These variables are included in the SQLCA file.

SQLCA Variables

The SQLCA is a data structure that is updated after every executable SQL statement. SQLCA variables can be implicitly or explicitly checked. Table 45.5 describes some of the components that can be checked.

Page 1048

Table 45.5. SQLCA processing static variables.

---

Name	Type	Description
SQLAID[8]	char	"SQLCA"
SQLABC	long	Length of SQLCA
SQLCODE	long	Oracle error code 0: Successful execution <0: Abnormal termination with error code >0: Successful execution with status code 1403: No data found
SQLERRM	struct	Error code and message text
SQLERRML	short	Code
SQLERRMC[70]	char	Message text
SQLERRP[8]	char	(Not currently used)
SQLERRD[6]	long	Third cell is the number of rows processed
SQLWARN[8]	char	Array of warning flags
SQLEXT[9]	char	(Reserved for future use)

TIP

Warning flags are set to null if not set, and W if set. If SQLCODE is a negative number, you should check SQLERRD(5).

If you need more Oracle-specific diagnostic information, you can include the ORACA file. This file contains additional system statistics, option settings, and extended diagnostic variables. ORACA is declared in the same way as the SQLCA file. The following example shows the syntax for declaring ORACA:

 EXEC SQL INCLUDE oraca; 

To enable the ORACA file, you must set the precompiler option to YES. The following example shows two ways to set this option:

 /* OFF LINE */ ORACA = YES /* IN LINE */ EXEC ORACLE OPTION (ORACA=YES) 

Page 1049

You need to set certain flags for the runtime options. By setting these flags to non-zero values, you are able to do the following:

• Save the text of SQL statements
• Enable debug operations
• Check cursor cache consistency
• Check heap consistency
• Gather cursor statistics

Some of the flags are shown here:

Flag Name	Description
ORACAID	Character string "ORACA"
ORACABC	Length of ORACA data structure in bytes
ORACCHF	Cursor cache consistency flag
ORADBGF	Master debug flag
ORAHCHF	Heap consistency flag
ORASTXTF	Save-SQL-statement flag
ORASTXT	Subrecord for storing SQL statements
ORASFNM	Subrecord for storing filename
ORASLNR	Line in file at or near current SQL statement
ORAHOC	Highest MAXOPENCURSORS requested
ORAMOC	Maximum open cursors required
ORACOC	Current number of cursors used
ORANOR	Number of cursor cache reassignments
ORANPR	Number of SQL statement parses
ORANEX	Number of SQL statement executions

The WHENEVER Statement

WHENEVER statements are declarative in nature; therefore, their scope is positional, not logical. These statements test all executable SQL statements that follow it until another WHENEVER statement is encountered . This type of error detection is preferable because it is easier, more portable, and ANSI-compliant. These statements are used to direct a program's execution whenever an error or warning condition occurs. Some of the conditions that this statement can detect are Oracle errors, Oracle warnings, and no data found. If a WHENEVER statement is omitted, the process continues. The following example shows the syntax of the WHENEVER statement:

 EXEC SQL WHENEVER <condition> <action> 

Page 1050

CAUTION

Careless use of WHENEVER statements might cause problems such as infinite looping.

All the possible error conditions are described in following list:

 SQLERROR       Sqlcode is negative SQLWARNING     Sqlwarn[0] is set to W NOT FOUND      Sqlcode is +1403 

You can take several different actions when one of the error conditions is encountered. The following list describes those actions:

 Continue Do function_call()  break Goto statement_label Stop 

TIP

When using a WHENEVER-DO statement, you cannot pass parameters to or from the function being called.

Indicator Variables

An indicator variable is associated with a host variable. Each time the host variable is used in an executable SQL statement, a return code is assigned to the indicator variable. This enables the user to monitor the host variables. The two primary uses for indicator variables are detecting null values or truncated data values with the SELECT INTO clause and setting columns to null values without explicitly hard-coding them for use with the UPDATE and INSERT statements. Indicator variables must be explicitly declared in the DECLARE section as a two-byte integer (short), and it is good practice to declare them after the host variable. The following example shows how to declare these variables:

 EXEC SQL BEGIN DECLARE SECTION;   int    emp_number;   float salary;   short sal_ind;   /* indicator variable */ EXEC SQL END DECLARE SECTION; 

Previous Table of Contents Next