This content has been marked as final. Show 2 replies
JDBC (this is assumign you are talking about a JDBC call to this procedure) cannot work with PL/SQL types.
It only understands SQL types.
So you would have to create a SQL type instead.
>1 person found this helpful
I'm a PL/SQL developer. I'm a rookie to Java.
I have an Oracle procedure which has collection as input parameter. I want the array/collection to be passed from front end.
Is it possible to pass a collection from frontend to Oracle procedure?
Yes - but as already discussed in your SQL and PL/SQL thread yesterday (Re: Oracle to Java communication for the use case you described there to pass parameters that approach is NOT recommended.
And unfortunately this is still the wrong forum for the question. It really belongs in the JDBC (Database Connectivity) forum
You need to defined the types as SQL types and then use the ARRAY processing functionality of JDBC.
See Chapter 16 Working with Oracle Collections in the JDBC Developer's Guide for extensive documentation on using collections.
That doc doesn't have a code example but you can find them on the net. Here is a simple ARRAY example.
But, again if you are working with objects that are really table data it is far simpler and more efficient to send data from Java to a table and then let your procedure process the data from there.
And for sending record data from Oracle to Java the preferred method is to have the procedure create a REF CURSOR and then JAVA can fetch the data using the ref cursor.