Structured datatypes via ORACLE-JDBC

Hello,

I am trying to transfer structured datatypes (ANY structured datatype, i. e. tables, records, ...) from ORACLE to Java via JDBC.

Since there is no problem with simple datatypes (integer, String, ...), I tried the straight approach:

[ORACLE part]

CREATE OR REPLACE PACKAGE test_array_parameter AS

TYPE test_rec IS RECORD

( string1 VARCHAR2(10),

string2 VARCHAR2(10),

string3 VARCHAR3(10) );

FUNCTION TEST_RECORD_RETURN ( local_rec OUT test_rec ) RETURN INTEGER;

END test_array_parameter;

/

CREATE OR REPLACE PACKAGE BODY test_array_parameter AS

FUNCTION TEST_RECORD_RETURN ( local_rec OUT test_rec ) RETURN INTEGER IS

BEGIN local_rec.string1 := '123321';

local_rec.string2 := '3321AB';

local_rec.string3 := '21urzn';

• everything OK:

RETURN errors.SQL_OK;

• Exception Handling

EXCEPTION WHEN OTHERS THEN RETURN errors.SQL_UNSPECIFIED;

END TEST_RECORD_RETURN; END test_array_parameter;

/

[JAVA part]

... (the usual imports)

... (uses ORACLE connection caching)

// get connection

Connection verbindung = conPool.getConnection();

CallableStatement objStatement = null;

OracleResultSet objResultSet = null;

// prepare Stored Procedure call

objStatement=verbindung.prepareCall("begin ? := TEST_ARRAY_PARAMETER.TEST_RECORD_RETURN ( ? ); end;");

objStatement.registerOutParameter(1,OracleTypes.INTEGER);

objStatement.registerOutParameter(2,OracleTypes.ARRAY);

// execute

objStatement.execute();

Unfortunately, the Java code crashes in when registering the second OUT-Parameter (the ARRAY), throwing the following exception:

java.sql.SQLException: Conflicting parameter type: sqlType=2003

Provided I have all necessary rights etc., so how do I register the RECORD/ARRAY? What alternatives do I have? Does anyone know how to transfer a table as part of an OPEN CURSOR (REF CURSOR)?

ORACLE says they do not plan on supporting structured datatypes via JDBC (maybe I misunderstood that...), and they encourage the use of OPEN CURSORs (which don't really solve my problem). I already read

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOT&p_id=76424.1

but didn't understand the example. Can anyone help me?

Jochen Received on Thu Jan 25 2001 - 10:57:06 CET