There is a structure in Pro*C called the SQL Communications Area. The structure variable is named sqlca. The most useful field in the structure is sqlcode, which is the status variable. You access this variable with the fully qualified name like this "sqlca.sqlcode". The structure is defined in file sqlca.h.The status variable (sqlcode) has three different types of values. Zero means the operation was a success. A positive value means the SQL statement executed but threw an exception. And a negative value means the statement did not execute. This variable is updated by Oracle after each SQL statement.
Normally you will get a sqlcode of 1403 if there is a No Data Found exception. However you can set the MODE to ANSI in your Pro*C precompiler. This will result in sqlcode being set to 100 (instead of 1403) on No Data Found.
The developer is able to independently define other status variables such as SQLSTATE and SQLCODE. This SQLCODE is different than the sqlca.sqlcode. In addition to the SQL Communication Area, there is an Oracle Communications Area. It has the variable name oraca.
You can trap Pro*C errors using the WHENEVER clause. There are different values used in association with the clause. Some of these are SQLERROR, SQL WARNING, and NOT FOUND. SQLERROR equates to the sqlcode being negative. SQL WARNING is when sqlcode is positive or sql.sqlwarn[0] equals ‘W’. And NOT FOUND is a sqlcode of either 1403 or 100, depending on the precompiler MODE.
It is possible to have more than one sqlca. However there is only one active one at a time. My recent research into the SQL Communications Area was when the sqlcode was non zero for a trivial SQL statement. It turned out that the value was 1403 which means No Data Found. It took a while for me to discover that one of the bind variables had an extra space at the end causing an update to fail.
