Oracle 10g : How to pass ARRAYS to Oracle using Java?

Environment : JDK 1.5 (did not test on older versions of java but it should work on them !!)

Keep the JDBC driver in the classpath : '''ojdbc14.jar'''

Note : Oracle ARRAY is supported on Oracle9i Database version 9.0.1 or later.

Take a look at the package : Tests

'''Java Code''' :
(Look at the technique - don't look at the java standard practices like using try-catch-finally ...etc., to keep the code readable, I have just taken them out.)
----------------------------------------------------------------------------------------------------------
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
public class TestArray {
private static Connection getConnection() {
Connection conn = null;
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn =
DriverManager.getConnection("",
"test", "test");
conn.setAutoCommit(false);
System.out.println("Connection :- " + conn);
} catch (SQLException e) {
System.out.println(e.getMessage());
}
return conn;
}

private static void insertEmp(Connection conn) throws Exception {
/* let us create 20 names in a string array*/
String[] names = new String[20];
for (int i = 0; i < 20; i++) {
names[i] = "Ravi" + i;
}
/*Create an array descriptor of type STRINGS */
ArrayDescriptor desc =
ArrayDescriptor.createDescriptor("STRINGS", conn);
/* Create an array object */
ARRAY nameArray = new ARRAY(desc, conn, names);
String st = "begin Tests.insert_emp(:1); end;";
CallableStatement cs = conn.prepareCall(st);
cs.setArray(1, nameArray);
cs.execute();
cs.close();
conn.commit();
System.out.println("Successfully INSERTED " + names.length +
" EMPLOYEE records :- ");
}

private static void getEmp(Connection conn) throws Exception {
String[] names = { };
/*Create an array descriptor of type STRINGS */
ArrayDescriptor desc =
ArrayDescriptor.createDescriptor("STRINGS", conn);
/* Create an array object */
ARRAY nameArray = new ARRAY(desc, conn, names);
String st = "begin Tests.get_emp(:1); end;";
CallableStatement cs = conn.prepareCall(st);
/* Register the out parameter as STRINGS */
cs.registerOutParameter(1, OracleTypes.ARRAY, "STRINGS");
cs.execute();
/* Get the array into a local ARRAY object */
nameArray = (ARRAY)cs.getArray(1);
System.out.println("Array is of type " + nameArray.getSQLTypeName());
System.out.println("Array element is of type code "+nameArray.getBaseType());
System.out.println("Array is of length " + nameArray.length());
/* Get the array of names into a string array */
names = (String[])nameArray.getArray();
System.out.println("*****************************************************************");
System.out.println("Retrieving ONLY the names of employees as an ARRAY \n");
System.out.println("*****************************************************************");
for (int i = 0; i < names.length; i++)
System.out.println(names[i]);
cs.close();
}

private static void getEmpRecs(Connection conn) throws SQLException {
/* Create an array of objects (since we are getting heterogeneous data)*/
Object[] empRecs = { };
/* Create an array descriptor for EMP_REC_ARRAY */
ArrayDescriptor desc =
ArrayDescriptor.createDescriptor("EMP_REC_ARRAY", conn);
ARRAY nameArray = new ARRAY(desc, conn, empRecs);
String st = "begin Tests.get_emp_recs(:1); end;";
CallableStatement cs = conn.prepareCall(st);
/* Register OUT param as EMP_REC_ARRAY */
cs.registerOutParameter(1, OracleTypes.ARRAY, "EMP_REC_ARRAY");
cs.execute();
/* Get the Array into a local ARRAY object */
nameArray = (ARRAY)cs.getArray(1);
System.out.println("Array is of type " + nameArray.getSQLTypeName());
System.out.println("Array element is of type code "+nameArray.getBaseType());
System.out.println("Array is of length " + nameArray.length());
/* Get the Employee Records */
empRecs = (Object[])nameArray.getArray();
System.out.println("***********************************************************");
System.out.println("Retrieving the EMPLOYEE RECORDS as an ARRAY !!\n");
System.out.println("***********************************************************");
int id = 0;
String name = "";
for (int i = 0; i < empRecs.length; i++) {
/* Since we don't know the type of the record, get it into STRUCT !! */
oracle.sql.STRUCT empRec = (oracle.sql.STRUCT)empRecs[i];
/* Get the attributes - nothing but the columns of the table */
Object[] attributes = empRec.getAttributes();
/* 0- first column, 1 - second column ...*/
id = Integer.parseInt("" + attributes[0]);
name = "" + attributes[1];
System.out.println("id = " + id + " name = " + name);
}
cs.close();
}

public static void main(String[] args) throws Exception {
Connection conn = null;
try {
conn = getConnection();
/*Insert 20 employee names*/
insertEmp(conn);
/*Get the 20 employee names*/
getEmp(conn);
/*Get the 20 employee RECORDS*/
getEmpRecs(conn);
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {
if (conn != null)
conn.close();
}
}
}

----------------------------------------------------------------------------------------------------------
'''SQL Scripts :'''

Create a test table and the types :

create table emp(id number , name varchar2(50))
/

create or replace type Strings IS VARRAY(20) of VARCHAR2(30)
/

create or replace type emp_rec IS OBJECT (id number, name varchar2(30))
/

create or replace type emp_rec_array AS VARRAY(100) of emp_rec
/

create or replace package Tests is

-- Author : r-a-v-i

procedure insert_emp(emp_names Strings);
procedure get_emp(emp_names out Strings);
procedure get_emp_recs(emp_recs out emp_rec_array);
end Tests;
/
create or replace package body Tests is
procedure insert_emp(emp_names Strings)
as
cnt Integer := 1;
v_id Integer := 0;
begin
dbms_output.put_line(emp_names.COUNT);
loop
if cnt > emp_names.COUNT then
exit;
else
dbms_output.put_line(cnt||' = '||emp_names(cnt));
select nvl(max(id),0) + 1 into v_id from emp;
insert into emp(id,name) values (v_id,emp_names(cnt));
end if;
cnt := cnt + 1;
end loop;
commit;
end;
procedure get_emp(emp_names out Strings)
as
v_emp_names Strings := Strings();
cursor c1 is select name from emp order by id;
v_name varchar2(30);
cnt Integer := 1;
begin
open c1;
loop
fetch c1 into v_name;
if c1%notfound then exit;
else
dbms_output.put_line(cnt||' = '||v_name);
v_emp_names.extend;
v_emp_names(cnt) := v_name;
end if;
cnt := cnt + 1;
end loop;
emp_names := v_emp_names;
end;
procedure get_emp_recs(emp_recs out emp_rec_array)
as
v_emp_recs emp_rec_array := emp_rec_array();
cursor c1 is select id,name from emp order by id;
v_id Integer;
v_name varchar2(30);
v_emp_rec emp_rec;
cnt Integer := 1;
begin
open c1;
loop
fetch c1 into v_id,v_name;
if c1%notfound then exit;
else
v_emp_rec := emp_rec(v_id,v_name);
v_emp_recs.extend;
v_emp_recs(cnt) := v_emp_rec;
end if;
cnt := cnt + 1;
end loop;
emp_recs := v_emp_recs;
end;
end Tests;
/

Long live "Tom Kyte".

----------------------------------------------------------------------------------------------------------