Ref cursor support v15
The EDB Postgres Advanced Server Open Client Library supports the use of REF CURSOR
as OUT
parameters in PL/SQL procedures that are compatible with Oracle. Support is provided through the following APIs:
OCIBindByName
OCIBindByPos
OCIBindDynamic
OCIStmtPrepare
OCIStmtExecute
OCIStmtFetch
OCIAttrGet
The EDB OCL Connector also supports the SQLT_RSET
data type.
This example invokes 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
, was created on the database server and that the required handles were 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 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's 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))