File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC 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
JavaRanch » Java Forums » Databases » JDBC
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: 800

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
Similar Threads
writing log to a file from java stored procedure
Can we call java program from a Oracle stored procedure?
Stored Procedures
Problem using Callable Statement
Can you invoke a Java class from a PL/SQL stored procedure?