views:

226

answers:

0

When I try to pass an array of numbers to a stored procedure, the stored procedure is not able to get the array contents. Array.count = 0 in the stored procedure!

The stored procedure is pretty simple. It just has to insert the parameters into a table. code looks like :

TYPE arr_parmid IS TABLE OF testtable.UNID%TYPE INDEX BY BINARY_INTEGER;

CREATE OR REPLACE FUNCTION testfunc( 
    srq_id          integer ,
    unid            IN arr_parmid)
RETURN INTEGER AS
BEGIN 
  DBMS_OUTPUT.PUT_LINE('srq_id = ' || testfunc.srq_id
                       || 'count = ' || testfunc.unid.count);
  Insert into testtable values (srq_id , unid(1));
END;

My C code that invokes this stored procedure does the following

  1. Allocate & init all the OCI handles needed
  2. Do a stmtPrepare
  3. Bind the variables as shown below
  4. And then does OCIStmtExecute()
  5. while srq_id gets passed correctly, there is problem with the array. Count = 0;

Binding:

OCIBindByName(stmthp, (OCIBind **)&bindp[1], errhp, (text *)":srq_id",(sb4)-1,
   (dvoid *) &srqid, (sb4) sizeof(ub4), (ub2)SQLT_INT,
   (dvoid *) 0, (ub2 *)0, (ub2 *)0,
    (ub4)0,  (ub4 *)0, (ub4) OCI_DEFAULT) 

OCIBindByName(stmthp,(OCIBind **) &bindp[2], errhp, (text *)":unid",(sb4)-1,
     (dvoid *) &unid[0], (sb4) sizeof(unid[0]), (ub2)SQLT_NUM,
     //(dvoid *) 0, (ub2 *)&alen[1], (ub2 *)0,
     (dvoid *) &ind[1], (ub2 *)&alen[1], (ub2 *)&rc[1],
     (ub4) MAX, (ub4 *) &curele, (ub4) OCI_DEFAULT)

OCIBindArrayOfStruct(bindp[2], errhp, (ub4)sizeof(unid[0]),
                     (ub4)sizeofub2,(ub4)sizeofub2,(ub4)sizeofub2)

Can somebody please help me in passing an array to a stored procedure through an OCI call?