Oracle 8.1.6 ProC and error codes , C++

According to the ProC programmer's guide (chapter on 'Handling runtime errors'), there are three main ways to handle error conditions caused by embedded SQL statements:

1. using the SQLSTATE variable
2. using the SQLCODE variable
3. using the sqlca.sqlcode variable

Furthermore the chapter says that SQLCODE (option 2) is deprecated, which leaves me with option 1 and option 3. On the sqlca.sqlcode it also says

    "<0 Means that Oracle did not execute the statement because of      a database, system, ........ Such an error can be fatal..."

This statement is a little bit vague and leaves some questions (especially how
to differentiate between non-fatal and fatal error conditions). Maybe one of you could enlighten me a little bit on this issue.

My questions

1. Does anyone know of a header file that lists all possible error codes (unfortunately such a file is not provided by Oracle in the ProC standard distribution. At least I haven't found it :-)

[Quoted]     Reason: In a statement like the following, I want to use predefined named constants instead

    of hard coded integral constants, that is to say

        EXEC SQL CONNECT :username IDENTIFIED BY :password;

        if (SQLCACODE_USER_NAME_OR_PASSWORD_INVALID == sqlca.sqlcode)
        {
             /* handle non recoverable error */
        }

    instead of

        EXEC SQL CONNECT :username IDENTIFIED BY :password;

        if (-1017 == sqlca.sqlcode)
        {
             /* handle non recoverable error */
        }

    2. Is there a way to figure out whether or not an error condition is indead fatal ? I mean,

    usually my program needs to find out

• if it has been disconnected from the database. In which case I simply want to reconnect
• if an error has occured that is temporary (e.g. "database temporarily not available), and this condition can be fixed by simply executing the same statement again later on, e.g. 30 seconds later
• if an error is fatal in that sense that executing the statement can never be successful
• if an error is fatal but disconnecting from and reconnecting to the database can solve the problem

    Example:

    EXEC SQL INSERT INTO abc ( a, b, c) VALUES (:a, :b, :c)

    if (0 > sqlca.sqlcode)
    {

        /*pseudo code following*/

        ERR_COND_DISCON: The program has been disconnected from the database.

        Required action: Reconnect to the database (and maybe do an EXEC SQL ROLLBACK WORK RELEASE before that)

        ERR_COND_TEMPORARY: A condition like "No more resources. Try again later"

        Required action: Sleep 30 seconds and try again (e.g. do this 10 times)

        ERR_COND_FATAL1: e.g. the data provided in a, b, or c is no appropriate for the fields (for simplicity reasons

        let's assume :a, :b, and :c are varchars)
        Required action: write an entry to the log file about the failed

        ERR_COND_FATAL2: for whatever reason the database cannot accept the insert now (but maybe later on)

        Required action: disconnect from the database, reconnect to the data base, and re-execute the INSERT

    }

    Can anyone explain how to find these "error classes" ? (Yes, the SQLSTATE has classes,

    but as I see, the mapping from ORACLE messages to SQLSTATEs is many-to-one, that is

    to say, e.g. state "72000" (class 72 = SQL phase error) is returned for about 10,000 different

    oracle messages).

    3. Where can I find a list of error codes that can be returned by the statements

            EXEC SQL CONNECT :username IDENTIFIED BY :password;     or

            EXEC SQL INSERT INTO abc (a, b, c) VALUES (:a, :b, :c);     or

            EXEC SQL COMMIT WORK; Thanks

    Thomas Received on Sat Jul 21 2001 - 23:45:14 CEST