Forum Stats

  • 3,876,042 Users
  • 2,267,023 Discussions
  • 7,912,405 Comments

Discussions

How can I recover from an error in an OCIDefineDynamic() callback?

Scott Harrison
Scott Harrison Member Posts: 1
edited Feb 24, 2014 7:36AM in Oracle Call Interface (OCI)

I have an Oracle 11g server that I am accessing using OCI calls from a C program.  I have configured all information to be passed back and forth to the server using UTF16.  All the character data on the server is stored in UTF8.

I have a table that has three columns: INT, BLOB, CLOB

I am reading each row individually by looping through all the rows issuing SQL like:  select * from table where int = x;

Each time I am about to issue the SQL I call the following API in a general routine of mine that sends SQL to the database:

OCIHandleAlloc()  /* for an OCI_HTYPE_STMT */

OCIAttrSet()  /* just to set a prefetching value, which in the case of rows with LOBs should do nothing, but the routine is general */

OCIStmtPrepare()

OCIAttrGet()  /* just to check whether the statement is a SELECT so I can do some maintenance work (remember the routine is general) */

OCIStmtExecute()

When I actually fetch the data, the first time I set up a way to handle each column by doing OCIDefineByPos().  Note that for the LOB columns the mode is OCI_DYNAMIC_FETCH and for them I also call OCIDefineDynamic() providing a callback to actually take the bytes returned and process them.

I then go about doing the fetch with OCIStmtFetch2() passing in one row requested.  Basically I will call this multiple times until it returns OCI_NO_DATA, at which time I will commit the transaction with OCITransCommit().

This works for the vast majority of my processing.  However, there are a number of records in the database where the CLOB data appears to be corrupt.  What happens is in my callback things seem to progress for a few iterations and then the **alenpp no longer indicates the number of bytes written into my buffer as it does normally, but it is a negative value of the total number of bytes given back.  For example, with a buffer of 8000 I usually am told 8000 bytes are written each time the callback is called.  However, is rare cases (totally repeatable) I will get let's say 32000 bytes processed and then the next callback will give me -32000 as the number of bytes in the buffer.  The numbers are not large enough to overflow an integer, and the value is always the negative total, so it may not be evenly divisible by the 8000 buffer size (based on what has been returned).

So, firstly, what is happening such that the API is giving me this negative value?  I assume it symbolizes an error condition from the Oracle client libraries.

Next, when I run into this issue I want to recover, by cancelling what I am doing and rolling back the transaction.  I attempt to do so by issuing OCIStmtFetch2() asking for zero rows (which seems to succeed), and then OCITransRollback() which returns to me an error:

ORA-01013: user requested cancel of current operation

And then when I go through the loop again to process the next select SQL, all the OCI calls up to the OCIStmtExecute() seem to return success, but the OCIStmtExecute() returns an error:

ORA-24909: call in progress. Current operation cancelled

So, secondly, but more importantly, how do I go about properly stopping the fetch and rolling back the transaction when I have encountered the error condition in my OCIDefineDynamic() callback so I can get to a "neutral" state so I can continue processing other SQL statements?


Thank you very much for your help.

Tagged:

Answers

  • ddevienne
    ddevienne Member Posts: 199

    Have you tried "return OCI_ERROR;" from the callback you registered? Normally you "return OCI_CONTINUE;" from the callback, to proceed normally, but I expect OCI to fail the statement, if you instead return OCI_ERROR from the callback. No idea about your earlier questions, sorry. --DD

This discussion has been closed.