Hi Larry,
It would be something like this. Actually it is not straight forward, you have
to do some work at
the oracle too. By the way this is database specific. I got this from
asktom.oracle.com
--------------------------------------------------------------------------------------------
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).
[EMAIL PROTECTED]> 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.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> create or replace type NUM_ARRAY as table of number;
2 /
Type created.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> 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.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> create or replace
2 procedure show_java_calling_plsql
3 as language java
4 name 'ArrayDemo.passArray()';
5 /
Procedure created.
[EMAIL PROTECTED]>
[EMAIL PROTECTED]> set serveroutput on
[EMAIL PROTECTED]> exec show_java_calling_plsql
1
2
3
4
5
6
PL/SQL procedure successfully completed.
Rgds
Prashanth Sukumaran.
--- Larry Meadors <[EMAIL PROTECTED]> wrote:
> How would you do it with JDBC?
>
> Larry
>
>
> On 8/11/05, Rao, Satish <[EMAIL PROTECTED]> wrote:
> >
> >
> > Has anyone had success with passing a java.util.List as IN parameter to
> > stored procedure?
> > If yes, what would the jdbcType in the sqlmap and what would be the
> > corresponding declaration in stored procedure?
> >
> > If this cannot be accomplished with a List, are there other options?
> >
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
