Home » Developer & Programmer » Precompilers, OCI & OCCI » how to pass array to stored procedure using OCI calls (Linux)
how to pass array to stored procedure using OCI calls [message #444055] Thu, 18 February 2010 09:31
lavanyabl
Messages: 4
Registered: February 2010
Junior Member
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?



Previous Topic: [Oracle][ODBC]Invalid attribute/option identifier. SQL State :HY092
Next Topic: using occi in external XA applications.
Goto Forum:
  


Current Time: Fri Mar 29 09:07:34 CDT 2024