Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Pass java array to plsql procedure ARRAY

Marc OttoJan 16 2013 — edited Nov 20 2014
I am trying to pass a java string[] to a plsql procedure, but i get no values in the ARRAY passed to the procedure.

This is how i have defined the array in the database:
CREATE TYPE varchar2_array AS TABLE OF VARCHAR2(4000);

Then i have this dummy procedure:
PROCEDURE set_table(i_keys_array IN varchar2_array
,i_values_array IN varchar2_array)
IS
s1 VARCHAR2(200);
BEGIN
g_kdfve025_v_r := NULL;
s1 := i_keys_array(1);
INSERT INTO xx values ('begin');
FOR i IN 1 .. i_keys_array.COUNT LOOP
INSERT INTO xx values (s1);
INSERT INTO xx values (i_values_array(i));
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
END;

My java part then looks like this:
public void proAction() {
OracleCallableStatement st = null;
String[] ret = {"","",""};
String plsql = "begin pack.set_table(?,?); end;";
try {
// 1. Create a JDBC CallabledStatement
CallableStatement call;
st = (OracleCallableStatement)getDBTransaction().createCallableStatement(plsql, 0);
Connection conn = st.getConnection();
call = conn.prepareCall(plsql);
ArrayDescriptor ad = ArrayDescriptor.createDescriptor("PUBLIC.VARCHAR2_ARRAY", conn);

ARRAY kar = new ARRAY(ad, conn, new String[] {"Y", "N", "I"});
ARRAY var = new ARRAY(ad, conn, new String[] {"Y", "N", "I"});
// Pass Input
call.setArray(1, kar);
call.setArray(2, var);
// Make the call
call.execute();
// Get result and form list to return

} catch (SQLException e) {
throw new JboException(e);
} finally {
if (st != null) {
try {
// 7. Close the statement
st.close();
} catch (SQLException e) {
}
}
}
}

The lenght of the ARRAYS in plsql is positive meaning larger than 0. But i get no values in the xx table, it is just empty values it has in the list apparently. Any ideas how i can solve this.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 14 2013
Added on Jan 16 2013
3 comments
1,479 views