Re: Cursor and OCI

From: Witold Iwaniec <wiwaniec_at_novalistech.com>
Date: 2000/05/24
Message-ID: <j_VW4.30562$HG1.618694@nnrp1.uunet.ca>#1/1

Hi

In a code that executes a stored procedure that returns a value in an OUT parameter I followed the order (Oracle 8.0.5):

Get statement handle
Prepare statement
Bind variable
Execute statement

After the statement is executed the value set by the procedure is already in the bound variable. You don't need to call a Bind or Fetch again. But I have never used a procedure that returns a result set so it may be different...

HTH Witold

---

Witold Iwaniec
Senior Software Developer
NovaLIS Technologies
wiwaniec_at_novalistech.com
http://www.novalistech.com

"frankv" <frankNOfrSPAM_at_mancala.com.invalid> wrote in message news:24cb8274.be2041b7_at_usw-ex0105-036.remarq.com...
> 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-
> Frank
>
> Here's my code example:
>
> file://allocate statement handle for stored proc
> OCIStmt * statement;
> result = OCIHandleAlloc((dvoid *) env, (dvoid **) &statement,
> OCI_HTYPE_STMT, 0, (dvoid **) 0);
>
> file://prepare the statement
> text stmtText[] = "begin pckg.proc(:cursor); end;";
> result = OCIStmtPrepare(statement, err, stmtText, (ub4)
> strlen(stmtText),
> OCI_NTV_SYNTAX, OCI_DEFAULT);
>
> file://allocate second statement handle for the cursor
> OCIStmt * cursor;
> result = OCIHandleAlloc((dvoid *) env, (dvoid **) &cursor,
> OCI_HTYPE_STMT, 0, (dvoid **) 0);
>
> file://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);
>
> file://execute
> result = OCIStmtExecute(svc, statement, err,
> (ub4) 1, (ub4) 0, (OCISnapshot *) NULL,
> (OCISnapshot *) NULL, OCI_DEFAULT);
>
> file://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);
>
> file://result is error here!!!!!!!!!!!
>
> file://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 Wed May 24 2000 - 00:00:00 CDT