How can I call an Oracle stored procedure with JDBC/Spring where some of the parameter type are user defined?

Question

I'm trying to call an Oracle stored procedure from my Java program. I'm using JDBC and Spring's StoredProcedure. A couple of the parameters are user defined types and I need to know how to pass them in.

In particular what type should I specify in the parameter map (i.e. which of java.sql.Types.*)? And what Java type should I use? The problem types are defined like this:

type MyDoubles as varray(50000) of double precision
type MyStrings as varray(50000) of varchar2(2000)

Answer 1

Why are users passing 50,000 instances of doubles and strings - so Oracle can perform a calculation?

This seems backwards to me. If users already have all that day, perhaps Java can perform that calculation. If you really want Oracle to do it, I'd say the data should already be resident within the database and not passed in.

Wouldn't both be java.sql.Type.ARRAY?

Answer 2

First hit in Google seems to show how to bind parameters of type VARRAY: http://www.devx.com/tips/Tip/22034. The examples in this document use a prepared statement, but for a stored procedure it should work just the same.

Here's an excerpt showing the basic concept:

String arrayElements[] = { "Test3", "Test4" };
PreparedStatement ps =
    conn.prepareStatement ("insert into sample_varray_table values (?)");

ArrayDescriptor desc = ArrayDescriptor.createDescriptor("STRING_VARRAY", conn);
ARRAY newArray = new ARRAY(desc, conn, arrayElements);
((OraclePreparedStatement)ps).setARRAY (1, newArray);

ps.execute ();

To clarify here are a few FQDNs:

• oracle.sql.ArrayDescriptor
• oracle.sql.ARRAY
• oracle.jdbc.OraclePreparedStatement