Setting an Oracle varray in CallableStatement

[sedj]

Hi,

I have an Oracle proc which has an in type of varray, does anybody know what the Java mapping type is to this - ie how to use one of CallableStatement's setXXX methods for a varray (and what the object is that you would set) ??

Cheers

Ben

 

[sdm786110]

Hi Ben

I have the exact same question. Did you get the answer? Please share with me if you have.

Thanks a lot.
-Saleem

 

[JVZ]

Not to sure if this is what you are looking for or not...Hope it help though:

The following was taken from:

http://download-east.oracle.com/docs/cd/B10500_01/java.920/a96654.pdf

The oracle.sql.ARRAY class supports Oracle collections—either VARRAYs or
nested tables. If you select either a VARRAY or nested table from the database, then
the JDBC driver materializes it as an object of the ARRAY class; the structure of the
data is equivalent in either case. The oracle.sql.ARRAY class extends
oracle.sql.Datum and implements the standard JDBC 2.0 java.sql.Array
interface (oracle.jdbc2.Array under JDK 1.1.x).
You can use the setARRAY() method of the OraclePreparedStatement or
OracleCallableStatement class to pass an array as an input parameter to a
prepared statement. Similarly, you might want to manually create an ARRAY object
to pass it to a prepared statement or callable statement, perhaps to insert into the
database. This involves the use of ArrayDescriptor objects.
For more information about working with Oracle collections using the
oracle.sql.ARRAY and ArrayDescriptor classes, see "Overview of Collection
(Array) Functionality" on page 11-5.

Passing Arrays to Statement Objects
This section discusses how to pass arrays to prepared statement objects or callable
statement objects.
Passing an Array to a Prepared Statement
Pass an array to a prepared statement as follows (use similar steps to pass an array
to a callable statement). Note that you can use arrays as either IN or OUT bind
variables.


1. Construct an ArrayDescriptor object for the SQL type that the array will
contain (unless one has already been created for this SQL type). See "Steps in
Creating ArrayDescriptor and ARRAY Objects" on page 11-11 for information
about creating ArrayDescriptor objects.
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor
(sql_type_name, connection);
Where sql_type_name is a Java string specifying the user-defined SQL type
name of the array, and connection is your Connection object. See "Oracle
Extensions for Collections (Arrays)" on page 11-2 for information about SQL
typenames.


2. Define the array that you want to pass to the prepared statement as an
oracle.sql.ARRAY object.
ARRAY array = new ARRAY(descriptor, connection, elements);
Where descriptor is the ArrayDescriptor object previously constructed
and elements is a java.lang.Object containing a Java array of the
elements.


3. Create a java.sql.PreparedStatement object containing the SQL
statement to execute.


4. Cast your prepared statement to an OraclePreparedStatement and use the
setARRAY() method of the OraclePreparedStatement object to pass the
array to the prepared statement.
(OraclePreparedStatement)stmt.setARRAY(parameterIndex, array);
Where parameterIndex is the parameter index, and array is the
oracle.sql.ARRAY object you constructed previously.


5. Execute the prepared statement.