How can I call JDBC a PL / SQL function that returns UDO and interprets this result?

Question

Say my UDO (custom object):

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 I want to use 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 raises a different exception depending on the type of execute method used by java.sql.Type.

Is anyone I was looking for Oracle docs, but I find this very confusing.

Answer 1

So the solution

 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. The same goes for collections (I tested only VARRAY_OF_NUM though), you need to use registerOutParameter with three parameters, otherwise you will see some exceptions, such as "ORA-03115: unsupported network data type or view". Obviously, if your PL / SQL function returns VARRAY using statement.registerOutParameter(1, OracleTypes.ARRAY, "YOURE_VARRAY_TYPE"); .

Answer 2

How to make my function a table function? This will allow you to:

 select CREATE_A_PERSON('Daniel') from dual; 

and get a table consisting of two columns (AGE, NAME) with the data of the current user.