Try it here
Subscribe
SQLCA

Using the SQL Communications Area (SQLCA)

using_the_sql_communications_area_(sqlca)

The SQLCA is a data structure. Its 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.

Your program can have more than one SQLCA. For example, it might have one global SQLCA and several local ones. Access to a local SQLCA is limited by its scope within the program. Oracle returns information only to the SQLCA that is in scope.

Key Components of Error Reporting Using the SQLCA

Error reporting depends on variables in the SQLCA. This section highlights the key components of error reporting.

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.

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.

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.

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.

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.

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.

SQLCA Structure

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

sqlcaid

This string component is initialized to "SQLCA" to identify the SQL Communications Area.

sqlcabc

This integer component holds the length, in bytes, of the SQLCA structure.

sqlcode

This integer component holds the status code of the most recently executed SQL statement. The status code, which indicates the outcome of the SQL operation, can be any of the following numbers:

thead>
Status Codes Description
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.
<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, in most cases, be rolled back.
sqlerrm

This embedded struct contains the following two components:

Components Description
sqlerrml This integer component holds the length of the message text stored in sqlerrmc.
sqlerrmc This string component holds the message text corresponding to the error code stored in sqlcode. The string is not null terminated. Use the sqlerrml component to determine the length.

This component can store up to 70 characters. To get the full text of messages longer than 70 characters, you must use the sqlglm() function.

Make sure sqlcode is negative before you reference sqlerrmc. If you reference sqlerrmc when sqlcode is zero, you get the message text associated with a prior SQL statement.

sqlerrp

This string component is reserved for future use.

sqlerrd

This array of binary integers has six elements. Descriptions of the components in sqlerrd follow:

Components Description
sqlerrd[0] This component is reserved for future use.
sqlerrd[1] This component is reserved for future use.
sqlerrd[2] This component holds the number of rows processed by the most recently executed SQL statement. However, if the SQL statement failed, the value of sqlca.sqlerrd[2] is undefined, with one exception. If the error occurred during an array operation, processing stops at the row that caused the error, so sqlca.sqlerrd[2] gives the number of rows processed successfully.
sqlerrd[3] This component is reserved for future use.
sqlerrd[4] This component holds an offset that specifies the character position at which a parse error begins in the most recently executed SQL statement. The first character occupies position zero.
sqlerrd[5] This component is reserved for future use.

The rows-processed count is zeroed after an OPEN statement and incremented after a FETCH statement. For the EXECUTE, INSERT, UPDATE, DELETE, and SELECT INTO statements, the count reflects the number of rows processed successfully. The count does not include rows processed by an UPDATE or DELETE CASCADE. For example, if 20 rows are deleted because they meet WHERE-clause criteria, and 5 more rows are deleted because they now (after the primary delete) violate column constraints, the count is 20 not 25.

sqlwarn

This array of single characters has eight elements. They are used as warning flags. Oracle sets a flag by assigning it a "W""(for warning) character value.

The flags warn of exceptional conditions. For example, a warning flag is set when Oracle assigns a truncated column value to an output host variable.

Descriptions of the components in sqlwarn follow:

Components Description
sqlwarn[0] This flag is set if another warning flag is set.
sqlwarn[1] 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.

Components Description
sqlwarn[2] This flag is set if a NULL column is not used in the result of a SQL group function, such as AVG() or SUM().
sqlwarn[3] 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[4] This flag is no longer in use.
sqlwarn[5] This flag is set when an EXEC SQL CREATE {PROCEDURE | FUNCTION | PACKAGE | PACKAGE BODY} statement fails because of a PL/SQL compilation error.
sqlwarn[6] This flag is no longer in use.
sqlwarn[7] This flag is no longer in use.
sqlext

This string component is reserved for future use.

Writer profile pic

Admin on Mar 28, 2020 at 03:03 am


If you like dEexams.com and would like to contribute, you can write your article here or mail your article to admin@deexams.com . See your article appearing on the dEexams.com main page and help others to learn.



Post Comment

Comments( 0)

WEB TECHNOLOGY

Articles

×

Forgot Password

Please enter your email address below and we will send you information to change your password.