How do I call by JDBC a PL/SQL function that returns an UDO and interpret that result?

Question

Say my UDO (user defined object) is:

create or replace
TYPE UDO_PERSON AS object (NAME VARCHAR2(100), AGE INTEGER);

And I have a PL/SQL function

create or replace
FUNCTION CREATE_A_PERSON(NAME VARCHAR2)
RETURN UDO_PERSON
AS  
    AGE INTEGER;
BEGIN

    SELECT dbms_random.value(1,100) INTO AGE FROM DUAL;
    RETURN NEW UDO_PERSON(NAME, AGE);

END CREATE_A_PERSON;

I tested the following method and it works, and there are ways to 'parse' the result

...

String select = "SELECT CREATE_A_PERSON('my name') FROM DUAL";
Statement stmt=conn.createStatement();
ResultSet rs= stmt.executeQuery(select);
rs.next();

java.sql.Struct jdbcStruct = (java.sql.Struct)rs.getObject(1);

Object[] attrs = jdbcStruct.getAttributes();
for(int i=0;i<attrs.length;i++){
    System.out.println("attrs["+i+"] "+attrs[i].toString());
}

... 

But what I want to use is a CallableStatement like:

String procedure = "{? = call CREATE_A_PERSON (?)}";
CallableStatement statement = conn.prepareCall(procedure);

statement.registerOutParameter(1, java.sql.Types.STRUCT); // I tested with OTHER, JAVA_OBJECT 
statement.setString(2, "Youre name");

ResultSet rs= statement.executeQuery(); // tried also with execute() and then statement.getObject()...still nothing
java.sql.Struct jdbcStruct = (java.sql.Struct)rs.getObject(1);

...

So this last piece of code throws different kind of exception depending on the java.sql.Type that I use, the type of execute method.

Anyone? I searched the Oracle docs, but find it very confusing.

Answer 1

So the solution is

String procedure = "{? = call CREATE_A_PERSON (?)}";
CallableStatement statement = conn.prepareCall(procedure);
statement.registerOutParameter(1, java.sql.Types.STRUCT, "UDO_PERSON");
statement.setString(2, "YOURE NAME");
statement.execute();
...

Old line:

statement.registerOutParameter(1, java.sql.Types.STRUCT);

New line:

statement.registerOutParameter(1, java.sql.Types.STRUCT, "UDO_PERSON");

from here you just 'parse' the java.sql.Struct object. Same goes with collections (I've tested only VARRAY_OF_NUM though) you need to use the registerOutParameter with three parameters, else you'll see some exceptions like "ORA-03115: unsupported network datatype or representation". Obviously if your PL/SQL function returns a VARRAY use statement.registerOutParameter(1, OracleTypes.ARRAY, "YOURE_VARRAY_TYPE");.