How to map an Oracle object in JDBC

I have an oracle object like this:

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 type of sql or oracle do I need to specify as a registerOutParameter parameter to read an object from a stored function? I tried several, but always was PLS-00382: expression of the wrong type error. Thank!

+3
source share
1 answer

The correct type for the Oracle object is, java.sql.Types.STRUCTor oracle.jdbc.OracleTypes.STRUCTdepending on the level of support you need.

, PLS-00382: expression is of wrong type, - . , invalid name pattern.

, , - :

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

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

execute 'd :

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
+3

Source: https://habr.com/ru/post/1758355/


All Articles