Search This Blog

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'

Wednesday 25 June 2008

Fast Connection Failover (FCF) Within OAS 10.1.3.x

If you read the 10.2 JDBC developer guide you will find that you will find you can subscribe remotely to ONS within the driver itself without the need for an oracle client.

http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/fstconfo.htm#CHDCHAGH
Remote ONS Subscription

OAS 10.1.3.x ships with the 10.1.0.5 JDBC driver, so is it right to assume it doesn't support FCF out of the box?

After a recent customer visit they swapped out to the 10.2.0.4 JDBC driver as they assumed 10.1.0.5 was not supported and would not work.

That is not true in actual fact 10.1.0.5 does work and the ONS subscription occurs within opmn.xml. However if you try to use the 10.1.0.5 driver outside of OAS in a stand alone client then it won't work as you can't subscribe to ONS with that driver outside of OAS 10.1.3.x.

It is all explained here.

http://www.oracle.com/technology/products/ias/hi_av/OracleApplicationServer10gFCF.pdf