Passing an array into a Oracle PreparedStatement

Hello,

I am trying to pass an array into a Oracle PreparedStatement, but I cannot seem to get this working. I can get this code to compile without errors but when I run the code I get:

ORA-00932: inconsistent datatypes: expected NUMBER got APAS.NUM_VARRAY

when I uncomment all the ps.execute() code I get this error:

java.sql.SQLException: ORA-00942: table or view does not exist

The code is posted below, I would appreciate any input. This is my
first time trying to pass an array to a PreparedStatement and its pretty tough. THANKS!


String sql = "SELECT PK_EMPLOYEE_ID, ROUND(AVG(xxx),2) xxx, ROUND(AVG(xxx),2) xxx " +
"FROM xxx" +
"WHERE " +
"xxx = ? " +
"xxx = ? " +
"AND xxx = ? " +
"AND xxx = ? " +
"AND PK_EMPLOYEE_ID IN (?) " +
"GROUP BY PK_EMPLOYEE_ID, xxx, xxx, xxx, xxx";

try {
crs = new CachedRowSet();
ps = (connection.getDBConnection()).prepareStatement(sql);


// ps.execute ("DROP TABLE varray_table");
// ps.execute ("DROP TYPE num_varray");

// the above drop statements will throw exceptions
// if the types and tables did not exist before
// line 47

// ps.execute ("CREATE TYPE num_varray AS VARRAY(10) OF NUMBER(12, 2)");
// ps.execute ("CREATE TABLE varray_table (col1 num_varray)");
// ps.execute ("INSERT INTO varray_table VALUES (num_varray(100, 200))");

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("NUM_VARRAY", connection.getDBConnection());
ARRAY newArray = new ARRAY(desc, connection.getDBConnection(), Id);


ps.setDate(1, new java.sql.Date(startDate.getTime())); //pass the bind variable
ps.setDate(2, new java.sql.Date(endDate.getTime())); //pass the bind variable
ps.setString(3, xxx); //pass the bind variable
ps.setInt(4, xxx); //pass the bind variable
((OraclePreparedStatement)ps).setARRAY(5, newArray); //pass the bind variable
//ps.setInt(5, currentEmployeeId); //pass the bind variable

rs = ps.executeQuery();
crs.populate(rs);

ps.close();
rs.close();
connection.closeDBConnection();

do you have to pass an array? would passing a string not work? just trying to think of some easier solutions...before you have to start messing with native Oracle Datatypes