Java – Mapping an Oracle stored procedure result to a custom Java type (class)

javajdbcoraclestored-procedures

I have to call a stored procedure in Oracle (11g) that uses a single IN OUT parameter. This parameter is an Oracle custom type defined as 

 CREATE OR REPLACE TYPE "SEPADD"."T_NAPRAVI_NALOG_TEST" IS OBJECT
 (
    I_INICIJALI              varchar2(3)  ,          
    I_STATUS                 number(1)        
 )

The actual type is more complex but I simplified it here for better readability. The Oracle procedure using this type (again simplified ) is defined as

  CREATE OR REPLACE PROCEDURE "SEPADD"."GETNALOGTESTPROC"(nalog in out T_NAPRAVI_NALOG_TEST )
IS   
      BEGIN       
  nalog.I_INICIJALI := 'PC';         
      nalog.I_STATUS := nalog.I_STATUS + 3; 
END;

The Oracle custom type is mapped to a Java class that implements the SQLData interface. (see https://docs.oracle.com/javase/tutorial/jdbc/basics/sqlcustommapping.html#implementing_sqldata)

 public class TNapraviNalog implements SQLData{

private int I_STATUS;
private String I_INICIJALI;
private String sql_type = "T_NAPRAVI_NALOG_TEST";
public String getSQLTypeName() {
        return sql_type;
    }
    public int getIStatus(){
     return this.I_STATUS;
}
    public String getIInicijali(){
        return this.I_INICIJALI;
    }
    public void setIInicijali(String in){
        I_INICIJALI = in;
    }
    public void setIStatus(int st){
        I_STATUS = st;
    }
    public void readSQL(SQLInput stream, String type)
        throws SQLException {
        sql_type = type;
        I_INICIJALI = stream.readString();
        I_STATUS = stream.readInt();

    }

    public void writeSQL(SQLOutput stream)
        throws SQLException {
        stream.writeString(I_INICIJALI);
        stream.writeInt(I_STATUS);          
    }
}

Now, from my JDBC code I call the Oracle stored procedure in the following way

    Object obj=null;
    ResultSet rs=null;
    CallableStatement stmt=null;
    TNapraviNalog n = null;
    try{

          sqlQuery = "{call getnalogtestproc(?)}";

          Map m = conn.getTypeMap();
          m.put("sepadd.T_NAPRAVI_NALOG_TEST", Class.forName("ib.easyorm.db.TNapraviNalog"));//this maps the Java class to the Oracle custom type
          conn.setTypeMap(m);

          stmt=conn.prepareCall(sqlQuery);
          stmt.registerOutParameter(1, Types.STRUCT, "T_NAPRAVI_NALOG_TEST");
          stmt.setObject(1, paramValues.get(0) ); //paramValues.get(0) returns an instance of TNapraviNalog class

          stmt.execute();    

          obj = stmt.getObject(1, m);

          //obj = stmt.getObject(1,TNapraviNalog.class); this method is not implemented in the JDBC driver

    }catch(Exception e){
          throw new EasyORMException(e);
    }finally{
          closeResources(rs,stmt);
    }
    return obj;

Now, the problem is that, while I can get the result returned by the stored procedure, the result is not converted to a Java class (TNapraviNalog) so I have to do that manually. I can successfully
call the Oracle procedure with an instance of TNapraviNalog ( stmt.setObject(1, paramValues.get(0) ); ) but I can't get the result converted to TNapraviNalog. I really would like
to be able to have something like

 TNapraviNalog nalog = stmt.getObject(1, m);

However, this line will cause an exception ( java.lang.ClassCastException: oracle.sql.STRUCT cannot be cast to ib.easyorm.db.TNapraviNalog).
I'm guessing that the JDBC driver isn't aware of the actual type returned by the stmt.getObject(1,m) and thus can't make the conversion.

Does anybody know if this can be done using either plain JDBC or Hibernate?

EDIT: The relevant code from an Oracle page (link given in cihan seven's answer)

Retrieving SQLData Objects from a Callable Statement OUT Parameter

Consider you have an OracleCallableStatement instance, ocs, that calls a PL/SQL function GETEMPLOYEE. The program passes an employee number to the function. The function returns the corresponding Employee object. To retrieve this object you do the following:

1.Prepare an OracleCallableStatement to call the GETEMPLOYEE function, as follows:

 OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{ ? = call GETEMPLOYEE(?) }");

2.Declare the empnumber as the input parameter to GETEMPLOYEE. Register the SQLData object as the OUT parameter, with the type code OracleTypes.STRUCT. Then, run the statement. This can be done as follows:

 ocs.setInt(2, empnumber); 
 ocs.registerOutParameter(1, OracleTypes.STRUCT, "EMP_OBJECT"); 
ocs.execute();

3.Use the getObject method to retrieve the employee object. The following code assumes that there is a type map entry to map the Oracle object to the Java type Employee:

 Employee emp = (Employee)ocs.getObject(1); //my comment-->this doesn't seem to work

If there is no type map entry, then getObject would return an oracle.sql.STRUCT object. Cast the output to the STRUCT type, because the getObject method returns an instance of the generic java.lang.Object class. This is done as follows:

STRUCT emp = (STRUCT)ocs.getObject(1);

Thank you

Best Answer

The error appears to be in this line:

      m.put("sepadd.T_NAPRAVI_NALOG_TEST", Class.forName("ib.easyorm.db.TNapraviNalog"));//this maps the Java class to the Oracle custom type

This is the only line in which you are qualifying your T_NAPRAVI_NALOG_TEST type with the schema owner. You refer to it in two other places without the schema name.

If you're connecting to your database as the SEPADD user (it seems you are), you can remove the schema owner prefix sepadd. from this line. Alternatively, try changing the schema owner in the above line to upper-case.

Related Topic