Geek Answers Handbook

How can I call an Oracle stored procedure using JDBC / Spring where certain types of parameters are defined by the user?

I am trying to call an Oracle stored procedure from my Java program. I am using JDBC and Spring StoredProcedure. A pair of parameters are user-defined types, and I need to know how to pass them.

In particular, what type should I indicate in the parameter map (i.e. which of java.sql.Types.* )? And what type of Java should I use? The types of problems are defined as follows:

 type MyDoubles as varray(50000) of double precision type MyStrings as varray(50000) of varchar2(2000)
+4
source share
3 answers

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

Here is 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 a few FQDNs here:

  • oracle.sql.ArrayDescriptor
  • oracle.sql.ARRAY
  • oracle.jdbc.OraclePreparedStatement
+1
source

Why do users pass 50,000 instances of doubles and rows - so that Oracle can do the calculation?

It seems to me the opposite. If users already have this whole day, maybe Java can do this calculation. If you really want Oracle to do this, I would say that the data should already be resident in the database and not transferred.

Won't both java.sql.Type.ARRAY?

0
source

You can really use objects from the Oracle JDBC driver, as suggested by Philip. Most users complete the creation of utility methods for transferring this logic. Or they use Spring mapping classes. However, there is a lot of manual work.

Another approach is to use the upcoming version 1.5.4 of jOOQ - the open source library that I am developing - where arrays are supported evenly. Therefore, when you have your types:

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

Then jOOQ will generate classes like

 public class MyDoubles extends ArrayRecordImpl<Double> { /* ... */ } public class MyStrings extends ArrayRecordImpl<String> { /* ... */ }

Your stored procedures might look like this:

 PROCEDURE MY_PROC1 (d IN MyDoubles, s IN MyStrings); PROCEDURE MY_PROC2 (d IN MyDoubles, s OUT MyStrings); PROCEDURE MY_PROC3 (d OUT MyDoubles, s OUT MyStrings);

And jOOQ will create another Java class like

 public class Procedures { // Invoke procedure MY_PROC on JDBC Connection c with VARRAY arguments public static void myProc1(Connection c, MyDoubles d, MyStrings s); // The OUT parameter is mapped to a method return value public static MyStrings myProc2(Connection c, MyDoubles d); // MyProc3 is a wrapper for both OUT parameters public static MyProc3 myProc3(Connection c); }

Generated code artifacts that invoke stored procedures with UDT, VARRAY parameters are as simple as possible. When generating the source code, you can change objects (for example, your types or your procedures) in your database schema, and your Java classes immediately reflect this change.

Read more at http://www.jooq.org/manual/META/PROCEDURE/

0
source

More articles:


All Articles