Problem with OCI_DYNAMIC_FETCH

I wonder if anyone else has tried this and solved it.

I'm doing some relatively simple SELECTs via OCIStmtExecute and OCIStmtFetch which work fine except if the columns required are LONG and therefore need to be done with OCI_DYNAMIC_FETCH.

If the OCIStmtExecute is done using OCI_DYNAMIC_FETCH instead of OCI_DEFAULT, all works fine for the set of rows retrieved. I've done this with and without OCIDefineArrayofStruct. For the first set of rows, the OCIStmtExecute/OCIStmtFetch calls return OCI_NEED_DATA as often as I expect, and I use OCIStmtGetPieceInfo and OCIStmtSetPieceInfo happily to provide/extend buffers for the LONG data until all the rows I asked for in OCIStmtExecute have been found and OCI_SUCCESS is returned.

However, when I come back to get the next set of rows and do an OCIStmtFetch, I always get ORA-01002 Fetch out of Sequence. I think this is because of the piece information I have previously set, but I can't clear it out of the way. If I try and OCIHandleFree the define handles I get ORA-03130 which says the NEXT buffer piece is required!

For example, if there are 100 rows in the table and I define for an array fetch of 20 rows, I get OCI_NEED_DATA returned 20 times, then OCI_SUCCESS, and the first 20 rows/LONG columns come back fine. When I go back for 20 more the whole thing falls in a heap!

Has anyone got this mechanism to work? Or is there a better way to retrieve unknown numbers of LONG columns. I'm using C and the OCI at 8.1.6 under Win2K.

Thanks,

Ken Received on Sat Mar 30 2002 - 06:55:03 CET