File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Passing multiple values to a stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Head First Android this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Passing multiple values to a stored procedure" Watch "Passing multiple values to a stored procedure" New topic

Passing multiple values to a stored procedure

Nischal Tanna
Ranch Hand

Joined: Aug 19, 2003
Posts: 182
Hi All
I have a stored procedure : SP_test() in the oracle database. I have an ArrayList in java. i want to send this list as an input parameter to this stored procedure....Any ideas how can this be done on java and oracle side?
[ November 28, 2007: Message edited by: Nischal Tanna ]

Peter Rooke
Ranch Hand

Joined: Oct 21, 2004
Posts: 810

I do not think you can, [well in T-SQL anyway], so we tend to use work arounds:

1) create a temp table - populate it with values. Your procedure then just does a select from the temp table.
2) Use a comma seperated list...

In my old Informix days - we used to use the Temp table solution to pass values between different procedures.

Here are a few more ideas...

Regards Pete
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
From AskTom (Tom Kyte the Oracle VP)
Here is a quick and dirty example showing PLSQL calling Java calling PLSQL and passing an array from Java to PLSQL. The trick is to use a SQL table type -- not a PLSQL type (eg: create the type OUTSIDE of plsql -- that way Java can in fact bind to it). Java cannot bind to PLSQL table types (eg: like owa_util.ident_arr was -- that was a plsql table type -- not a SQL type).

ops$tkyte@DEV816> create or replace
2 and compile java source named "ArrayDemo"
3 as
4 import*;
5 import java.sql.*;
6 import oracle.sql.*;
7 import oracle.jdbc.driver.*;
9 public class ArrayDemo
10 {
11 public static void passArray() throws SQLException
12 {
13 Connection conn =
14 new OracleDriver().defaultConnection();
16 int intArray[] = { 1,2,3,4,5,6 };
18 ArrayDescriptor descriptor =
19 ArrayDescriptor.createDescriptor( "NUM_ARRAY", conn );
21 ARRAY array_to_pass =
22 new ARRAY( descriptor, conn, intArray );
24 OraclePreparedStatement ps =
25 (OraclePreparedStatement)conn.prepareStatement
26 ( "begin give_me_an_array(:x); end;" );
28 ps.setARRAY( 1, array_to_pass );
30 ps.execute();
32 }
34 }
35 /

Java created.

ops$tkyte@DEV816> create or replace type NUM_ARRAY as table of number;
2 /

Type created.

ops$tkyte@DEV816> create or replace
2 procedure give_me_an_array( p_array in num_array )
3 as
4 begin
5 for i in 1 .. p_array.count
6 loop
7 dbms_output.put_line( p_array(i) );
8 end loop;
9 end;
10 /

Procedure created.

ops$tkyte@DEV816> create or replace
2 procedure show_java_calling_plsql
3 as language java
4 name 'ArrayDemo.passArray()';
5 /

Procedure created.

ops$tkyte@DEV816> set serveroutput on
ops$tkyte@DEV816> exec show_java_calling_plsql

PL/SQL procedure successfully completed.
I agree. Here's the link:
subject: Passing multiple values to a stored procedure
It's not a secret anymore!