How to bind a collection in OCI?

From: Daniel Kunz <dkunz_at_europe.com>
Date: 6 Apr 2004 00:11:58 -0700
Message-ID: <614e041b.0404052311.566b91e0@posting.google.com>

I would like to convert the following PL/SQL procedure to C. This procedure works properly and displays
SQL> exec testcollection;

value: 2
value: 3
value: 5

as output when executed. By using a collection as bind variable, I'm able to supply a set of values as input for a SELECT statement. This works rather nicely in PL/SQL. However, I was unable so far to convert this routine into an equivalent program using OCI.

CREATE OR REPLACE TYPE testval_type AS OBJECT (

        value NUMBER
);
/
show errors;

CREATE OR REPLACE TYPE testval_table

        AS table of testval_type;
/
show errors;

CREATE OR REPLACE PROCEDURE TestCollection IS

	TYPE resCur IS REF CURSOR;
	crs		 resCur;
	stmt      varchar2(1000);
	vvalue    number;
	vTestList testval_table;
BEGIN --TestCollection
	vTestList := testval_table();

	vTestList.extend(1);
	vTestList(1) := testval_type(2);
	vTestList.extend(1);
	vTestList(2) := testval_type(3);
	vTestList.extend(1);
	vTestList(3) := testval_type(5);

	stmt := 'SELECT value FROM TABLE(CAST(:1 as testval_table))';
	open crs for stmt using vTestList;
	loop	
		fetch crs into vvalue; 
		exit when crs%notfound;

		dbms_output.put_line('value: ' || vvalue);
	end loop;
	close crs;

END TestCollection;
/
show errors;
/
exec testcollection;

So far for the PL/SQL version. The equivalent OCI program looks like this (it runs without any error, but for some strange reason OCIStmtFetch() always returns 100 (OCI_NO_DATA) as result, and not the numbers I've fed into the collection. I guess that my collection somehow didn't make it to Oracle (I'm using Oracle 9.2) - perhaps because of some missing or incorrect bind call? - and Oracle thinks the collection is empty (although OCITableSize() reports that my collection has a size > 0).

static text *sel1int = (text *)"SELECT value FROM TABLE(CAST(:1 as testval_table))";

   OCIStmt *sel1stmthp;
   OCINumber sel1val_num;
   OCIType *ttab_tdo = (OCIType *) 0;
   OCIArray *ttab_obj = NULL;
   OCIInd *ind = NULL;
   boolean exist = FALSE;
   sword result;
   sb4 tabsize;

   OCINumber testval;
   unsigned int value;

   result = OCIHandleAlloc( (dvoid *) gctx->envhp,

(dvoid **) &sctx->sel1stmthp,

                           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
   if (result != OCI_SUCCESS)
      printf("error\n");

   result = OCIStmtPrepare(sctx->sel1stmthp, 
                           gctx->errhp, (text *)sel1int,


                           (ub4) strlen((char *)sel1int),

                           (ub4) OCI_NTV_SYNTAX, 

                           (ub4) OCI_DEFAULT);

   if (result != OCI_SUCCESS)
      printf("error\n");

   /* bind the variables */
   result = OCIDefineByPos(sctx->sel1stmthp,

                           &sel1def1p, gctx->errhp, 1, 


                           (dvoid *) &sel1val_num,

                           (sb4) sizeof(unsigned char[22]), 

                           SQLT_VNU, (dvoid *) 0, (ub2 *)0,


                           (ub2 *)0, OCI_DEFAULT);

   if (result != OCI_SUCCESS)
      printf("error\n");

   result = OCIBindByPos(sctx->sel1stmthp, 
                        &sel1bnd1p, gctx->errhp, 1, 
                        (dvoid *) 0, (sb4) 0, SQLT_NTY,
                        (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, 
                        (ub4) 0, (ub4 *) 0, OCI_DEFAULT);
   if (result != OCI_SUCCESS)
      printf("error\n");

   result = OCITypeByName(gctx->envhp, gctx->errhp, 
                        gctx->svchp, (const text *) 0, (ub4) 0, 
                        (const text *) "TESTVAL_TABLE", 
                        (ub4) strlen((const char *) "TESTVAL_TABLE"),
                        (CONST text *) 0, (ub4) 0, 
                        OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, 
                        &ttab_tdo);
   if (result != OCI_SUCCESS)
      printf("error\n");

   result = OCIObjectNew(gctx->envhp, gctx->errhp, 
                        gctx->svchp, OCI_TYPECODE_VARRAY, 
                        (OCIType *) ttab_tdo, (dvoid *) 0, 
                        OCI_DURATION_SESSION, (ub1) TRUE, 
                        (dvoid **) &ttab_obj);
   if (result != OCI_SUCCESS)
      printf("error\n");

   result = OCIBindObject(sel1bnd1p, gctx->errhp, 
                        ttab_tdo, (dvoid **) &ttab_obj, 
                        (ub4 *) 0, (dvoid **) &ind, (ub4 *) 0);
   if (result != OCI_SUCCESS)
      printf("error\n");

   // assign some values to the collection

   value = 2;
   result = OCINumberFromInt(gctx->errhp, (dvoid *) &value,

                              sizeof(unsigned int), OCI_NUMBER_SIGNED, 
                              (struct OCINumber *) &testval);
   if (result != OCI_SUCCESS)
      printf("error\n");

   result = OCICollAppend(gctx->envhp, gctx->errhp,


                           (CONST dvoid*) &testval, (CONST dvoid*) NULL, 

                           (OCIColl *) ttab_obj);

   if (result != OCI_SUCCESS)
      printf("error\n");

   value = 3;
   result = OCINumberFromInt(gctx->errhp, (dvoid *) &value,

                              sizeof(unsigned int), OCI_NUMBER_SIGNED, 
                              (struct OCINumber *) &testval);
   if (result != OCI_SUCCESS)
      printf("error\n");

   result = OCICollAppend(gctx->envhp, gctx->errhp,


                           (CONST dvoid*) &testval, (CONST dvoid*) NULL, 

                           (OCIColl *) ttab_obj);

   if (result != OCI_SUCCESS)
      printf("error\n");

   value = 5;
   result = OCINumberFromInt(gctx->errhp, (dvoid *) &value,

                              sizeof(unsigned int), OCI_NUMBER_SIGNED, 
                              (struct OCINumber *) &testval);
   if (result != OCI_SUCCESS)
      printf("error\n");

   result = OCICollAppend(gctx->envhp, gctx->errhp,


                           (CONST dvoid*) &testval, (CONST dvoid*) NULL, 

                           (OCIColl *) ttab_obj);

   if (result != OCI_SUCCESS)
      printf("error\n");

   // OCITableSize (only called for testing purposes) returns 3

   result = OCITableSize(gctx->envhp, gctx->errhp,

(CONST OCITable*) ttab_obj, &tabsize);
   if (result != OCI_SUCCESS)

      printf("error\n");

   result = OCIStmtExecute(gctx->svchp, sctx->sel1stmthp,

                           gctx->errhp, (ub4) 0, (ub4) 0,


                           (CONST OCISnapshot *) NULL, 

                           (OCISnapshot *) NULL, OCI_DEFAULT );

   if (result != OCI_SUCCESS)
      printf("error\n");

   // this function always returns OCI_NO_DATA as result,    // can somebody please tell me what I'm doing wrong?

   result = OCIStmtFetch(sctx->sel1stmthp, gctx->errhp,

                        (ub4) 1, OCI_FETCH_NEXT, OCI_DEFAULT);

  Thanks in advance,

      Danny. Received on Tue Apr 06 2004 - 02:11:58 CDT