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
- Allocate & init all the OCI handles needed
- Do a stmtPrepare
- Bind the variables as shown below
- And then does OCIStmtExecute()
- 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?