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 .
Best Answer
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
7) Make the database call
Enjoy... :)