harindya harindya - 6 months ago 20
SQL Question

Call PL/SQL function returning Oracle type from Java

I have a PL/SQL function as follows which returns an Oracle type (PROCESSEXCLEFILEARGS)

FUNCTION PROCESS_FILE_INTERNAL
(
i_Filename VARCHAR2,
i_EventType NUMBER
)
RETURN PROCESSEXCELFILEARGS


I have to call this function from Java and my Java Method looks as follows

OracleCallableStatement cstmt = null;
try{
OracleDriver ora = new OracleDriver();
DriverManager.registerDriver(ora);

Connection connection = ora.defaultConnection();
String call = "{ ? = call NEUTRINO_META.PKG_EXCEL.PROCESS_FILE_INTERNAL(?, ?) }";
cstmt = (OracleCallableStatement)connection.prepareCall(call);
cstmt.setQueryTimeout(1800);
cstmt.registerOutParameter(1, OracleTypes.OTHER, "NEUTRINO_META.PROCESSEXCELFILEARGS");
cstmt.setString(2, filename);
cstmt.setDouble(3, eventType);
cstmt.execute();

OracleObjects.ProcessExcelFileArgsobj = (OracleObjects.ProcessExcelFileArgs)cstmt.getObject(1);
connection.commit();


}
catch (SQLException e){
WriteEventToDb(e.getMessage());
}
finally{
if (cstmt != null){
cstmt.close();
}
}


OracleObject.ProcessExcelFileArgs is implementing SQLData, and the readSQl(..) and writeSQL(..) method are implemented properly to read and write the types fields.

But when i run this java method I get a SQLException with message 'Invalid column type: 1111'

Can anyone let me know if there is anything wrong in the approach I took, or if there is any other way to retrieve the return oracle type as a java object.

EDIT:

create or replace
TYPE PROCESSEXCELFILEARGS FORCE AS OBJECT
(
FullFilePath VARCHAR2(700),
Filename VARCHAR2(200),
Graph TYPEGRAPHDATA
)


please not that TYPEGRAPHDATA is another user defined Oracle type at schema level

thanks

Answer

ok I managed to get the returned oracle type as a java object by using the following code.

try{
Map rtn = connection.getTypeMap();
            rtn.put("NEUTRINO_META.PROCESSEXCELFILEARGS", Class.forName("OracleObjects.ProcessExcelFileArgs"));
            String call = "{ ? = call NEUTRINO_META.PKG_EXCEL.PROCESS_FILE_INTERNAL(?, ?) }";
                          cstmt = (OracleCallableStatement)connection.prepareCall(call);
                          cstmt.setQueryTimeout(1800);
                          cstmt.registerOutParameter(1, OracleTypes.STRUCT, "NEUTRINO_META.PROCESSEXCELFILEARGS");
                          cstmt.setString(2, filename);
                          cstmt.setDouble(3, eventType);                          
                          cstmt.execute();

                          ProcessExcelFileArgs args = (ProcessExcelFileArgs)cstmt.getObject(1, rtn);

        }
        catch (SQLException e){
            WriteEventToDb(e.getMessage());
        }        
        finally{
            if (cstmt != null){
                cstmt.close();
            }
        }

This worked by my ProcessExcelFileArgs class having implemented java.sql.SQLData, and by adding to Oracletype to java class mapping to the connection type map.