ORA-00932 Using the Collect Function with Oracle JDBC driver

Recently I tried to use the COLLECT function from 10g as follows from SQL*Plus which worked fine.

SQL> select COLLECT(ename) as emps from emp;

EMPS
--------------------------------------------------------------------------------
SYSTPUJ7P3Tw/Wq/gQLsKRlBJ8Q==('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAK
E', 'CLARK', 'SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')

I then decided to use the same query in JDBC and got this error when trying to execute a query from a Statement object.

java.sql.SQLException: ORA-00932: inconsistent datatypes: expected NUMBER got -

It's obvious the return type needs to be something which the JDBC client can actually work with , so to get this to work I simply did this.

1. Create a TYPE to hold my array of Employee names.

CREATE OR REPLACE TYPE varchar2_enames AS TABLE OF VARCHAR2(50);

I then quickly ran a new query to ensure my TYPE was working correctly as shown below.

SQL> select CAST(COLLECT(ename) AS varchar2_enames) AS emps from emp;

EMPS
--------------------------------------------------------------------------------
VARCHAR2_ENAMES('SMITH', 'ALLEN', 'WARD', 'JONES', 'MARTIN', 'BLAKE', 'CLARK', '
SCOTT', 'KING', 'TURNER', 'ADAMS', 'JAMES', 'FORD', 'MILLER')

Note: You will se this time the return output is a table of varchar's which we can easily map to a JDBC array which we can then obtain without any errors.

2. JDBC code as follows, using the 10.2.0.4 JDBC driver

package pas.au;

import java.sql.*;
import oracle.jdbc.OracleDriver;

public class Test
{
  public Test()
  {
  }

  public void run ()
  {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
   
    String queryStr = "select CAST(COLLECT(ename) AS varchar2_enames) AS emps from emp";
 
    try
    {
      conn = getConnection();
      driverDeatils(conn);
      stmt = conn.createStatement();
      rset = stmt.executeQuery(queryStr);
      // only expecting one row
      rset.next();

      Array arr = rset.getArray(1);
      String[] values = (String[])arr.getArray();
      for( int i = 0; i < values.length; i++ )
        System.out.println( "row " + i + " = '" + values[i] +"'" );

    }
    catch (SQLException e)
    {
      e.printStackTrace();
    }
    finally
    {
      try
      {
        if (conn != null)
          conn.close();
       
        if (stmt != null)
          stmt.close();
         
        if (rset != null)
          rset.close();
      }
      catch (Exception e)
      {
        // bad luck
      }
    }
   
  }
 
  public void driverDeatils (Connection conn) throws SQLException
  {
    DatabaseMetaData dmd = conn.getMetaData();
    System.out.println("DriverVersion:      ["+
                         dmd.getDriverVersion()+"]");
    System.out.println("DriverMajorVersion: ["+
                         dmd.getDriverMajorVersion()+"]");
    System.out.println("DriverMinorVersion: ["+
                         dmd.getDriverMinorVersion()+"]");
    System.out.println("DriverName:         ["+
                         dmd.getDriverName()+"]");
  }
 
  public static Connection getConnection() throws SQLException
  {
    String username = "scott";
    String password = "tiger";
    String thinConn = "jdbc:oracle:thin:@beast:1522:linux10g";
    DriverManager.registerDriver(new OracleDriver());
    Connection conn = DriverManager.getConnection(thinConn,username,password);
    conn.setAutoCommit(false);
    return conn;
  }
 
  public static void main(String[] args)
  {
    Test test = new Test();
    test.run();
  }
}

3. Output as follows.

DriverVersion: [10.2.0.4.0]
DriverMajorVersion: [10]
DriverMinorVersion: [2]
DriverName: [Oracle JDBC driver]
row 0 = 'SMITH'
row 1 = 'ALLEN'
row 2 = 'WARD'
row 3 = 'JONES'
row 4 = 'MARTIN'
row 5 = 'BLAKE'
row 6 = 'CLARK'
row 7 = 'SCOTT'
row 8 = 'KING'
row 9 = 'TURNER'
row 10 = 'ADAMS'
row 11 = 'JAMES'
row 12 = 'FORD'
row 13 = 'MILLER'