Java – how to use JDBC / spring to call Oracle stored procedures, some of which parameter types are user-defined?
I'm trying to call the Oracle stored procedure from my Java program. I'm using JDBC and spring's Storedprocedure Some parameters are user - defined types and I need to know how to pass them
In particular, which type should I specify in the parameter mapping (that is, which one of Java. SQL. Types. *)? What Java type should I use? The problem types are defined as follows:
type MyDoubles as varray(50000) of double precision type MyStrings as varray(50000) of varchar2(2000)
Solution
The first hit in Google seems to show how to bind varray: http://www.devx.com/tips/Tip/22034 Parameter of type The example in this document uses prepared statements, but it should work the same for stored procedures
This is an excerpt showing the basic concepts:
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 ();
Here are some fqdns to clarify:
> oracle. sql. ArrayDescriptor> oracle. sql. ARRAY> oracle. jdbc. OraclePreparedStatement