Passing ArrayList to Oracle Stored Procedure in Java

Question

Is there a way to pass a Java ArrayList object as a parameter to an Oracle Stored Procedure? I have seen examples of passing an Array object to an Oracle Stored Procedure, but not an ArrayList object. Is this possible?

Answer 1

No. If the Oracle stored procedure is in PL/SQL you must convert your ArrayList (or any List implementation) into an array.

If your stored procedure is written in Java, you could serialize your ArrayList, send the byte stream to Oracle as a long string, and reconstitute it on the Oracle side. I did this about 10 years ago for a client and it worked very well. There are limits on how long a string you can pass across the Java-Oracle interface so if your data structure is large you will have to divide it into chunks that will fit into a single parameter, and have the Java on the Oracle side accept multiple long string parameters.