Call Stoered Procedure /Stored Function with ARRAY Parameters

PL/SQL
create or replace type EMP_REC as object
(
employee_id number(6),
last_name varchar2(25),
job_id varchar2(10)
)
=============================
create or replace
TYPE EMP_TABLE AS TABLE OF EMP_REC
================================
create or replace
PACKAGE BODY PK_PROD AS

PROCEDURE INSERT_PROD(p_iorec IN EMP_TABLE) AS
BEGIN
/* TODO implementation required */
FOR i IN 1..p_iorec.count LOOP
--get p_iorec(i).EMP_NAME
insert into productmaster values(p_iorec(i).employee_id, p_iorec(i).last_name, p_iorec(i).job_id);
END LOOP;

END INSERT_PROD;

PROCEDURE INSERT_PROD1 AS
dt varchar2(20);
BEGIN
/* TODO implementation required */

--select sysdate into dt from dual;
insert into productmaster values(2,'as', 'asd');
END INSERT_PROD1;

END PK_PROD;
Java Code:
con = uow.getAccessor().getConnection();
//code to create the struct+array
oracle.sql.StructDescriptor structDesc = StructDescriptor.createDescriptor("EMP_REC", con);
oracle.sql.ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("EMP_TABLE", con);
...
oracle.sql.ARRAY newArray = new ARRAY(arrayDesc, con, arr.toArray());

//code to create and execute the query:
StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("PK_PROD.INSERT_PROD");
call.addUnamedArgument("in_array");

DataModifyQuery query = new DataModifyQuery();
query.setShouldBindAllParameters(true);
query.setCall(call);
query.addArgument("in_array");
Vector args = new Vector(1);
args.addElement(newArray);

getSession().executeQuery(query, args);



If using TopLink 11, you can have TopLink create the Array and Struct objects for you - so you don't need to get the connection and build the objects yourself. The following code assumes that the EMP_REC struct is mapped to an Employee object using a TopLink Descriptor, see http://www.oracle.com/technology/products/ias/toplink/doc/10131/main/_html/prjdaun003.htm for details on how to do this.

//code to create the java objects representing the ARRAY and STRUCT
emptable= new Vector(); //represents the ARRAY
//build each employee and add it to the emptable
Employee emp = new Employee();
..
emptable.add(emp);

StoredProcedureCall call = new StoredProcedureCall();
call.setProcedureName("PK_PROD.INSERT_PROD");
//Tell TopLink what the ARRAY contains
ObjectRelationalDatabaseField ordf = new ObjectRelationalDatabaseField("");
ordf.setSqlType(Types.STRUCT);
ordf.setSqlTypeName("EMP_REC");
ordf.setType(Employee.class);
call.addUnamedArgument("in_array", Types.ARRAY, "EMP_TABLE", ordf);
//build the reusable query
DataModifyQuery query = new DataModifyQuery();
query.setShouldBindAllParameters(true);
query.setCall(call);
query.addArgument("in_array");

Vector args = new Vector(1);
args.addElement(emptable);

getSession().executeQuery(query, args);