Hello!

I am trying to retrieve Oracle STRUCT/ARRAYs from a java OracleResultSet returned from a PL/SQL stored procedure in the Oracle 9.2 database. I am using WebSphere Studio Application Developer 5.1.2 with the JDBC driver ojdbc14.zip. We are building a web-application running in WebSphere 5.1

The problem is this: When I run my method in a standard java console application, outside WAS with a "direct" databaseconnection everything works fine. I can list out the content in the objects:



OUTPUT:
id = 25
pool id = 4
changed by = Me
Pool Mappings[0] = network: good network
Pool Mappings[1] = subnet: cx?435
Pool Mappings[2] = geography: Ulan Bator


BUT, when i try to run this inside WAS, I run into problems. The attributes in my Oracle STRUCT object has been transformed from meaningful strings to this: "???". What gives ? I get a underlying native Oracleconnection from the wrapperconnection in WSAD, I cast the java.sqlResultSet to oracle.jdbc.OracleResultSet, and have done what I can to get the two methods to behave the same way. Here are the output when I run inside WAS:

OUTPUT:
id = 25
[11/11/04 18:24:42:288 CET] 2c472c47 SystemOut O pool id = 4
[11/11/04 18:24:44:792 CET] 2c472c47 SystemOut O changed by = Me
[11/11/04 18:25:05:992 CET] 2c472c47 SystemOut O Pool Mappings[0] = ???: ???
[11/11/04 18:25:13:934 CET] 2c472c47 SystemOut O Pool Mappings[1] = ???: ???
[11/11/04 18:25:16:387 CET] 2c472c47 SystemOut O Pool Mappings[2] = ???: ???

Here are the method:

public static void getPoolMapping(Connection myConnection, int poolId) throws SQLException {
  try {
        CallableStatement cstmt = myConnection.prepareCall("{call PCK_IPAM.SP_SELECT_POOL_MAPPING(?,?)}");
cstmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); //recordset
cstmt.setInt(2, poolId); //pool_id

cstmt.execute();

System.out.println("Stored proc executed successfully!");
System.out.println("Retrieving the data ....");

// Hack ???
ResultSet res = (ResultSet) cstmt.getObject(1);

// step 2: while there are rows in the result set ...
while (res.next())
{

System.out.println("id = " + res.getInt("ID"));
System.out.println("pool id = " + res.getInt("POOL_ID"));
System.out.println("changed by = " + res.getString("CHANGED_BY"));

// a: retrieve the collection from the result set into an
// ARRAY using the getARRAY() method
ARRAY poolResMapARRAY = ((OracleResultSet) res).getARRAY("EXT_PARAM");

// b: retrieve the element values from the ARRAY using the
// getArray() method, storing the element values in an Object array
Object[] poolResMapping = (Object[]) poolResMapARRAY.getArray();

// c: read the element values from the Object array
for (int count = 0; count < poolResMapping.length; count++)
{
// ... and here we have to handle each extParam STRUCT to list them out

STRUCT extParam = (STRUCT) poolResMapping[count];
Object[] extParamAttrs = extParam.getAttributes();

System.out.println("Pool Mappings[" + count + "] = " + extParamAttrs[0] +": " + extParamAttrs[1]);
}

} // end of while loop

cstmt.close();
} catch (Exception ex) {
System.out.println("Uuups ...: "+ex.getMessage());
}
} // end of getPoolMapping()

Can anone tell me what's going on ????

-jh, NORWAY