EXECUTE IMMEDIATE and sqlca.sqlcode

From: Alex Vinokur <alexander.vinokur_at_telrad.co.il>
Date: Mon, 25 Oct 1999 08:44:10 GMT
Message-ID: <7v158q$nls$1@nnrp1.deja.com>

Hi,

I have got several questions concerning

• sqlca.sqlcode
• EXEC SQL <action> and
• EXEC SQL EXECUTE IMMEDIATE <action>

  ---

EXEC SQL <action> // for instance, SELECT sqlca.sqlcode detects both errors and exceptions

        (negative and positive return codes)
        of SELECT action.


==============================================

EXEC SQL EXECUTE IMMEDIATE <action, for instance, the same SELECT> 1. sqlca.sqlcode detects errors (negative return codes)

        of IMMEDIATE (IMMEDIATE, not SELECT - is it true?) action

2. Can sqlca.sqlcode detect errors of SELECT action

   which are not errors of IMMEDIATE action?

3. Can sqlca.sqlcode detect exceptions (positive return codes)

   of IMMEDIATE action?

---

4. Can sqlca.sqlcode detect exceptions of SELECT action

   which are not exceptions of IMMEDIATE action?    (That is what I want to get).

---

        Thanks in advance,
        Alex

#################################################

Pro*C/C++ Precompiler Programmer's Guide Release 8.0
A58233-01

#################################################

11
Handling Runtime Errors

[snip]

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 statement
• explicit checking of SQLCA components

You can use WHENEVER statements, 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).

[snip]

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.

[snip]

Status Codes

Every executable SQL statement returns a status code to the SQLCA variable
sqlcode, which you can check implicitly with the WHENEVER statement 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.

[snip]

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

:

 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=ANSI, +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, in most cases, be rolled

     back.

Negative return codes correspond to error codes listed in Oracle8 Error Messages.

[snip]

You code the WHENEVER statement using the following syntax:

EXEC SQL WHENEVER <condition> <action>;

Conditions

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

SQLWARNING sqlwarn[0] is set because Oracle returned a warning (one of the warning flags, sqlwarn[1] through sqlwarn[7], is also set) or SQLCODE has a positive
value other than +1403. For example, sqlwarn[0] is set when Oracle assigns a
truncated column value to an output host variable.

Declaring the SQLCA is optional when MODE=ANSI. To use WHENEVER SQLWARNING,
however, you must declare the SQLCA.

SQLERROR SQLCODE has a negative value because Oracle returned an error.

NOT FOUND SQLCODE has a value of +1403 (+100 when MODE=ANSI) 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=ANSI, +100 is returned to SQLCODE after an INSERT of no rows.

[snip]

#################################################

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 25 1999 - 03:44:10 CDT