ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Prashanth Sukumaran <prashanthsukuma...@yahoo.com>
Subject Re: Passing a list as IN parameter to stored procedure
Date Fri, 12 Aug 2005 12:38:25 GMT
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).


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.




Rgds

Prashanth Sukumaran.




--- Larry Meadors <larry.meadors@gmail.com> wrote:

> How would you do it with JDBC?
> 
> Larry
> 
> 
> On 8/11/05, Rao, Satish <satish.rao@fmr.com> 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 

Mime
View raw message