Monday, 30 June 2008

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'

1 comment:

Pas Apicella said...

From the documentation it's clear the CAST is needed here in order to use the return data.


http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions024.htm#SQLRF06304