This is an example about how to pass string, string array or string pairs array from Javy to Oracle You should create the following types in your database:
create or replace type VARCHAR2_PAIR as object( k varchar2(200), v varchar2(200)); create or replace type VARCHAR2_PAIRS_TABLE as table of VARCHAR2_PAIR; create or replace type VARCHAR2_TABLE as table of VARCHAR2(200);
Then you should create stored procedure of the following signature
CREATE OR REPLACE PROCEDURE arrays_from_java(p_scalar varchar2 , p_array IN VARCHAR2_TABLE , p_pairs_array in VARCHAR2_PAIRS_TABLE);
And in the end call it from Java
public static void callArrayProcedure() throws Exception{
System.out.println("Preparing data");
String[] list1=new String[2];
list1[0] = "first string";
list1[1] = "second string";
String[][] list2=new String[2][];
{
String[] pair1 = new String[2];
pair1[0] = "key1";
pair1[1] = "value1";
list2[0] = pair1;
}
{
String[] pair2 = new String[2];
pair2[0] = "key2";
pair2[1] = "value2";
list2[1] = pair2;
}
System.out.println("Preparing connection");
SingleConnectionDataSource scds = new SingleConnectionDataSource();
scds.setDriverClassName("oracle.jdbc.driver.OracleDriver");
scds.setUrl("jdbc:oracle:thin:@host:1521:service_name");
scds.setUsername(...);
scds.setPassword(...);
Connection conn = scds.getConnection();
System.out.println("Preparing array of strings");
ArrayDescriptor descriptor1 = ArrayDescriptor.createDescriptor("VARCHAR2_TABLE", conn );
ARRAY arrayToPass1 = new ARRAY( descriptor1, conn, list1 );
System.out.println("Preparing array of string pairs");
ArrayDescriptor descriptor2 = ArrayDescriptor.createDescriptor("VARCHAR2_PAIRS_TABLE", conn );
ARRAY arrayToPass2 = new ARRAY( descriptor2, conn, list2 );
System.out.println("Calling procedure");
OraclePreparedStatement ps = (OraclePreparedStatement) conn.prepareStatement( "{ call arrays_from_java( ?, ?, ? ) }" );
ps.setString( 1, "string");
ps.setARRAY( 2, arrayToPass1 );
ps.setARRAY( 3, arrayToPass2 );
ps.execute();
ps.close();
}
You should also add orai18n.jar to Java classpath, otherwise strings may become null after passing them to stored procedure if the basic character set (NLS_CHARACTERSET) is not UTF
