Search This Blog

Monday, May 25, 2009

Passing Arralist to package

Declaration of Types
-------------------------
create or replace type EMP_REC as object(
employee_id number(6),
last_name varchar2(25),
job_id varchar2(10))
=============================
create or replaceTYPE EMP_TABLE AS TABLE OF EMP_REC
=============================================
PACKAGE
create or replacePACKAGE 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
========
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
con = DriverManager.getConnection(connectUrl, "HR", "oracle");
//Statement s = con.createStatement();
//ResultSet rs = s.executeQuery("Select * from employees");
//while (rs.next()){
//System.out.println(rs.getString(2));
//}
oracle.jdbc.OracleCallableStatement ocs = null;
oracle.sql.StructDescriptor structDesc =
StructDescriptor.createDescriptor("EMP_REC", con);
oracle.sql.ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("EMP_TABLE", con);
ArrayList arr = new ArrayList();
String[] objName;
ListIterator LItr = rows.listIterator();
while (LItr.hasNext()) {
// objName=(String[])LItr.next();
// Object [] singleobj = {
// objName[0],objName[1],objName[2]
// };
oracle.sql.STRUCT p1struct = new STRUCT(structDesc, con, (Object[])LItr.next()); //singleobj);
arr.add(p1struct);
}
oracle.sql.ARRAY newArray =new ARRAY(arrayDesc, con, arr.toArray());
ocs =
//(OracleCallableStatement)con.prepareCall("begin PK_PROD.INSERT_PROD1; end;");
(OracleCallableStatement)con.prepareCall("begin PK_PROD.INSERT_PROD(:1); end;"); ocs.setARRAY(1, newArray);
ocs.execute();
con.commit();
con.close();
ocs.close();

No comments: