Postgres Plus Advanced Server Oracle Compatibility Developer's Guide : 9.2 Ref Cursor Support

Previous PageTable Of ContentsNext Page

Postgres Plus Advanced Server Oracle Compatibility Developer's Guide

 

9.2 Ref Cursor Support

The Advanced Server Open Client Library supports the use of Oracle-compatible REF CURSOR's as OUT parameters in PL/SQL procedures. Support is provided through the following API's:

      OCIBindByName

      OCIBindByPos

      OCIBindDynamic

      OCIStmtPrepare

      OCIStmtExecute

      OCIStmtFetch

      OCIAttrGet

OCL also supports the SQLT_RSET data type.

The following example demonstrates how to invoke a stored procedure that opens a cursor and returns a REF CURSOR as an output parameter. The code sample assumes that a PL/SQL procedure named openCursor (with an OUT parameter of type REF CURSOR) has been created on the database server, and that the required handles have been allocated:

      char * openCursor =
      "begin \
      openCursor(:cmdRefCursor); \
      end;";
      OCIStmt *stmtOpenRefCursor;
      OCIStmt *stmtUseRefCursor;

Allocate handles for executing a stored procedure to open and use the REF CURSOR:

      /* Handle for executing the stored procedure to open the ref cursor */
      OCIHandleAlloc((dvoid *) envhp,
      (dvoid **) &stmtOpenRefCursor,
      OCI_HTYPE_STMT,
      0,
      (dvoid **) NULL));

      /* Handle for using the Ref Cursor */
      OCIHandleAlloc((dvoid *) envhp,
      (dvoid **) &stmtUseRefCursor,
      OCI_HTYPE_STMT,
      0,
      (dvoid **) NULL));

Then, prepare the PL/SQL block that is used to open the REF CURSOR:

OCIStmtPrepare(stmtOpenRefCursor,
errhp,
(text *) openCursor,
(ub4) strlen(openCursor),
OCI_NTV_SYNTAX,
OCI_DEFAULT));

Bind the PL/SQL openCursor OUT parameter:

OCIBindByPos(stmtOpenRefCursor,
&bndplrc1,
errhp,
1,
(dvoid*) &stmtUseRefCursor,
/* the returned ref cursor */
0,
SQLT_RSET,
/* SQLT_RSET type representing cursor */
(dvoid *) 0,
(ub2 *) 0,
(ub2) 0,
(ub4) 0,
(ub4 *) 0,
OCI_DEFAULT));

Use the stmtOpenRefCursor statement handle to call the openCursor procedure:

OCIStmtExecute(svchp,
stmtOpenRefCursor,
errhp,
1,
0,
0,
0,
OCI_DEFAULT);

At this point, the stmtUseRefCursor statement handle contains the reference to the cursor. To obtain the information, define output variables for the ref cursor:

/* Define the output variables for the ref cursor */
OCIDefineByPos(stmtUseRefCursor,
&defnEmpNo,
errhp,
(ub4) 1,
(dvoid *) &empNo,
(sb4) sizeof(empNo),
SQLT_INT,
(dvoid *) 0,
(ub2 *)0,
(ub2 *)0,
(ub4) OCI_DEFAULT));

Then, fetch the first row of the result set into the target variables:

/* Fetch the cursor data */
OCIStmtFetch(stmtUseRefCursor,
errhp,
(ub4) 1,
(ub4) OCI_FETCH_NEXT,
(ub4) OCI_DEFAULT))

Previous PageTable Of ContentsNext Page