Passing arrays to Oracle stored procedures

Here is a how to pass an array ( Collection ) from java to oracle stored procedure as an IN Variable

Oracle

Create varray

CREATE OR REPLACE
TYPE EMPID IS VARRAY(1000) OF VARCHAR2(30);
/

have the created VARRAY as a invariable in your procedure


PROCEDURE EMPCREATE(EID IN EMPID,ERRMSG OUT VARCHAR2)

You can loop through the array in this way


FOR i IN 1..EID.COUNT LOOP
EMPID := EID(I) ;
END LOOP;

Java

Import the following to the database utility class

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

ArrayDescriptor empIds= ArrayDescriptor.createDescriptor("EID", );
ARRAY empIdsarray= new ARRAY(empIds, con,(Object [])v.toArray() );
// v is the vector of employee id's



It served a good purpose for me when there is a lot of data to be passed from java to oracle.The peformance improvement is tremendous .