Home » Developer & Programmer » Precompilers, OCI & OCCI » hoe to retrieve data from ref cursors ??
hoe to retrieve data from ref cursors ?? [message #93907] Wed, 19 March 2003 23:36 Go to next message
Sudha
Messages: 29
Registered: November 2000
Junior Member
How do I fetch from a REF CURSOR ?? As per my knowledge, the REF cursors have to be bound to a OCIStmt object. Upon performing the first fetch , the bound statement handle becomes a valid one.
I have written the following program :

text * plsql = " begin
open :rc for select id from employee;
end;";
.
.
.
OCIStmt *p_stmt1;
OCIStmt *p_stmt2;
.
.
.
OCIStmtPrepare( p_stmt1,...,plsql,...);
OCIBindByName(p_stmt1,...,";rc",....,(dvoid *)&p_stmt2,SQLT_RSET....);
OCIStmtExecute(...,p_stmt1,...);
OCIStmtFetch(p_stmt1,...,1,...);

now that p_stmt2 is valid, how do i fetch from it ?? I tried the following , but doesnt work:

int id =0;
OCIBindByName(p_stmt2,...,"id",...,&id,...);
OCIStmtFetch(p_stmt2,.....);
printf("%d",id);

Can anyone help me ??
Thanks,
Sudha.
Re: hoe to retrieve data from ref cursors ?? [message #151339 is a reply to message #93907] Wed, 14 December 2005 06:40 Go to previous message
manav.sah
Messages: 15
Registered: September 2005
Location: India
Junior Member

hopr this will solve ur problem....


sword status;
OCIBind *bndp[2];
OCIDefine *defnp[2];
OCIDefine *dfn2p[2];

char *citycode= (char *)malloc(sizeof(char)*6);
char *city= (char *)malloc(sizeof(char)*25);

printf("getdata\n");

status=checkError(errhp,
(sword)OCIHandleAlloc((dvoid *)envhp,(dvoid **)&stmthp, OCI_HTYPE_STMT, 0,0), errmsg);
if(status!=OCI_SUCCESS){
printf("a->%s\n", errmsg);
return 0;
}
else
printf("a\n");
/// statement handler for the ref cursor
status=checkError(errhp,
(sword)OCIHandleAlloc((dvoid *)envhp,(dvoid **)&stmthpdata, OCI_HTYPE_STMT, 0,0), errmsg);
if(status!=OCI_SUCCESS){
printf("a1->%s\n", errmsg);
return 0;
}
else
printf("a1\n");


status=checkError(errhp,
(sword)OCIStmtPrepare(stmthp,errhp,(text *)sql, (ub4)strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT), errmsg);
if(status!=OCI_SUCCESS){
printf("b->%s\n", errmsg);
return 0;
}
else
printf("b\n");


status=checkError(errhp,
OCIBindByName(stmthp, bndp, errhp,(text*) ":rs", (sb4)(strlen(":rs")),(dvoid *)&stmthpdata,0,SQLT_RSET,0,0,0,0,0,OCI_DEFAULT),errmsg);
if(status!=OCI_SUCCESS){
printf("01->%s\n", errmsg);
return 0;
}
else
printf("01\n");

status=checkError(errhp,
(sword)OCIStmtExecute(svchp,stmthp,errhp,(ub4)1,
(ub4)0,(CONST OCISnapshot *)NULL,(OCISnapshot *)NULL,OCI_DEFAULT), errmsg);//OCI_DEFAULT));
if(status!=OCI_SUCCESS){
printf("2->%s\n", errmsg);
return 0;
}
else
printf("2\n");

status=checkError(errhp,
(sword)OCIDefineByPos(stmthpdata,&defnp[0], errhp, 1, (dvoid **)citycode, 7, SQLT_STR, 0, 0, 0, OCI_DEFAULT), errmsg);
if(status!=OCI_SUCCESS){
printf("3->%s\n", errmsg);
return 0;
}
else
printf("3\n");

status=checkError(errhp,OCIStmtFetch2(stmthpdata, errhp,1, OCI_DEFAULT, (sb4)0,OCI_DEFAULT),errmsg);
if(status!=OCI_SUCCESS){
printf("5->%s", errmsg);
return 0;
}
else
printf("5");

while(status!=OCI_NO_DATA){
printf("%s\t%s\n", city,citycode);
status= OCIStmtFetch2(stmthpdata, errhp,1, OCI_DEFAULT, (sb4)0,OCI_DEFAULT);
}
return OCI_SUCCESS;




best regards...
manav sah
Previous Topic: OCCI resultset navigation.....some thing like move previous()
Next Topic: Bench Mark Results
Goto Forum:
  


Current Time: Thu Mar 28 07:09:57 CDT 2024