#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2003
    Location
    FLorida
    Posts
    45
    Rep Power
    11

    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();
  2. #2
  3. SwollenMember
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    the master control
    Posts
    264
    Rep Power
    14
    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

IMN logo majestic logo threadwatch logo seochat tools logo