OCI Scrollable Cursor - Oracle 9i

From: Bryan <Mad_Coder_at_hotmail.com>
Date: 8 Aug 2002 19:12:05 -0700
Message-ID: <b1c6770d.0208081812.430f8c29@posting.google.com>

I am trying to implement a scrollable cursor that exists in a stored procedure, and is accessed by the app via a refcursor through OCI.

Here is my situation:

o Oracle 9i (9.2.0.1.0) for both client and server. o OCI Interface in purely C/C++ environment o I am able to compile/link program with 9i client library, and successfully fetch data from a stored proc that contains a nonscrollable/forward only/traditional cursor without any problems. Only when attempting a scrollable operation does it fail.

I have a stored proc that looks like this:

---

procedure test(
testcursor out cv_types.cv_fit_data
) as
begin
open testcursor
for select data_id,
text
from data;
end test;

---

First I allocate an OCIStmt handle and bind it to the proc. Then using the stmt handle that points to the proc, I bind a secondary handle that points to the cursor (testcursor above) by calling OCIBindByPos with type=SQLT_REF (See OCI ref manual on binding ref cursor). Next, I execute the proc via OCIStmtExecute with the mode=OCI_STMT_SCROLLABLE_READONLY using the statement handle that points to the procedure. Then I use that secondary stmt handle to fetch the rows via OCIStmtFetch2(). I am able to fetch the rows if I stick to orientation=OCI_FETCH_NEXT, but when I attempt to use any scrollable features such as OCI_FETCH_ABSOLUTE, it bails with an ORA-24391 error. Upon further investigation, I found out that this error occurs when the stmt is not executed with mode=OCI_STMT_SCROLLABLE_READONLY. The clincher is that I did execute the proc in that mode...and ORacle does not let you execute the secondary handle. Has anyone been faced with a similar situation?

Thanks,
Bryan Received on Thu Aug 08 2002 - 21:12:05 CDT