OCIErrorGet and Multiple Error Handling for OCI_ERROR

Question

For OCIErrorGet() , it is documented that it can return several errors that I use to extract with the following method for OCI_SUCCESS_WITH_INFO , but not currently for OCI_ERROR :

 void check_error( sword status ) { switch( status ) { case OCI_SUCCESS: break; case OCI_SUCCESS_WITH_INFO: { ub4 recordno = 1; while( status != OCI_NO_DATA ) { sb4 errcode = 0; text errbuf[ 1024 ]; status = ::OCIErrorGet( m_err, recordno, (text*)NULL, &errcode, errbuf, sizeof( errbuf ), OCI_HTYPE_ERROR ); if( status == OCI_SUCCESS ) { std::cout << "oracle info: " << (const char*)errbuf << std::endl; } else { assert( status == OCI_NO_DATA ); } ++recordno; } } break; case OCI_ERROR: { sb4 errcode = 0; text errbuf[ 1024 ]; // note here: no check of returned value from OCIErrorCode(), no loop! ::OCIErrorGet( m_err, 1, (text*)NULL, &errcode, errbuf, sizeof( errbuf ), OCI_HTYPE_ERROR ); throw my_oracle_error( errcode, (const char*)errbuf ); } break; default: throw "something else"; } } 

(of course, the actual code is slightly different, but the important part is shown above).

In the case of OCI_ERROR (marked with a comment in the above code), my questions are:

• Do I need a similar loop or does Oracle guarantee / document that in this case only one error can be returned?
• Do I need to check the return value from OCIErrorGet() in this case?
• If multiple errors can be returned, which errcode should I use for the exception I selected?

Preferably, the answers should be related to Oracle documentation.

Answer 1

Oracle guarantees that only one error will be returned per call to OCIErrorGet() (note the singular):

Returns an error message in the provided buffer and an Oracle database error code.
...
Multiple diagnostic entries can be obtained by calling OCIErrorGet () several times until there are no more entries (OCI_NO_DATA is returned). OCIErrorGet () returns no more than one diagnostic record.

Whether you need a similar loop or not depending on what (PL /) SQL code you call. Simple SQL statements usually return only one error code; eg,

 SQL> select 1/0 from dual; select 1/0 from dual * ERROR at line 1: ORA-01476: divisor is equal to zero 

However, if PL / SQL is involved, more can be returned:

 SQL> begin 2 raise_application_error(-20000, 'error'); 3 end; 4 / begin * ERROR at line 1: ORA-20000: error ORA-06512: at line 2 

Here, the actual error that interests you will be ORA-20000. Oracle exception throwing runs from the inner block to the outer block, so assuming you are not dealing with compilation errors, the first cause of the error will be the first exception. If you catch and throw an exception, this changes. An example provided by Oracle in the docs:

 SQL> begin 2 dbms_output.put_line(1/0); -- handled 3 exception when zero_divide then 4 dbms_output.put_line(1/0 || ' is undefined'); -- not handled 5 end; 6 / begin * ERROR at line 1: ORA-01476: divisor is equal to zero ORA-06512: at line 4 ORA-01476: divisor is equal to zero 

DBMS_OUTPUT.PUT_LINE - procedure, so the same exception appears twice; note that this is still the first exception that interests you.

To answer your questions:

You do not need a similar loop; you should use only one if you want to get more than one error code.

If multiple errors are returned, you should probably throw the first error code because of the method in which Oracle throws exceptions; it is essentially a call to judgment that you need to make. The docs are unclear whether OCIErrorGet() returns the most recent or earliest exception; you may need to throw the last exception.

Answer 2

I solved the problem like this:

 std::string checkOciErr( OCIError* errhp, sword status ){ std::stringstream ss; text errbuf[10240]; sb4 errcode; bool ret_code = true;// switch (status) { case OCI_SUCCESS: ret_code = false; break; case OCI_SUCCESS_WITH_INFO: OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR); ss << " Error: OCI_SUCCESS_WITH_INFO; Info: " << errbuf << endl; ret_code = (errcode == 436 || errcode == 437 || errcode == 438 || errcode == 439); break; case OCI_NEED_DATA: ss << " Error: OCI_NEED_DATA"<< endl; break; case OCI_NO_DATA: ss << " Error: OCI_NO_DATA"<< endl; break; case OCI_ERROR:{ int rc_l = 0; int recordno = 1; ss << " Error: "; while ( rc_l !=OCI_NO_DATA){ text errbuf1[10240]; rc_l = OCIErrorGet ((dvoid *) errhp, (ub4) recordno, (text *) NULL, &errcode, errbuf1, (ub4) sizeof(errbuf1), (ub4) OCI_HTYPE_ERROR); ss << errbuf1 << endl; recordno++; } } break; case OCI_INVALID_HANDLE: ss << " Error: OCI_INVALID_HANDLE" << endl; break; case OCI_STILL_EXECUTING: ss << " Error: OCI_STILL_EXECUTE"<< endl; break; case OCI_CONTINUE: ss << " Error: OCI_CONTINUE" << endl; break; default: ss << " Error: UNKNOWN(" << status << ")" << endl; break; } return ss.str(); } 

OUTPUT:

 Error: ORA-20000: My Raise!!! ORA-06512: at "OWNER.RAISEERROR", line 7 ORA-06512: at "OWNER.MYPACKAGE", line 214 ORA-06512: at line 1