| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: OCI and reference cursors
Description: OCI 8.0.x Program that returns a dynaset or result set from a stored procedure. It uses an REF CURSOR from the stored procedure to fetch upon to create the dynaset
/*
create or replace package reftest as
cursor c1 is select ename from emp;
type empCur is ref cursor return c1%ROWTYPE;
procedure GetEmpData(indeptno IN NUMBER,EmpCursor in out empCur );
END;
create or replace package body reftest as
procedure GetEmpData(indeptno IN NUMBER,EmpCursor in out empCur )
is
begin
open EmpCursor for select ename from emp where deptno =
indeptno;
end;
end;
#include <stdio.h> #include <stdlib.h> #include <string.h> /*
/*
/*
/*
typedef dvoid * PDV;
/*
BOOL oci8Error (sb2 sb2ErrorCode);
UINT oci8Connect (const char *pszUserName, const char *pszPassword,
const char *pszConnection);
void oci8Disconnect (BOOL bEndSession);
/*****************************************************************/UINT oci8Connect (const char *pszUserName, const char *pszPassword, const char *pszConnection)
/*
OCIEnvInit (&or8EnvHandle, OCI_DEFAULT, 0, (dvoid **) 0);
/*
if (oci8Error (OCIHandleAlloc ((dvoid *) or8EnvHandle, (dvoid * *) &or8SrcHandle, OCI_HTYPE_SVCCTX, 0, (dvoid * *) 0))) return OCI8_INITIALIZED; if (oci8Error (OCIHandleAlloc ((dvoid *) or8EnvHandle, (dvoid * *) &or8ErrorHandle, OCI_HTYPE_ERROR, 0, (dvoid * *) 0))) return OCI8_INITIALIZED; /*
if (oci8Error (OCIAttrSet ((dvoid *) or8SrcHandle,
OCI_HTYPE_SVCCTX,
(dvoid *) or8SrvrHandle, (ub4) 0,
OCI_ATTR_SERVER,
or8ErrorHandle))) return OCI8_SERVER_ATTACHED; /*
if (oci8Error (OCIAttrSet ((dvoid *) or8UsrHandle,
OCI_HTYPE_SESSION,
(dvoid *) pszUserName, (ub4) strlen
(pszUserName),
OCI_ATTR_USERNAME, or8ErrorHandle))) return OCI8_SERVER_ATTACHED; if (oci8Error (OCIAttrSet ((dvoid *) or8UsrHandle, OCI_HTYPE_SESSION, (dvoid *) pszPassword, (ub4) strlen (pszPassword), OCI_ATTR_PASSWORD, or8ErrorHandle))) return OCI8_SERVER_ATTACHED; if (oci8Error (OCISessionBegin (or8SrcHandle, or8ErrorHandle, or8UsrHandle, OCI_CRED_RDBMS, OCI_DEFAULT))) return OCI8_SERVER_ATTACHED; if (oci8Error (OCIAttrSet ((dvoid *) or8SrcHandle, OCI_HTYPE_SVCCTX, (dvoid *) or8UsrHandle, (ub4) 0, OCI_ATTR_SESSION, or8ErrorHandle))) return OCI8_SESSION_BEGAN; /*
/*****************************************************************/BOOL oci8Error (sb2 sb2ErrorCode)
text errbuf[512];
sb4 errcode;
switch (sb2ErrorCode) {
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
printf ("Error - OCI_SUCCESS_WITH_INFO\n");
break;
case OCI_NEED_DATA:
printf ("Error - OCI_NEED_DATA\n");
break;
case OCI_NO_DATA:
printf ("Error - OCI_NO_DATA\n");
break;
case OCI_ERROR:
OCIErrorGet ((dvoid *) or8ErrorHandle, (ub4)
1, (OraText *)
NULL, &errcode, errbuf, (ub4)
sizeof(errbuf),
(ub4) OCI_HTYPE_ERROR);
printf ("Error - %s\n", errbuf);
break;
case OCI_INVALID_HANDLE:
printf ("Error - OCI_INVALID_HANDLE\n");
break;
case OCI_STILL_EXECUTING:
printf ("Error - OCI_STILL_EXECUTE\n");
break;
case OCI_CONTINUE:
printf ("Error - OCI_CONTINUE\n");
break;
default:
break;
}
return ((sb2ErrorCode==OCI_SUCCESS) ? FALSE : TRUE );
}
/*****************************************************************/
void oci8Disconnect (BOOL bEndSession)
{
/* Disconnect from server
*/
if (bEndSession == TRUE) {
OCISessionEnd (or8SrcHandle, or8ErrorHandle,
or8UsrHandle,
OCI_DEFAULT);
}
oci8Error (OCIServerDetach (or8SrvrHandle, or8ErrorHandle,
OCI_DEFAULT));
/*
/*****************************************************************/
int main (int argc, char *argv[])
{
UNREFERENCED_PARAMETER(argc); UNREFERENCED_PARAMETER(argv); static unsigned char *szPLSQLBlock = (unsigned char *) "BEGIN RefTest.GetEmpData(:DEPTNO , :empCursor); END;";
static char *pszUserName = "scott"; static char *pszPassword = "tiger"; static char *pszConnection = "tcp803"; sb2 rv; int bv_deptno = 10; char bv_ename[40]; int bv_ename_len = 40; OCIBind *ptrBindHandle1; OCIBind *ptrBindHandle2; OCIDefine *or8Define1; UINT nConnRv = oci8Connect(pszUserName, pszPassword,pszConnection);
/*
* Allocate Stmt handle
*/
rv = OCIHandleAlloc ((PDV) or8EnvHandle, (PDV*)
&or8StmtHandle,
OCI_HTYPE_STMT, 0, (PDV*) 0);
if (rv) {
oci8Disconnect (TRUE);
return 0;
}
/*
* Allocate Stmt handle for cursor resultset
*/
rv = OCIHandleAlloc ((PDV) or8EnvHandle, (PDV*)
&or8CursorStmtHandle,
OCI_HTYPE_STMT, 0, (PDV*) 0);
if (rv) {
oci8Disconnect (TRUE);
return 0;
}
/*
* Prepare statement
*/
rv = OCIStmtPrepare (or8StmtHandle,
or8ErrorHandle,
(OraText *)
szPLSQLBlock,
(ub4)strlen((char
*)szPLSQLBlock),
(ub4)OCI_NTV_SYNTAX,
(ub4)OCI_DEFAULT);
if (rv) {
oci8Error (rv);
exit(1);
}
char *pszBindVarName = ":DEPTNO";
rv = OCIBindByName( or8StmtHandle,
(OCIBind
**)&ptrBindHandle1,
or8ErrorHandle,
(OraText *)
pszBindVarName,
-1, /* (sb4)
strlen(pszBindVarName), */
(dvoid *) &bv_deptno,
(sb4) sizeof
(bv_deptno),
(ub2) SQLT_INT,
(dvoid *) 0,
(ub2 *) 0,
(ub2 *) 0,
(ub4) 0,
(ub4 *)0,
(ub4) OCI_DEFAULT);
if (rv) {
oci8Error (rv);
exit(1);
}
char *pszBindVarName2 = ":empCursor";
rv = OCIBindByName(or8StmtHandle,
(OCIBind
**)&ptrBindHandle2,
or8ErrorHandle,
(OraText *)
pszBindVarName2,
-1, /* (sb4)
strlen(pszBindVarName2), */
(dvoid
*)&or8CursorStmtHandle,
(sb4) 0,
SQLT_RSET,
(dvoid *) 0,
(ub2 *) 0,
(ub2 *) 0,
(ub4) 0,
(ub4 *)0,
(ub4) OCI_DEFAULT);
if (rv) {
oci8Error (rv);
exit(1);
}
rv = OCIStmtExecute(or8SrcHandle,
or8StmtHandle,
or8ErrorHandle,
(ub4) 1,
(ub4) 0,
(CONST OCISnapshot *)
NULL,
(OCISnapshot *) NULL,
OCI_DEFAULT);
if (rv) {
oci8Error (rv);
exit(1);
}
rv = OCIDefineByPos(or8CursorStmtHandle,
(OCIDefine
**)&or8Define1,
or8ErrorHandle,
(ub4) 1,
(dvoid *)&bv_ename,
(sb4) bv_ename_len,
SQLT_STR,
(dvoid *)0,
0, 0,
OCI_DEFAULT);
if (rv) {
oci8Error (rv);
exit(1);
}
sword rc = OCIStmtFetch(or8CursorStmtHandle, or8ErrorHandle,
1, 0, 0);
while (rc != OCI_NO_DATA) {
printf("[%s]\n", bv_ename);
rc = OCIStmtFetch(or8CursorStmtHandle, or8ErrorHandle,
1, 0, 0);
}
oci8Error (OCIHandleFree ((PDV) or8StmtHandle,
OCI_HTYPE_STMT));
oci8Disconnect (TRUE);
return 0;
 |
 |