Document ID: 99711.875 Title: Error Handling during Array Processing Creation Date: 01 October 1995 Last Revision Date: 17 February 1995 Revision Number: 1 Product: Precompiler Product Version: Generic Platform: Generic Information Type: Solution Impact: Low Abstract: This bulletin discusses some of the errors you can receive when using host arrays in DML statements and also how to handle errors. Keywords: ARRAY;SQLERRD;ORA-00001;ORA-01400;ORA-01401; ORA-01407; ORA-01438; --------------------------------------------------------------------- Error Handling during Array Processing If you are using arrays to do inserts, updates, or delete in a Pro*language program, this article will explain the extent of error handling you can expect to be able to do. ERROR HANDLING -------------- Some of the possible errors you can get during an array insert or array update operation are: ORA-00001: duplicate key in index ORA-01400: mandatory (not null) column is missing or Null during insert ORA-01401: inserted value too large for column ORA-01407: cannot update mandatory (not null) column to null ORA-01438: value larger than specified precision allows for this column Errors such as these will cause Oracle to stop execution and return a negative error code to SQLCODE in the SQL communication area (SQLCA). Oracle will also return the number of rows processed to SQLERRD(3) in SQLCA (In C, it is SQLERRD[2]). These two elements in the SQLCA can be used in determining the type of error and the array element which generates the error. For example, in C, if the first error encountered in array X is at X[50], then SQLERRD[2] will have the value 50. (Remember, unlike FORTRAN and COBOL, array subscription in C starts at 0 not 1.) The element in error can then be identified by X[SQLERRD[2]]. You can also use SQLERRD to calculate the offset in the array at which to continue the operation. In the above example, processing can be resumed at X[SQLERRD[2]+1]. Of course, you will need to manage the array with the offset programmatically. The use of SQLERRD in handling error conditions works nicely for array insert. However, in certain cases, this method cannot be used for array updates. If an array element updates more than one row in a table, and a subsequent array element is in error, the SQLERRD value becomes unreliable for identifying the element in error and for offsetting the array to continue operation. Let's look at the following scenario: EXEC SQL UPDATE t1 SET col1 = :X WHERE col2 = :Y Column col2 is not a unique key. X and Y are arrays. Let's say one row is updated with X[0] where col2 matched Y[0]. Then four rows are updated with X[1] where col2 matched Y[1]. An error occurs on X[2] when the value is too large for column col1. To this point SQLERRD[2] will have the value 5. Unlike the insert, you cannot identify the error by X[SQLERRD[2]]. This would give you X[5], not X[2] at which the error occurred. In turn, this would also give you the wrong offset to resume processing. In addition, using host arrays in the WHERE clause of an UPDATE or DELETE where some elements do not satisfy the search condition does not result in a fatal error. Oracle will continue processing. SQLCODE is set to 0, not +1403 even though no record is found. In this case, you will not be able to identify the unmatched elements. If this is necessary, the workaround would be to use scalar variable update/delete. Rollback is NOT automatic on DML statement errors, therefore prior transactions can still be committed if desired. Here, you can take advantage of array processing. Array elements which have been processed successfully can be committed. With SQLERRD, you can skip over the bad element and continue processing the rest of the array. (Again, this works very well for array insert and possibly for array update if each array element affects exactly one row.) SAMPLE PROGRAM -------------- This program illustrates array insert error handling under V6. Elements in error will be displayed to the terminal. Those inserted successfully will be committed. Processing will continue on after handling the error until the entire array is processed. #include EXEC SQL BEGIN DECLARE SECTION; char *account = "scott/tiger"; int col1[5]; short i_col1[5]; int to_insert; EXEC SQL END DECLARE SECTION; EXEC SQL INCLUDE SQLCA; main() { int rec = 0; int i, tot_rec, pos; EXEC SQL WHENEVER SQLERROR GOTO sql_err; EXEC SQL CONNECT :account; /* logon to Oracle */ printf("connect to ORACLE ....... successfully \n"); exec sql execute immediate "create table tt (t1 number(2) not null)"; /* populate arrays */ col1[0] = 1; i_col1[0] = 0; col1[1] = 2; i_col1[1] = -1; col1[2] = 3; i_col1[2] = 0; col1[3] = 4; i_col1[3] = 0; col1[4] = 555; i_col1[4] = 0; to_insert = 5; /* initialize to size of arrays */ tot_rec = 5; /* total # of records to be processed */ rec = 0; /* number of records processed */ exec sql whenever sqlerror continue; /* this will loop until every element in the array is processed */ while(rec < tot_rec) { /* the 'for :to_insert' is used to control how many elements to insert. The first time will be set to the entire array. The to_insert counter will change after an error is encountered. Think of the array as a stack. All elements up to and including the error will be POPed off, thus, fewer elements will remain to be processed the next time through the while loop.*/ exec sql for :to_insert insert into tt (t1) values (:col1:i_col1); if (sqlca.sqlcode < 0) { printf("bad record: col1 = %d, sqlcode = %d\n", col1[sqlca.sqlerrd[2]], sqlca.sqlcode); /* the following if statement checks to see if the error is on the last element to be processed. If so, don't bother POPing the array. */ if (sqlca.sqlerrd[2]+1 != to_insert) { pos = 0; /* The for loop is used to POP the already processed elements off the array. */ for (i=sqlca.sqlerrd[2]+1; i