Programmer's Guide to the Pro*C/C++ Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
You cannot anticipate all possible errors, but you can plan to handle certain kinds of errors meaningful to your program. For the Pro*C Precompiler, error handling means detecting and recovering from SQL statement execution errors.
You can also prepare to handle warnings such as "value truncated" and status changes such as "end of data."
It is especially important to check for error and warning conditions after every SQL data manipulation statement, because an INSERT, UPDATE, or DELETE statement might fail before processing all eligible rows in a table.
Note: In this guide, the sqlca structure is commonly referred to using the acronym for SQL Communications Area (SQLCA). When this guide refers to a specific component in the C struct, the structure name (sqlca) is used.
The SQLCA is defined in the header file sqlca.h, which you include in your program using either of the following statements:
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). Other components contain warning flags and miscellaneous information about the processing of the SQL statement. For complete information about the SQLCA structure, see the "Using the SQL Communications Area" section .
Note: SQLCODE (upper case) always refers to a separate status variable, not a component of the SQLCA. SQLCODE is declared as a long integer. When referring to the component of the SQLCA named sqlcode, the fully-qualified name sqlca.sqlcode is always used.
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. See the "Using the Oracle Communications Area" section for complete information about the ORACA.
After executing a SQL statement, the Oracle Server returns a status code to the SQLSTATE variable currently in scope. The status code indicates whether the SQL statement executed successfully or raised an exception (error or warning condition). To promote interoperability (the ability of systems to exchange information easily), SQL92 predefines all the common SQL exceptions.
Unlike SQLCODE, which stores only error codes, SQLSTATE stores error and warning codes. Furthermore, the SQLSTATE reporting mechanism uses a standardized coding scheme. Thus, SQLSTATE is the preferred status variable. Under SQL92, SQLCODE is a "deprecated feature" retained only for compatibility with SQL89 and likely to be removed from future versions of the standard.
Unlike SQLCODE, which stores signed integers and can be declared outside the Declare Section, SQLSTATE stores 5-character null-terminated strings and must be declared inside the Declare Section. You declare SQLSTATE as
char SQLSTATE[6]; /* Upper case is required. */
Note: SQLSTATE must be declared with a dimension of exactly 6 characters.
Each of the five characters in a SQLSTATE value is a digit (0..9) or an uppercase Latin letter (A..Z). Class codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined conditions (those defined in SQL92). All other class codes are reserved for implementation-defined conditions. Within predefined classes, subclass codes that begin with a digit in the range 0..4 or a letter in the range A..H are reserved for predefined subconditions. All other subclass codes are reserved for implementation-defined subconditions. Figure 9 - 1 shows the coding scheme.
Figure 9 - 1. SQLSTATE Coding Scheme
Table 9 - 1 shows the classes predefined by SQL92.
Table 9 - 2 shows how SQLSTATE status codes and conditions are mapped to Oracle errors. Status codes in the range 60000 .. 99999 are implementation-defined.
If you declare SQLSTATE
/* declare host variables */
EXEC SQL BEGIN DECLARE SECTION;
int emp_number, dept_number;
char emp_name[20];
EXEC SQL END DECLARE SECTION;
/* declare status variable--must be upper case */
long SQLCODE;
When MODE=ORACLE, if you declare SQLCODE, it is not used.
You can declare more than one SQLCODE. Access to a local SQLCODE is limited by its scope within your program.
After every SQL operation, Oracle returns a status code to the SQLCODE currently in scope. So, your program can learn the outcome of the most recent SQL operation by checking SQLCODE explicitly, or implicitly with the WHENEVER statement.
When you declare SQLCODE instead of the SQLCA in a particular compilation unit, the precompiler allocates an internal SQLCA for that unit. Your host 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.
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.
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.
By default, static SQL statements are checked for syntactic errors at precompile time. So, sqlca.sqlerrd[4] is most useful for debugging dynamic SQL statements, which your program accepts or builds at run time.
Parse errors arise from missing, misplaced, or misspelled keywords, invalid options, nonexistent tables, 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.
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.
Note: When your application uses SQL*Net to access a combination of local and remote databases concurrently, all the databases write to one SQLCA. There is not a different SQLCA for each database. For more information, see the section ``Concurrent Connections" .
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 long. 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 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.
Note: Declaring the SQLCA is optional when MODE=ANSI, but you cannot use the WHENEVER SQLWARNING statement without the SQLCA. So, if you want to use the WHENEVER SQLWARNING statement, you must declare the SQLCA.
Note: This Guide uses SQLCODE when referring to the SQLCODE status variable, and sqlca.sqlcode when explicitly referring to the component of the SQLCA structure.
/* NAME SQLCA : SQL Communications Area. FUNCTION Contains no code. Oracle fills in the SQLCA with status info during the execution of a SQL stmt. NOTES ************************************************************** *** *** *** This file is SOSD. Porters must change the data types *** *** appropriately on their platform. See notes/pcport.doc *** *** for more information. *** *** *** ************************************************************** If the symbol SQLCA_STORAGE_CLASS is defined, then the SQLCA will be defined to have this storage class. For example: #define SQLCA_STORAGE_CLASS extern will define the SQLCA as an extern. If the symbol SQLCA_INIT is defined, then the SQLCA will be statically initialized. Although this is not necessary in order to use the SQLCA, it is a good programing practice not to have unitialized variables. However, some C compilers/OS's don't allow automatic variables to be initialized in this manner. Therefore, if you are INCLUDE'ing the SQLCA in a place where it would be an automatic AND your C compiler/OS doesn't allow this style of initialization, then SQLCA_INIT should be left undefined -- all others can define SQLCA_INIT if they wish. If the symbol SQLCA_NONE is defined, then the SQLCA variable will not be defined at all. The symbol SQLCA_NONE should not be defined in source modules that have embedded SQL. However, source modules that have no embedded SQL, but need to manipulate a sqlca struct passed in as a parameter, can set the SQLCA_NONE symbol to avoid creation of an extraneous sqlca variable. */ #ifndef SQLCA #define SQLCA 1 struct sqlca { /* ub1 */ char sqlcaid[8]; /* b4 */ long sqlabc; /* b4 */ long sqlcode; struct { /* ub2 */ unsigned short sqlerrml; /* ub1 */ char sqlerrmc[70]; } sqlerrm; /* ub1 */ char sqlerrp[8]; /* b4 */ long sqlerrd[6]; /* ub1 */ char sqlwarn[8]; /* ub1 */ char sqlext[8]; }; #ifndef SQLCA_NONE #ifdef SQLCA_STORAGE_CLASS SQLCA_STORAGE_CLASS struct sqlca sqlca #else struct sqlca sqlca #endif #ifdef SQLCA_INIT = { {'S', 'Q', 'L', 'C', 'A', ' ', ' ', ' '}, sizeof(struct sqlca), 0, { 0, {0}}, {'N', 'O', 'T', ' ', 'S', 'E', 'T', ' '}, {0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0, 0, 0}, {0, 0, 0, 0, 0, 0, 0, 0} } #endif ; #endif #endif
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 the Oracle7 Server Messages manual.
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 (discussed later).
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.
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.
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.
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 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:
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.
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 set if every row in a table was processed by an UPDATE or DELETE statement without a WHERE clause. An update or deletion is called unconditional if no search condition restricts the number of rows processed. Such updates and deletions are unusual, so Oracle sets this warning flag. That way, you can roll back the transaction if necessary.
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.
void sqlglm(char *message_buffer, size_t *buffer_size, size_t *message_length);
where:
message_buffer
Is the text buffer in which you want Oracle to store the error message (Oracle blank-pads to the end of this buffer).
buffer_size
Is a scalar variable that specifies the maximum size of the buffer in bytes.
message_length
Is a scalar variable in which Oracle stores the actual length of the error message.
Note: The types of the last two arguments for the sqlglm() function are shown here generically as size_t pointers. However on your platform they might have a different type. For example, on many UNIX workstation ports, they are unsigned int *.
You should check the file sqlcpr.h, which is in the standard include directory on your system, to determine the datatype of these parameters.
The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names. The maximum length of an error message returned by sqlglm depends on the value you specify for buffer_size.
The following example calls sqlglm to get an error message of up to 200 characters in length:
EXEC SQL WHENEVER SQLERROR DO sql_error();
...
/* other statements */
...
sql_error()
{
char msg[200];
size_t buf_len, msg_len;
buf_len = sizeof (msg);
sqlglm(msg, &buf_len, &msg_len); /* note use of pointers */
printf("%.*s\n\n", msg_len, msg);
exit(1);
}
Notice that sqlglm is called only when a SQL error has occurred. Always make sure SQLCODE (or sqlca.sqlcode) is non-zero before calling sqlglm. If you call sqlglm when SQLCODE is zero, you get the message text associated with a prior SQL statement.
With the WHENEVER statement you can specify actions to be taken when Oracle detects an error, warning condition, or "not found" condition. These actions include continuing with the next statement, calling a routine, branching to a labeled statement, or stopping.
You code the WHENEVER statement using the following syntax:
EXEC SQL WHENEVER <condition> <action>;
Declaring the SQLCA is optional when MODE=ANSI. To use WHENEVER SQLWARNING, however, you must declare the SQLCA.
When MODE=ANSI, +100 is returned to SQLCODE after an INSERT of no rows.
The usual rules for entering and exiting a function apply. You can pass parameters to the error handler invoked by an EXEC SQL WHENEVER ... DO ... statement, and the function can return a value.
STOP in effect just generates an exit() call whenever the condition occurs. Be careful. The STOP action displays no messages before disconnecting from Oracle.
EXEC SQL WHENEVER NOT FOUND GOTO close_cursor;
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL WHENEVER SQLERROR GOTO error_handler;
In the following example, you use WHENEVER...DO statements to handle specific errors:
...
EXEC SQL WHENEVER SQLERROR DO handle_insert_error("INSERT error");
EXEC SQL INSERT INTO emp (empno, ename, deptno)
VALUES (:emp_number, :emp_name, :dept_number);
EXEC SQL WHENEVER SQLERROR DO handle_delete_error("DELETE error");
EXEC SQL DELETE FROM dept WHERE deptno = :dept_number;
...
handle_insert_error(char *stmt)
{ switch(sqlca.sqlcode)
{
case -1:
/* duplicate key value */
...
break;
case -1401:
/* value too large */
...
break;
default:
/* do something here too */
...
break;
}
}
handle_delete_error(char *stmt)
{
printf("%s\n\n", stmt);
if (sqlca.sqlerrd[2] == 0)
{
/* no rows deleted */
...
}
else
{ ...
}
...
}
Notice how the procedures check variables in the SQLCA to determine a course of action.
A WHENEVER statement stays in effect until superseded by another WHENEVER statement checking for the same condition.
In the example below, the first WHENEVER SQLERROR statement is superseded by a second, and so applies only to the CONNECT statement. The second WHENEVER SQLERROR statement applies to both the UPDATE and DROP statements, despite the flow of control from step1 to step3.
step1:
EXEC SQL WHENEVER SQLERROR STOP;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
...
goto step3;
step2:
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL UPDATE emp SET sal = sal * 1.10;
...
step3:
EXEC SQL DROP INDEX emp_index;
...
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;) { EXEC SQL FETCH... }
EXEC SQL CLOSE my_cursor;
...
EXEC SQL WHENEVER SQLERROR GOTO sql_error;
...
sql_error:
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
...
Without the WHENEVER SQLERROR CONTINUE statement, a ROLLBACK error would invoke the routine again, starting an infinite loop.
Careless use of WHENEVER can cause problems. For example, the following code enters an infinite loop if the DELETE statement sets NOT FOUND because no rows meet the search condition:
/* improper use of WHENEVER */
...
EXEC SQL WHENEVER NOT FOUND GOTO no_more;
for (;;)
{
EXEC SQL FETCH emp_cursor INTO :emp_name, :salary;
...
}
no_more:
EXEC SQL DELETE FROM emp WHERE empno = :emp_number;
...
The next example handles the NOT FOUND condition properly by resetting the GOTO target:
/* proper use of WHENEVER */
...
EXEC SQL WHENEVER NOT FOUND GOTO no_more;
for (;;)
{
EXEC SQL FETCH emp_cursor INTO :emp_name, :salary;
...
}
no_more:
EXEC SQL WHENEVER NOT FOUND GOTO no_match;
EXEC SQL DELETE FROM emp WHERE empno = :emp_number;
...
no_match:
...
func1()
{
EXEC SQL WHENEVER SQLERROR GOTO labelA;
EXEC SQL DELETE FROM emp WHERE deptno = :dept_number;
...
labelA:
...
}
func2()
{
EXEC SQL INSERT INTO emp (job) VALUES (:job_title);
...
}
The label to which a WHENEVER GOTO statement branches must be in the same precompilation file as the statement.
...
EXEC SQL UPDATE emp SET sal = sal * 1.10;
if (sqlca.sqlcode < 0)
{ /* handle error */
EXEC SQL DROP INDEX emp_index;
Just make sure no WHENEVER GOTO or WHENEVER STOP statement is active.
The sqlgls() function--part of the SQLLIB runtime library--returns the following information:
The syntax for sqlgls() is
int sqlgls(char *sqlstm, size_t *stmlen, size_t *sqlfc);
The sqlstm parameter is a character buffer that holds the returned text of the SQL statement. Your program must statically declare the buffer or dynamically allocate memory for the buffer.
The stmlen parameter is a long integer. Before calling sqlgls(), set this parameter to the actual size, in bytes, of the sqlstm buffer. When sqlgls() returns, the sqlstm buffer contains the SQL statement text, blank padded to the length of the buffer. The stmlen parameter returns the actual number of bytes in the returned statement text, not counting blank padding.
The sqlfc parameter is a long integer that returns the SQL function code for the SQL command in the statement. Table 9 - 3 shows the SQL function codes for the commands.
Besides helping you to diagnose problems, the ORACA lets you monitor your program's use of Oracle resources such as the SQL Statement Executor and the cursor cache.
Your program can have more than one ORACA. For example, it might have one global ORACA and several local ones. Access to a local ORACA is limited by its scope within the program. Oracle returns information only to the ORACA that is in scope.
EXEC SQL INCLUDE ORACA;
or
#include <oraca.h>
If your ORACA must be of the extern storage class, define ORACA_STORAGE_CLASS in your program as follows:
#define ORACA_STORAGE_CLASS extern
If the program uses a Declare Section, the ORACA must be defined outside it.
ORACA=YES
or inline with
EXEC ORACLE OPTION (ORACA=YES);
Then, you must choose appropriate runtime options by setting flags in the ORACA.
/* NAME ORACA : Oracle Communications Area. If the symbol ORACA_NONE is defined, then there will be no ORACA *variable*, although there will still be a struct defined. This macro should not normally be defined in application code. If the symbol ORACA_INIT is defined, then the ORACA will be statically initialized. Although this is not necessary in order to use the ORACA, it is a good pgming practice not to have unitialized variables. However, some C compilers/OS's don't allow automatic variables to be init'd in this manner. Therefore, if you are INCLUDE'ing the ORACA in a place where it would be an automatic AND your C compiler/OS doesn't allow this style of initialization, then ORACA_INIT should be left undefined -- all others can define ORACA_INIT if they wish. */ #ifndef ORACA #define ORACA 1 struct oraca { char oracaid[8]; /* Reserved */ long oracabc; /* Reserved */ /* Flags which are setable by User. */ long oracchf; /* <> 0 if "check cur cache consistncy"*/ long oradbgf; /* <> 0 if "do DEBUG mode checking" */ long orahchf; /* <> 0 if "do Heap consistency check" */ long orastxtf; /* SQL stmt text flag */ #define ORASTFNON 0 /* = don't save text of SQL stmt */ #define ORASTFERR 1 /* = only save on SQLERROR */ #define ORASTFWRN 2 /* = only save on SQLWARNING/SQLERROR */ #define ORASTFANY 3 /* = always save */ struct { unsigned short orastxtl; char orastxtc[70]; } orastxt; /* text of last SQL stmt */ struct { unsigned short orasfnml; char orasfnmc[70]; } orasfnm; /* name of file containing SQL stmt */ long oraslnr; /* line nr-within-file of SQL stmt */ long orahoc; /* highest max open OraCurs requested */ long oramoc; /* max open OraCursors required */ long oracoc; /* current OraCursors open */ long oranor; /* nr of OraCursor re-assignments */ long oranpr; /* nr of parses */ long oranex; /* nr of executes */ }; #ifndef ORACA_NONE #ifdef ORACA_STORAGE_CLASS ORACA_STORAGE_CLASS struct oraca oraca #else struct oraca oraca #endif #ifdef ORACA_INIT = { {'O','R','A','C','A',' ',' ',' '}, sizeof(struct oraca), 0,0,0,0, {0,{0}}, {0,{0}}, 0, 0,0,0,0,0,0 } #endif ; #endif #endif /* end oraca.h */
The Oracle runtime library does the consistency checking and might issue error messages, which are listed in the Oracle7 Server Messages. manual. They are returned to the SQLCA just like Oracle error messages.
This flag has the following settings:
Disable cache consistency checking (the default).
Enable cache consistency checking.
Disable all DEBUG operations (the default).
Enable all DEBUG operations.
This flag must be set before the CONNECT command is issued and, once set, cannot be cleared; subsequent change requests are ignored. It has the following settings:
Disable heap consistency checking (the default).
Enable heap consistency checking.
orastxtl
This integer component holds the length of the current SQL statement.
orastxtc
This string component holds the text of the current SQL statement. At most, the first 70 characters of text are saved. The string is not null terminated. Use the oratxtl length component when printing the string.
Statements parsed by the precompiler, such as CONNECT, FETCH, and COMMIT, are not saved in the ORACA.
orasfnml
This integer component holds the length of the filename stored in orasfnmc.
orasfnmc
This string component holds the filename. At most, the first 70 characters are stored.
Internally, there is a set of these variables for each CONNECTed database. The current values in the ORACA pertain to the database against which the last COMMIT or ROLLBACK was executed.
/* oraca.pc * This sample program demonstrates how to * use the ORACA to determine various performance * parameters at runtime. */ #include <stdio.h> #include <string.h> #include <sqlca.h> #include <oraca.h> EXEC SQL BEGIN DECLARE SECTION; char *userid = "SCOTT/TIGER"; char emp_name[21]; int dept_number; float salary; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; void sql_error(); main() { char temp_buf[32]; EXEC SQL WHENEVER SQLERROR DO sql_error("Oracle error"); EXEC SQL CONNECT :userid; EXEC ORACLE OPTION (ORACA=YES); oraca.oradbgf = 1; /* enable debug operations */ oraca.oracchf = 1; /* gather cursor cache statistics */ oraca.orastxtf = 3; /* always save the SQL statement */ printf("Enter department number: "); gets(temp_buf); dept_number = atoi(temp_buf); EXEC SQL DECLARE emp_cursor CURSOR FOR SELECT ename, sal + NVL(comm,0) AS sal_comm FROM emp WHERE deptno = :dept_number ORDER BY sal_comm DESC; EXEC SQL OPEN emp_cursor; EXEC SQL WHENEVER NOT FOUND DO sql_error("End of data"); for (;;) { EXEC SQL FETCH emp_cursor INTO :emp_name, :salary; printf("%.10s\n", emp_name); if (salary < 2500) EXEC SQL INSERT INTO pay1 VALUES (:emp_name, :salary); else EXEC SQL INSERT INTO pay2 VALUES (:emp_name, :salary); } } void sql_error(errmsg) char *errmsg; { char buf[6]; strcpy(buf, SQLSTATE); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL COMMIT WORK RELEASE; if (strncmp(errmsg, "Oracle error", 12) == 0) printf("\n%s, sqlstate is %s\n\n", errmsg, buf); else printf("\n%s\n\n", errmsg); printf("Last SQL statement: %.*s\n", oraca.orastxt.orastxtl, oraca.orastxt.orastxtc); printf("\nAt or near line number %d\n", oraca.oraslnr); printf ("\nCursor Cache Statistics\n------------------------\n"); printf ("Maximum value of MAXOPENCURSORS: %d\n", oraca.orahoc); printf ("Maximum open cursors required: %d\n", oraca.oramoc); printf ("Current number of open cursors: %d\n", oraca.oracoc); printf ("Number of cache reassignments: %d\n", oraca.oranor); printf ("Number of SQL statement parses: %d\n", oraca.oranpr); printf ("Number of SQL statement executions: %d\n", oraca.oranex); exit(1); }
![]() ![]() Prev Next |
![]() Copyright © 1997 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |