Passing multiple values to a stored procedure

[Nischal Tanna]

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]

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...

[Paul Campbell]

From AskTom (Tom Kyte the Oracle VP)

http://download-east.oracle.com/docs/cd/A81042_01/DOC/java.816/a81354/oraarr3.htm#1056648
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 java.io.*;
5 import java.sql.*;
6 import oracle.sql.*;
7 import oracle.jdbc.driver.*;
8
9 public class ArrayDemo
10 {
11 public static void passArray() throws SQLException
12 {
13 Connection conn =
14 new OracleDriver().defaultConnection();
15
16 int intArray[] = { 1,2,3,4,5,6 };
17
18 ArrayDescriptor descriptor =
19 ArrayDescriptor.createDescriptor( "NUM_ARRAY", conn );
20
21 ARRAY array_to_pass =
22 new ARRAY( descriptor, conn, intArray );
23
24 OraclePreparedStatement ps =
25 (OraclePreparedStatement)conn.prepareStatement
26 ( "begin give_me_an_array(:x); end;" );
27
28 ps.setARRAY( 1, array_to_pass );
29
30 ps.execute();
31
32 }
33
34 }
35 /

Java created.

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

Type created.

ops$tkyte@DEV816>
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>
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>
ops$tkyte@DEV816> set serveroutput on
ops$tkyte@DEV816> exec show_java_calling_plsql
1
2
3
4
5
6

PL/SQL procedure successfully completed.