| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: OCI mapping onto pl/sql nested table
Terry,
here's a small example. =20
You will need to create these objects first:
create type vc2_4000_array as table of varchar2(4000); /
create procedure load_array (p_array OUT vc2_4000_array)
is
begin
select distinct object_name
bulk collect into p_array
from all_objects
where rownum <=3D 100;
The oci code binds an array to the plsql proc, the proc executes and fills the array, the array is then traversed and the contents displayed.
Pretty straightforward.
For readability, I've removed as much type casting as possible. When you compile you'll get tons of warnings, but you can ignore them (just for this little demo ofcourse ;),=20 the code will run just fine.
I urge you to put in *all* the casting when you are coding oci for real.
I've also used only the generic error handling function that is found in the oci docs, you're going to want to do more error handling to be safe.
I tested and ran the following code on my laptop (redhat AS 3.0 running oracle 10).
I ran it like this (file is called oci_nt2.c):
=20
$ make -f demo_rdbms.mk build OBJS=3Doci_nt2.o EXE=3Doci_nt2=20
$ ./oci_nt2
#include <stdio.h> #include <stdlib.h> #include <string.h> #include <oci.h>
void checkerr(OCIError *errhp, sword status);
OCIEnv *envhp; /* standard handles for oci programs... */ OCIServer *srvhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp;
typedef OCITable vc2_4000_array; /* define the SQL nested table type vc2_4000_array as an OCITable type */
int main(int argc, char *argv[])
{
OCIBind *bndp =3D 0; /* bind handle */
OCIType *typeDesc =3D 0; /* type descriptor */
OCINumber cnt; /* number of elements in the array */
vc2_4000_array *out_array; /* OCI representation of the SQL nested table type */
OCIIter *iterator; /* after the array is bound, use it to create an oci iterator
OCIInd *element_ind; /* element of the array */
text *plsql =3D "begin load_array(:out_array); end;";
// prepare oci environment and log on
OCIInitialize(OCI_OBJECT,0,0,0,0);
OCIEnvInit(&envhp,OCI_DEFAULT,0,0);
OCIHandleAlloc((dvoid *)envhp,(dvoid
**)&errhp,(ub4)OCI_HTYPE_ERROR,(size_t)0,(dvoid **)0);
checkerr(errhp,OCIHandleAlloc((dvoid *)envhp,(dvoid **)&srvhp,(ub4)OCI_HTYPE_SERVER,(size_t)0,(dvoid **)0));
checkerr(errhp,OCIHandleAlloc((dvoid *)envhp,(dvoid
**)&svchp,OCI_HTYPE_SVCCTX,(size_t)0,(dvoid **)0));
=20
checkerr(errhp,OCILogon(envhp,errhp,&svchp,"scott",5,"tiger",5,"",0));
printf("\nConnected.\n");
/* allocate handle for plsql statement */ checkerr(errhp, OCIHandleAlloc((dvoid *)envhp,(dvoid **)&stmthp,(ub4)OCI_HTYPE_STMT,(size_t)0,(dvoid **)0));
/* prepare the statement */
checkerr(errhp, OCIStmtPrepare(stmthp,errhp,plsql,(ub4)strlen((char
*)plsql),(ub4)OCI_NTV_SYNTAX,(ub4)OCI_DEFAULT));
/* bind calls for collections -> oci */
checkerr(errhp,
OCITypeByName(envhp,errhp,svchp,0,0,"VC2_4000_ARRAY",strlen("VC2_4000_AR
RAY"),0,0,
=20
OCI_DURATION_SESSION,OCI_TYPEGET_HEADER,&typeDesc));
=20
checkerr(errhp,OCIBindByName(stmthp,&bndp,errhp,":out_array",-1,&cnt,siz
eof(OCINumber),SQLT_NTY,0,0,0,0,0,OCI_DEFAULT));
checkerr(errhp,
OCIObjectNew(envhp,errhp,svchp,OCI_TYPECODE_TABLE,typeDesc,0,OCI_DURATIO
N_DEFAULT,TRUE,&out_array));
checkerr(errhp,
OCIBindObject(bndp,errhp,typeDesc,&out_array,0,0,0));
/* execute plsql */
checkerr(errhp,
OCIStmtExecute(svchp,stmthp,errhp,1,0,NULL,NULL,OCI_DEFAULT));
/* create iterator for using the OUT array */ checkerr(errhp,OCIIterCreate(envhp,errhp,out_array,&iterator));
/* Loop through the array using the iterator */ boolean eoc =3D FALSE; /* end of collection */ dvoid *element =3D 0; /* one of the values in the array */
printf("\nDump contents of the array:\n");
printf("----------------------------\n");
while (!eoc) {
OCIIterNext(envhp,errhp,iterator,&element,&element_ind,&eoc);
if (*element_ind =3D=3D 0) printf("%s\n",OCIStringPtr(envhp,
*(OCIString **)element));
}
/* cleanup */
checkerr(errhp,OCIIterDelete(envhp,errhp,&iterator));
checkerr(errhp,OCIHandleFree(stmthp, OCI_HTYPE_STMT));
checkerr(errhp,OCIHandleFree(srvhp, OCI_HTYPE_SERVER));
checkerr(errhp,OCIHandleFree(svchp, OCI_HTYPE_SVCCTX));
checkerr(errhp,OCIHandleFree(errhp, OCI_HTYPE_ERROR));
}
void checkerr(OCIError *errhp, sword status)
{
text errbuf[512];
sb4 errcode =3D 0;
if (status) {
printf("Error occured\n");
printf("Status: %i\n",status);
switch (status)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
printf("Error - OCI_SUCCESS_WITH_INFO\n");
break;
case OCI_NEED_DATA:
printf("E rror - OCI_NEED_DATA\n");
break;
case OCI_NO_DATA:
printf("Error - OCI_NODATA\n");
OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL,
&errcode, errbuf, (ub4) sizeof(errbuf),
OCI_HTYPE_ERROR);
printf("Error - %.*s\n", 512, errbuf);
break;
case OCI_ERROR:
OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL,
&errcode, errbuf, (ub4) sizeof(errbuf),
OCI_HTYPE_ERROR);
printf("Error - %.*s\n", 512, 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("Er ror - OCI_CONTINUE\n");
break;
default:
printf("Unknown Error\n");
break;
}
exit(-1);
}
It's really not so bad once you get used to looking at it (just takes forever to write compare to regular plsql).
Once you logon the steps are pretty much like dbms_sql: allocate plsql handle -> bind -> prepare stmt -> execute
>From there:
create an iterator -> traverse the array and display the contents
Finally:
cleanup
A handy technique for oci programming is to make generic wrapper
functions=20
(routines so your code isn't always tons of lines long).
For example, you can make a function to execute all the init and logon
calls, function to prepare, bind, and exec statements, etc...=20
Hope that helps,
Anthony
-----Original Message-----
From: Terry Barnett [mailto:tbarne_at_landmark-information.co.uk]=20
Sent: Monday, December 13, 2004 5:11 PM
To: oracle-l_at_freelists.org
Subject: OCI mapping onto pl/sql nested table
Does anybody have any examples of OCI code that maps onto a pl/sql procedure out parameter which is a nested table of varchar2(4000).
Cheers,
Terry
=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D=
3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D
=3D3D=3D
=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D
This email and any files transmitted
with it are confidential and intended
solely for the use of the individual
or entity to whom they are addressed.
If you have received this email in=3D20
error please notify Landmark=3D20
Information Group on +44(0) 1392=3D20
441700.
For more information about the=3D20
Landmark Information Group visit http://www.landmark-information.co.uk
This email and any attachments have
been scanned for viruses and to the
best of our knowledge are clean.
=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D=
3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D
=3D3D=3D
=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D=3D3D
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Dec 13 2004 - 20:58:06 CST
 |
 |