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'
