Re: OCI and Stored Procedures.

The key thing is you have to bind a pointer to a variable of type OCIStmt to the ref cursor (note: this must be different than the 1 used in the OCIStmtPrepare).

 OCIStmt *oStmt; // used in prepare  OCIStmt *oCursorStmt; // used for result set .
.
 iReturn = OCIStmtPrepare (oStmt, oErr, (unsigned char *)"begin sptest(:P_DEPTNO, :P_DNAME, :P_LOC, :P_CURSOR); end;", 58, OCI_NTV_SYNTAX, OCI_DEFAULT);
.
.
 iReturn = OCIBindByName(oStmt, &oBind[3], oErr, (unsigned char *)":P_CURSOR", -1, (dvoid *)&oCursorStmt, 0, SQLT_RSET, 0, 0, 0, 0, 0, OCI_DEFAULT); Then you execute the original statement allocated

 iReturn = OCIStmtExecute(oServerContext, oStmt, oErr, (ub4) iIteration, (ub4) 0, (OCISnapshot *)NULL, (OCISnapshot *) NULL, OCI_DEFAULT);

then you bind you variable to the result set using the OCIStmt * bound to the result set

 iReturn = OCIDefineByPos(oCursorStmt, &oDefine[(iColNbr - 1)], oErr, iColNbr, ptrCol, lLength, iDbDataType, 0, 0, 0, OCI_DEFAULT);

then you fetch from the OCIStmt * bound to the result set

 iReturn = OCIStmtFetch(oCursorStmt, oErr, 1,OCI_FETCH_NEXT, OCI_DEFAULT);

These are the important points. My good sample is at work, otherwise I would be able to post a complete sample. Hopefully this will help

DeVerne Coleman

Craig Hier wrote in message ...
>Hello All:
>
> I am having a little problem. Ok, make that a big problem. I
>need to use stored procedures to return a multi-row set. The only way I
>have found to do that is via a ref cursor. That works in PL/SQL. The
>problem lies in getting the data from a Visual C++ program using OCI.
>Does anybody have any idea of how to do this?
>
> Craig Hier
> chier_at_fms-onlineNOSPAM.com (You know what to do....)
Received on Thu Sep 23 1999 - 19:58:00 CDT