How to pass a two dimensional Object Array To a Stored Procedure

[Parthiban Sreenivasan]

Hai All
I have a requirment to Pass a two dimensional object array from java to a oracle Stored procedure/Function. I am not able to handle this in the java side and also if you have some special datatype in oracle to then pls do help me that too
Pls do mail me if you have suggestions or example codes or any information on this .
Email [e-mail]

[SJ Adnams]

Here is the pl/sql:

create or replace type TESTARRAY as VARRAY(20) of varchar2(30);
create or replace package types
as 
type chararray is table of varchar2(10);
end types;
/
create or replace package test
as 
procedure test
(
  arg1 in TESTARRAY,
  arg2 out TESTARRAY
);
end test;
/
create or replace package body test
as
procedure test
(
  arg1 in TESTARRAY,
  arg2 out TESTARRAY
)
is
begin
  arg2 := TESTARRAY('a','b','c',' ',' ',' ');
  for i in 1 .. arg1.count
  loop
    arg2(i + 3) := arg1(i);
  end loop;
  
end;
end test;
/

Here is the java:

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.oracore.Util;
import oracle.jdbc.driver.*;
import oracle.sql.ARRAY;
import java.math.BigDecimal;
public class test
{
 public static ARRAY send()  throws Exception
 {
  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
  Connection conn = DriverManager.getConnection("jdbc   :-o	   racle:thin:@youripaddress:yourSID","user","pass");
  String s[] = {"hello","world","array"};
  ArrayDescriptor desc = ArrayDescriptor.createDescriptor("TESTARRAY", conn);
  
  ARRAY newArray = new ARRAY(desc, conn, s);
  CallableStatement cstmt = conn.prepareCall("{ call test.test (?,?) }");
  cstmt.setArray(1, newArray);
  cstmt.registerOutParameter(2, Types.ARRAY, "TESTARRAY" );
  cstmt.execute();
  newArray = (ARRAY) cstmt.getObject(2);
  return (newArray);
 }
 public static void main (String args[]) throws Exception
 {
  ARRAY a = null;
  a = send();
  if(a != null) {
   String[] values = (String[])a.getArray();
   for( int i = 0; i < values.length; i++ )
    System.out.println( "'" + values[i] + "'," );
  }
  else
   System.out.println("null is returned ");
  } 
}

here is the output:

C:\>java -classpath c:\;c:\oracle.jar test
'a',
'b',
'c',
'hello',
'world',
'array',
C:\>

Hope it helps.
[ October 23, 2002: Message edited by: Simon Lee ]
[ October 23, 2002: Message edited by: Simon Lee ]

[SJ Adnams]

sorry that example was for a single dimension array. However you should be able to see how to extend the example to a 2D array.
Simon

[Wang Alix]

can u divide a 2D array to both 1D arrays?