CODEWITHSUNDEEP
javasqloracle-databaseplsql
harindya
harindya

Reputation: 93

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

Upvotes: 3

Views: 12715

Answers (4)

Jose Boretto Blengino
Jose Boretto Blengino

Reputation: 886

You have an Oracle type is RECORD:

TYPE my_type IS RECORD (
    foo      VARCHAR2 (12)
);

In oracle your function return a PIPELINE:

FUNCTION my_funtion (
    foo   VARCHAR2,
    bar   VARCHAR2
)
RETURN my_type PIPELINED

In java, you can use the table funtion, and then Retrieve values from the ResultSet

select * from table (URGP.PKG_AG_SIR_MEW.RECUPERARPERSONAPORNRODOC(?,?))

Upvotes: 1

zzpzaf
zzpzaf

Reputation: 158

Here, I can't see any particular need to get back table data from a package function, using a CallableStatement object. Instead, we can use a normal sql query to wrap up the results and fetch them into a normal java resultset. Doing so, we avoid trying to find a painful solution adjusting the package function (which uses package-level types), leaving the function and package types intact, as well as continue benefiting from using the unknowing internal functionality and speed of the pipelined plsql function. Panos Zafiropoulos.

Upvotes: 0

harindya
harindya

Reputation: 93

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.

Upvotes: 2

Dmitry Nikiforov
Dmitry Nikiforov

Reputation: 3038

You case use oracle.sql.STRUCT class. Easiest example:

In Oracle:

create type type_dummy is object (
id int,
name varchar2(10)
)
/

create or replace function get_type_dummy
return type_dummy
is
begin
  return type_dummy(1,'ABCDe');
end;
/

In Java:

class TypeDummy {
   public Long id;
   public String name;
}



    try {
        DriverManager.registerDriver ( new oracle.jdbc.driver.OracleDriver());
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@ods.fors.ru:1521:test","odh","odh");
        OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall("{ ? = call get_type_dummy }"); ;
        cstmt.registerOutParameter(1, OracleTypes.JAVA_STRUCT, "TYPE_DUMMY");
        cstmt.execute();
        oracle.sql.STRUCT td = (oracle.sql.STRUCT)cstmt.getObject(1);
        Object[] x = td.getAttributes();
        TypeDummy ntd = new TypeDummy();
        ntd.id = ((BigDecimal)x[0]).longValue();
        ntd.name = (String)x[1];
        System.out.println(ntd.id);
        System.out.println(ntd.name);                        
        cstmt.close();        
    }
...

Output:

1
ABCDe

Upvotes: 4

Related Questions