0

I have a list of POJO's that I want to pass to an Oracle Stored Procedure and then loop through that list in the stored proc and run update statements

I've tried using a StructDescriptor but I keep getting an exception due to my connection object 

(java.lang.ClassCastException: org.jboss.jca.adapters.jdbc.jdk6.WrappedConnectionJDK6 cannot be cast to oracle.jdbc.OracleConnection)

public void MyMethod(List<myObject> myObjectList) {

private Connection con = null;
private CallableStatement stmt = null;

try {

    con = getConnection();  

    String query = "{call "+getStoredProcedureName()+"(?)}";
    stmt.setArray(1, myObjectList);
    stmt = con.prepareCall(query);

     stmt.execute();

} catch(Exception e) {

     throw e;
}
}

In Oracle

create or replace TYPE "MY_REC" AS OBJECT
(
   field_one varchar2(50),
   field_two varchar2(100)
);

create or replace TYPE "MY_REC_T" AS TABLE OF MY_REC;

I expect myObjectList to be passed to my stored procedure

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
LiterACola
  • 11
  • 1
  • Can you show us what `getConnection` does? I can find no answers which address passing an array of Java objects to an Oracle stored procedure across a JBoss connection. It appears that you're going to have to use something compatible with `oracle.jdbc.OracleConnection`. – Bob Jarvis - Слава Україні Jul 24 '19 at 22:54
  • Possible duplicate of https://stackoverflow.com/q/10247702/1509264 – MT0 Jul 25 '19 at 08:47

1 Answers1

0

Working with other connection wrappers (not jboss), I have had to unwrap the connection to get the underlying OracleConnection:

Connection connection = getConnection();
if ( !connection.isWrapperFor( OracleConnection.class ) )
{
  // throw exception
}
OracleConnection oConnection = (OracleConnection) connection.unwrap( OracleConnection.class );

The JBoss documentation and this answer also suggests that you could use:

WrappedConnection wrapped = (WrappedConnection) getConnection();
OracleConnection oConnection = (OracleConnection) wrapped.getUnderlyingConnection();

After that, there are multiple other answers on how to pass a Java array to Oracle SQL [1] [2] [3]

MT0
  • 143,790
  • 11
  • 59
  • 117