Returning Oracle PLSQL Array to Java code ( incorrect Array type code)

[anuptosh]

Hi,

I have been trying to run the below example to get a Oracle Array as an output from a Java code. This is an example I have found on the web. But, the expected result is that the code should return me Array element type code 1, but it is returning me type code 12 and the array in a junk or unreadable format . Our environment is JDK 1.4, ojdbc14.jar and Oracle 8i

Not able to find out what they problem is and am not able to change the type code so that it returns the data in correct format. I suspect it is either the problem with either the JDBC library (ojdbc14.jar ) or the oracle 8i installation/options. We have also tested the above code with a 8i, jdk1.2, classes12.zip environment, but it still returns code 12.

Can you please guide as to what are the areas to be looked and how do we resolve for returning type code 1.

---- Oracle PLSQL function to return an array ----

** Type declaration **

CREATE OR REPLACE TYPE SimpleArray AS TABLE OF VARCHAR2(30)
/


** Oracle PLSQL package and function **

CREATE OR REPLACE PACKAGE BODY Test_Pkg_Fp IS

FUNCTION getSimpleArray RETURN SIMPLEARRAY
AS
l_data SIMPLEARRAY := SIMPLEARRAY();
BEGIN
FOR i IN 1 .. 10 LOOP
l_data.EXTEND;
l_data(l_data.COUNT) := 'entry ' || i;
END LOOP;
RETURN l_data;
END;

END Test_Pkg_Fp;
/


----- Java Code calling the PLSQL function to return an array -----

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

class Array
{

public static void main(String args[]) throws Exception
{

DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@ccd ev3:1521:CAM2D3","hst","hst");

OracleCallableStatement stmt =
(OracleCallableStatement)conn.prepareCall( "begin ? := TEST_PKG_FP.getSimpleArray; end;" );

// The name we use below, SIMPLEARRAY, matches the name of our
// type we defined in SQL above
stmt.registerOutParameter( 1, OracleTypes.ARRAY, "SIMPLEARRAY" );
stmt.executeUpdate();

// Now we can get the array object back and print out some meta data about it
ARRAY simpleArray = stmt.getARRAY(1);

System.out.println ("Array is of type name " + simpleArray.getBaseTypeName());

System.out.println ("Array is of type " + simpleArray.getSQLTypeName());

System.out.println ("Array element is of type code " + simpleArray.getBaseType());

System.out.println ("Array is of length " + simpleArray.length());

// Lastly, we'll print out the entire contents of our array
String[] values = (String[])simpleArray.getArray();

for( int i = 0; i < values.length; i++ )
System.out.println( "row " + i + " = '" + values[i] +
"'" );

stmt.close();
conn.close();
}
}

--- Actual Result ----

Array is of type name VARCHAR
Array is of type HST.SIMPLEARRAY
Array element is of type code 12
Array is of length 10
row 0 = '???'
row 1 = '???'
row 2 = '???'
row 3 = '???'
row 4 = '???'
row 5 = '???'
row 6 = '???'
row 7 = '???'
row 8 = '???'
row 9 = '???'

------- Expected Result ---

Array is of type name CHAR
Array is of type HST.SIMPLEARRAY
Array element is of type code 1
Array is of length 10
row 0 = 'entry 0'
row 1 = 'entry 1'
row 2 = 'entry 2'
row 3 = 'entry 3'
row 4 = 'entry 4'
row 5 = 'entry 5'
row 6 = 'entry 6'
row 7 = 'entry 7'
row 8 = 'entry 8'
row 9 = 'entry 9'

Thanks and Regards,

Anup