How to map Oracle object in JDBC

Question

I have such oracle object:

CREATE OR REPLACE type employee_obj
 AS
 object (
 id NUMBER(10) ,
 ...
 )

stored procedure

function get_employee_obj () return employee_obj is
 l_employee employee_obj;
 begin
 ...
 return l_employee;
 end;

and I need to call it from java code:

 final String QUERY = "begin ? := GET_EMPLOYEE_OBJ(); end;";
 Connection connection = getConnection();
 CallableStatement stmt = connection.prepareCall(QUERY);
 stmt.registerOutParameter(1, <WHAT TO PUT HERE>);
 stmt.execute();
 ResultSet rs = (ResultSet) stmt.getObject(1); 
 ...

What sql or oracle type I need to specify as parameter for registerOutParameter to read object from stored function? I tried several, but allways got PLS-00382: expression is of wrong type error. Thanks!

• Janek Bogucki: Maybe this will help in combination with "select get_employee_obj() from dual": oracle.com/technology/sample_code/tech/java/sqlj_jdbc/files/…
• Dave Costa: download.oracle.com/docs/cd/B19306_01/java.102/b14355/…

Answer 1

The correct type for Oracle object is java.sql.Types.STRUCT or oracle.jdbc.OracleTypes.STRUCT depending the level of support you need.

But the trick to get rid of the error PLS-00382: expression is of wrong type is to specify the schema type name. It should be spelled in all upper cases, otherwise you will get invalid name pattern exception.

Given your example, something like that might be appropriate:

stmt.registerOutParameter(1, OracleTypes.STRUCT, "EMPLOYEE_OBJ");

or

stmt.registerOutParameter(1, Types.STRUCT, "EMPLOYEE_OBJ");

After having execute'd the query, you gain access to the result as:

STRUCT result = (oracle.sql.STRUCT)stmt.getObject(1);
Object[] attr = result.getAttributes();
// attr[0] if the first field
// attr[1] the second
// and so on

• PainIsAMaster: Wow, thank you so much Svlvain. I had the "type cannot be null" exception and the "illegal arguments error" for the past 2 days trying to call a Oracle Stored Procedure from my Spring boot application and I tried the NamedStoredProceduredQuery, Query, annotations and then finally just using the SimpleJdbcCall and passing the typeName of STRUCT as the 3rd argument of registering the SqlOutParameter was the key! Dude thank you so much man! I can see the data now too!