Send & Retrieve array list to oracle stored procedure

Question

I'm trying to push "Arraylist" to oracle stored procedure and after making necessary modification, the object it is returned back.

I have an oracle stored procedure with an inout parameter which is "AS TABLE OF TYPE".

I'm able to make the call using Mybatis by implements TypeHandler and overrides its method public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException

But I'm facing issue while retrieving the object.

For retrieving I Override the below method.

public Object getResult(CallableStatement cs, int columnIndex) throws SQLException {

    ARRAY array_to_pass = ((OracleCallableStatement) cs).getARRAY(1);       

    /* showing content */
    Datum[] elements = array_to_pass.getOracleArray();

    for (int i = 0; i < elements.length; i++) {
        Object[] element = ((STRUCT) elements[i]).getAttributes();
        String value = (String) element[0];
        System.out.println("array(" + i + ").val=" + value);
    }

}

I'm getting the below error java.lang.IllegalAccessError: tried to access class oracle.jdbc.driver.OracleCallableStatement

Statement generating the error is

ARRAY array_to_pass = ((OracleCallableStatement) cs).getARRAY(1);


Any thoughts/help on how to retrieve table of type object from oracle .

Answer 1

I'm able to implement it successfully using Spring, Mybatis & Oracle. The reference code to implement is as below

1) Create Type in Database

CREATE OR REPLACE TYPE TypeName AS OBJECT(
Field1 varchar(6), Field2 varchar(6) )

2) Create Table of Type in Database

CREATE OR REPLACE TYPE TableTypeName AS TABLE OF TypeName;

3) Create transfer object in java with 2 fields

4) Generate necessary arraylist for transfer object

5) Create database call

CALL StoredProcedureName( #{Parameter_in, javaType=Object, jdbcType=ARRAY, jdbcTypeName=TableTypeName , mode=INOUT, typeHandler=javaHandlername} )

6) Create handler

public class javaHandlername implements TypeHandler {
    // Set data to oracle object
    @Override
    public void setParameter(PreparedStatement ps, int i, Object parameter,
        JdbcType jdbcType) throws SQLException 
    {
        List<TO> TOs = (List<TO>) parameter;

        StructDescriptor structDescriptor = StructDescriptor
        .createDescriptor("TypeName", ps.getConnection());
        STRUCT[] structs = null;
        structs = new STRUCT[TOs.size()];
        for (int index = 0; index < TOs.size(); index++) {
            TO to = TO.get(index);
            Object[] params = new Object[2];
            params[0] = fSVDisptchSchedDely.getField1();
            params[1] = fSVDisptchSchedDely.getField2();

            STRUCT struct = new STRUCT(structDescriptor,
            ps.getConnection(), params);
            structs[index] = struct;

        }

        ArrayDescriptor desc = ArrayDescriptor.createDescriptor(
        "TableTypeName", ps.getConnection());
        ARRAY oracleArray = new ARRAY(desc, ps.getConnection(), structs);
        ps.setArray(i, oracleArray);
    }

    // Set the result back to Java object
    public Object getResult(CallableStatement cs, int columnIndex)
    throws SQLException {

        List<TO> TOs= new ArrayList<TO>();
        Object[] structArray = (Object[]) cs.getArray(columnIndex).getArray();
        oracle.sql.STRUCT mystruct = null;
        for (Object structObj : structArray) {
            TO to= new TO();
            mystruct = (oracle.sql.STRUCT) structObj;
            Object[] structAttr = mystruct.getAttributes();
            to.setField1((String)structAttr[0]));
            to.setField2((String)structAttr[1]));            
            TOs.add(to);
        }
        return TOs;
    }
}

7) Make the database call

Enjoy... :)