Re: OCI Error Handling (trapping UPDATE errors)

From: Ron Reidy <rereidy_at_indra.com>
Date: Mon, 29 Oct 2001 17:27:51 -0700
Message-ID: <3BDDF407.EBC63DB0_at_indra.com>

Massis Isagholian wrote:
>
> I have an OCI application that's trying to update a non-existent row. The
> problem is that the OCIStmtExecute() function does not return an error when
> this happens. I'm using "if (OCIStmtExecute(...))" for error detection. Is
> this not the right way to detect this type of errors?
>
> Thanks
> Massis
From the docs...

<long winded explanation>

Batch Error Mode for OCIStmtExecute()

The OCI provides the ability to perform array DML operations. For example, an application can process an array of INSERT, UPDATE, or DELETE statements with a single statement execution. If one of the operations fails due to an error from the server, such as a unique constraint
violation, the array operation aborts and the OCI returns an error. Any rows remaining in the array are ignored. The application must then re-execute the remainder of the array, and go through the whole process again if it encounters more errors, which makes additional roundtrips.

To facilitate processing of array DML operations, the OCI provides the batch error mode (also called the enhanced DML array feature). This mode, which is specified in the OCIStmtExecute() call, simplifies DML array processing in the event of one or more errors. In this mode, the OCI
attempts to INSERT, UPDATE, or DELETE all rows, and collects (batches) information about any errors which occurred. The application can then retrieve this error information and re-execute any DML operations which failed during the first call.

      Note: This function is only available to applications linked with the 8.1 OCI libraries running against a release 8.1 server. Applications

      must also be recoded to account for the new program logic described in this section.

In this way, all DML operations in the array are attempted in the first call, and any failed operations can be reissued in a second call.

This mode is used as follows:

    1.The user specifies OCI_BATCH_ERRORS as the mode parameter of the OCIStmtExecute() call.

    2.After performing an array DML operation with OCIStmtExecute(), the application can retrieve the number of errors encountered during the

      operation by calling OCIAttrGet() on the statement handle to retrieve the OCI_ATTR_NUM_DML_ERRORS attribute. For example:

      ub4   num_errs;
      OCIAttrGet(stmtp, OCI_HTYPE_STMT, &num_err, 0,

OCI_ATTR_NUM_DML_ERRORS, errhp);

    3.The list of errors hangs off an error handle.

      The application extracts each error, along with its row information, from the error handle which was passed to the OCIStmtExecute() call

      using OCIParamGet(). In order to retrieve the information, the application must allocate an additional new error handle for the

      OCIParamGet() call. This new error handle is populated with the batched error information. The application obtains the syntax of each

      error with OCIErrorGet(), and the row offset (into the DML array) at which the error occurred by calling OCIAttrGet() on the new error

      handle.

      For example, once the num_errs amount has been retrieved, the application can issue the following calls:

      ...
      OCIError errhndl;
      for (i=0; i<num_errs; i++) 
      {
         OCIParamGet(errhp, OCI_HTYPE_ERROR, errhp, &errhndl, i+1);
         OCIAttrGet(errhndl, OCI_HTYPE_ERROR, &row_offset, 0,
             OCI_ATTR_DML_ROW_OFFSET, errhp);
         OCIErrorGet(..., errhndl, ...);
      ...


      Following this, the application can correct the bind information
for the appropriate entry in the array using the diagnostic information
      retrieved from the batched error. Once the appropriate bind

buffers are corrected or updated, the application can reexecute the associated

      DML statements.

      Because the application cannot know at compile time which rows in the first execution will cause errors, the binds of the next execute

      should be done dynamically by passing in the appropriate buffers at runtime. The user can reuse the bind buffers used in the array binds

      done on the first DML operation.

Example of Batch Error Mode

The following code shows an example of how this execution mode might be used. In this example assume that we have an application which inserts five rows (with two columns, of types NUMBER and CHAR) into a table. Furthermore, let us assume only two rows (say, 1 and 3) are successfully inserted in the initial DML operation. The user then proceeds to correct the data (wrong data was being inserted the first time) and
to issue an update with the corrected data. The user uses statement handles stmtp1 and stmtp2 to issue the INSERT and UPDATE respectively.

...
OCIBind *bindp1[2], *bindp2[2];
ub4 num_errs, row_off[MAXROWS], number[MAXROWS] = {1,2,3,4,5}; char grade[MAXROWS] = {'A','B','C','D','E'}; ...
/* Array bind all the positions */
OCIBindByPos (stmtp1,&bindp1[0],errhp,1,(dvoid *)&number[0],

     sizeof(number[0]),SQLT_NUM,(dvoid *)0, (ub2 *)0,(ub2 *)0,
            0,(ub4 *)0,OCI_DEFAULT);
OCIBindByPos (stmtp1,&bindp1[1],errhp,2,(dvoid *)&grade[0], 
     sizeof(grade[0],SQLT_CHR,(dvoid *)0, (ub2 *)0,(ub2 *)0,0,
            (ub4 *)0,OCI_DEFAULT);

/* execute the array INSERT */
OCIStmtExecute (svchp,stmtp1,errhp,5,0,0,0,OCI_BATCH_ERRORS); /* get the number of errors */
OCIAttrGet (stmtp1, OCI_HTYPE_STMT, &num_errs, 0,

            OCI_ATTR_NUM_DML_ERRORS, errhp); if (num_errs) {

   /* The user can do one of two things: 1) Allocate as many  */
   /* error handles as number of errors and free all handles  */
   /* at a later time; or 2) Allocate one err handle and reuse */
   /* the same handle for all the errors */

   OCIError *errhndl[num_errs];
   for (i = 0; i < num_errs; i++) {

      OCIParamGet(errhp, OCI_HTYPE_ERROR, errhp, &errhndl[i], i+1);
      OCIAttrGet (errhndl[i], OCI_HTYPE_ERROR, &row_off[i], 0, 
                  OCI_ATTR_DML_ROW_OFFSET, errhp);
      /* get server diagnostics */
      OCIErrorGet (..., errhndl[i], ...); 
      }

   }
/* make corrections to bind data */

OCIBindByPos (stmtp2,&bindp2[0],errhp,1,(dvoid *)0,0,SQLT_NUM, 
     (dvoid *)0, (ub2 *)0,(ub2 *)0,0,(ub4 *)0,OCI_DATA_AT_EXEC);
OCIBindByPos (stmtp2,&bindp2[1],errhp,2,(dvoid *)0,0,SQLT_DAT, 
     (dvoid *)0, (ub2 *)0,(ub2 *)0,0,(ub4 *)0,OCI_DATA_AT_EXEC);

/* register the callback for each bind handle */ OCIBindDynamic (bindp2[0],errhp,row_off,my_callback,0,0); OCIBindDynamic (bindp2[1],errhp,row_off,my_callback,0,0); /* execute the UPDATE statement */
OCIStmtExecute (svchp,stmtp2,errhp,2,0,0,0,OCI_BATCH_ERRORS); ...

In this example, OCIBindDynamic() is used with a callback because the user does not know at compile time what rows will return with errors. With a callback, you can simply pass the erroneous row numbers, stored in row_off, through the callback context and send only those rows that need to be updated or corrected. The same bind buffers can be shared between the INSERT and the UPDATE executes.

</long winded explanation>

-- 
Ron Reidy
Oracle DBA
Reidy Consulting, L.L.C.

Received on Tue Oct 30 2001 - 01:27:51 CET