Re: Stored Proc with Cursor Param

From: Amogh <amogh.r_at_nospam.com>
Date: Sat, 27 May 2006 23:36:15 +0530
Message-ID: <AO0eg.4$3n.28@news.oracle.com>

Geoff wrote:
> One interesting thing is all their c demo files use obsolete oci calls and a
> cursor, a cursor being of type cda_def. Here,
>
> http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci01int.htm
>
> . . . it says, 'Note: cursors are not used in release 8.x or higher'. The
> link you provided,
>
> http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14250/oci05bnd.htm#sthref795
>
> . . . shows using a statement handle as a cursor but there are no examples
> of it in their demo files.
>
> -g
>
>

The doc provides a code snippet:

[paste]

static const text *plsql_block = (text *)

   "begin \

      OPEN :cursor1 FOR SELECT employee_id, last_name, job_id, manager_id, \
              salary, department_id \
              FROM employees WHERE job_id=:job ORDER BY employee_id; \
      OPEN :cursor2 FOR SELECT * FROM departments ORDER BY department_id;

   end;";

status = OCIStmtPrepare (stm1p, errhp, (text *) plsql_block,

              strlen((char *)plsql_block), OCI_NTV_SYNTAX, OCI_DEFAULT); ...
status = OCIBindByName (stm1p, (OCIBind **) &bnd1p, errhp,

              (text *)":cursor1", (sb4)strlen((char *)":cursor1"),
              (dvoid *)&stm2p, (sb4) 0,  SQLT_RSET, (dvoid *)0,
                (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0,   (ub4)OCI_DEFAULT);

[/paste]

stm1p is the statament handle for the entire PL/SQL Block. stm2p is the statement handle being bound for the cursor at pos ":cursor1"

If you are extracting data through a Ref cursor, the OCIDefineByPos would specify/define the statement handle at the specific position (with SQLT_RSET as the type). After you execute the PL/SQL block, use this statement handle(associated with the ref cursor) as a new statement altogether.

Rgds.
Amogh Received on Sat May 27 2006 - 13:06:15 CDT