Cursor and OCI

From: frankv <frankNOfrSPAM_at_mancala.com.invalid>
Date: 2000/05/23
Message-ID: <24cb8274.be2041b7@usw-ex0105-036.remarq.com>#1/1

Hi-

Simple question, I hope...

I'm trying to access a cursor returned from a stored procedure in 8i using OCI. If it matters this is all on Solaris.

Here's what I'm doing in my OCI code: (See actual code below)

• Create/Prepare statement handle for the stored procedure.
• Create/Bind a second statement handle for the cursor as a parameter to the stored proc statement. This is bound as type SQLT_RSET.
• Execute stored proc statement which returns no error.
• At this point I assume I should have a valid statement handle which I can bind to and fetch from. Instead I'm getting a ORA-01036 error (illegal variable name/number) when I attempt to bind to my cursor statement handle.

Any thoughts/suggestions? I'm sure I'm just missing something obvious but I'm unable to find example code for cursor handling in OCI.

Thanks in advance-

Here's my code example:

//allocate statement handle for stored proc
OCIStmt * statement;
result = OCIHandleAlloc((dvoid *) env, (dvoid **) &statement,

                        OCI_HTYPE_STMT, 0, (dvoid **) 0);

//prepare the statement

text stmtText[] = "begin pckg.proc(:cursor); end;"; result = OCIStmtPrepare(statement, err, stmtText, (ub4) strlen(stmtText),

                        OCI_NTV_SYNTAX, OCI_DEFAULT);

//allocate second statement handle for the cursor
OCIStmt * cursor;
result = OCIHandleAlloc((dvoid *) env, (dvoid **) &cursor,

                        OCI_HTYPE_STMT, 0, (dvoid **) 0);

//bind cursor statement handle as param to stored proc
OCIBind * cursorBind;
result = OCIBindByName(statement, &cursorBind, err,

                       (text *) ":cursor", -1,
                       (dvoid *)&mCursor, 0 , SQLT_RSET,
                       (dvoid *) 0, (ub2 *) 0,
                       (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);

//execute

result = OCIStmtExecute(svc, statement, err,

                        (ub4) 1, (ub4) 0, (OCISnapshot *) NULL,
                        (OCISnapshot *) NULL, OCI_DEFAULT);

//bind to cursor statement handle

OCIBind * resultBind;
char result[40 + 1];
ub2 resultLen;
result = OCIBindByPos(cursor, &resultBind, err,

                      1, (ub1 *) result, 40,
                      OCI_TYPECODE_VARCHAR, (dvoid *) 0, (ub2 *)
&resultLen,
                      (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT);

//result is error here!!!!!!!!!!!

//Try to Fetch... but I never get this far
result = OCIStmtFetch(cursor, err, 1,

                      OCI_FETCH_NEXT, OCI_DEFAULT);

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!

Received on Tue May 23 2000 - 00:00:00 CDT