Java Database Connectivity (JDBC)

Hi,

I am calling a oracle stored procedure using callable statement which has IN and OUT parameter of same type.
IN and OUT are array of objects. (ie) IN parameter as Array of Objects and OUT parameter as Array of Objects

here are the steps i have done as advised from oracle forum. correct me if i am in wrong direction

ORACLE types and Stored Procedure

CREATE OR REPLACE
TYPE APPS.DEPARTMENT_TYPE AS OBJECT (
DNO NUMBER (10),
NAME VARCHAR2 (50),
LOCATION VARCHAR2 (50)
);


CREATE OR REPLACE
TYPE APPS.DEPT_ARRAY AS TABLE OF department_type;


CREATE OR REPLACE package body APPS.insert_object
IS

PROCEDURE insert_object_prc (d IN dept_array, d2 OUT dept_array)
IS
BEGIN
d2 := dept_array ();

FOR j IN 1 .. d.COUNT
LOOP
d2.EXTEND;
d2 (j) := department_type (d (j).dno, d (j).name, d(j).location);
END LOOP;
END insert_object_prc;
END insert_object;
/




JAVA CODE

Value Object
package com.custom.vo;

public class Dep {
public int empNo;
public String depName;
public String location;

public int getEmpNo() {
return empNo;
}

public void setEmpNo(int empNo) {
this.empNo = empNo;
}

public String getDepName() {
return depName;
}

public void setDepName(String depName) {
this.depName = depName;
}

public String getLocation() {
return location;
}

public void setLocation(String location) {
this.location = location;
}

}


to call stored procedure

package com.custom.callable;

import com.custom.vo.Dep;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class CallableArrayTryOut {

private static OracleDataSource odcDataSource = null;

public static void main(String[] args) {
OracleCallableStatement callStatement = null;
OracleConnection connection = null;

try {
odcDataSource = new OracleDataSource();
odcDataSource
.setURL("......");
odcDataSource.setUser("....");
odcDataSource.setPassword("....");
connection = (OracleConnection) odcDataSource.getConnection();
} catch (Exception e) {
System.out.println("DB connection Exception");
e.printStackTrace();
}

Dep[] dep = new Dep[2];
dep[0] = new Dep();
dep[0].setEmpNo(100);
dep[0].setDepName("aaa");
dep[0].setLocation("xxx");

dep[1] = new Dep();
dep[1].setEmpNo(200);
dep[1].setDepName("bbb");
dep[1].setLocation("yyy");

try {
StructDescriptor structDescriptor = new StructDescriptor(
"APPS.DEPARTMENT_TYPE", connection);
STRUCT priceStruct = new STRUCT(structDescriptor, connection, dep);
STRUCT[] priceArray = { priceStruct };
ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(
"APPS.DEPT_ARRAY", connection);
ARRAY array = new ARRAY(arrayDescriptor, connection, priceArray);

callStatement = (OracleCallableStatement) connection
.prepareCall("{call insert_object.insert_object_prc(?,?)}");
((OracleCallableStatement) callStatement).setArray(1, array);
callStatement.registerOutParameter(2, OracleTypes.ARRAY,
"APPS.DEPT_ARRAY");
callStatement.execute();
ARRAY outArray = callStatement.getARRAY(2);
Datum[] datum = outArray.getOracleArray();
for (int i = 0; i < datum.length; i++) {

oracle.sql.STRUCT os = (oracle.sql.STRUCT) datum[0];

Object[] a = os.getAttributes();
for (int j = 0; j < a.length; j++) {
System.out.print("Java Data Type :"
+ a[j].getClass().getName() + "Value :" + a[j]
+ "\n");
}
}
connection.commit();
callStatement.close();
} catch (Exception e) {
System.out.println("Mapping Error");
e.printStackTrace();
}

}

}


THE ERROR

Mapping Errorjava.sql.SQLException: Inconsistent java and sql object types

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1130)
at oracle.sql.StructDescriptor.toOracleArray(StructDescriptor.java:823)
at oracle.sql.StructDescriptor.toArray(StructDescriptor.java:1735)
at oracle.sql.STRUCT.<init>(STRUCT.java:136)
at com.custom.callable.CallableArrayTryOut.main(CallableArrayTryOut.java:48)