How to pass java list of objects to oracle stored procedure (Cannot use Spring)

Question

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

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 - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 24, 2019 at 22:54

MT0 · Accepted Answer · 2019-07-25 08:53:12Z

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]

edited Jul 25, 2019 at 8:53

answered Jul 25, 2019 at 8:39

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pass java list of objects to oracle stored procedure (Cannot use Spring)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related