This discussion is archived
3 Replies Latest reply: Jan 17, 2013 8:37 AM by rp0428 RSS

Pass java array to plsql procedure ARRAY

MarcOtto Newbie
Currently Being Moderated
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.
  • 1. Re: Pass java array to plsql procedure ARRAY
    rp0428 Guru
    Currently Being Moderated
    >
    I am trying to pass a java string[] to a plsql procedure, but i get no values in the ARRAY passed to the procedure.
    . . .
    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.
    >
    You didnt' post your 4 digit Oracle version, ojdbc jar version or your jdk version.

    And you haven't shown that you 'get no values'. With small mods to your code I get values in the table.

    Both your Oracle code and Java code have fundamental errors.

    The biggest error in both is that you essentially have no exception handlers. That means you are throwing away any help you would otherwise be getting when a problem occurs. If an exception occurs in the procedure the commit will never happen so even if you got some data you would never know it.

    Get rid of this
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(sqlerrm);
    END;
    You need to know what is happening in the procedure, especially when things go wrong. You say you want help but then tell Oracle not to give you any information that might actually help.

    And an empty exception handler in Java does the same thing: throws away information that is needed when things go wrong.
    At a minimum you need to print a stack trace and identify where a problem happened.
    . . .
    } catch (SQLException e) {
    throw new JboException(e);
    } finally {
    if (st != null) {
    try {
    // 7. Close the statement
    st.close();
    } catch (SQLException e) {
    }
    }
    }
    }
    Why do you have two different statement objects in your code?
    OracleCallableStatement st = null;
    String[] ret = {"","",""};
    String plsql = "begin pack.set_table(?,?); end;";
    try {
    // 1. Create a JDBC CallabledStatement
    CallableStatement call;
    And why are you then trying to use both of them?
    st = (OracleCallableStatement)getDBTransaction().createCallableStatement(plsql, 0);
    Connection conn = st.getConnection();
    call = conn.prepareCall(plsql);
    Pick one and get rid of the other. I used 'st', the Oracle version and my code works without a problem.

    Do you really have a user named 'PUBLIC'?
    String plsql = "begin pack.set_table(?,?); end;";
    That causes a looping chain of synonyms for me. But then I knew that because my code had an exception handler that printed the stack trace.

    I created the procedure in the SCOTT schema and the Java code connection was to the SCOTT schema so I just used.
    String plsql = "begin set_table(?,?); end;";
    These are the core pieces of the code I used and it works just fine using Oracle 11.2.0.1, ojdbc6, and Java 1.6.0_22
    CREATE TYPE varchar2_array AS TABLE OF VARCHAR2(4000);
    
    create table xx (col1 varchar2(4000))
    
        public static void proAction() {
            OracleCallableStatement st = null;
            String plsql = "begin set_table(?,?); end;";
            try {
            // 1. Create a JDBC CallabledStatement
            st = (OracleCallableStatement) conn.prepareCall(plsql);
            ArrayDescriptor ad = ArrayDescriptor.createDescriptor("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
            st.setArray(1, kar);
            st.setArray(2, var);
            // Make the call
            st.execute();
            // Get result and form list to return
    
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
            if (st != null) {
            try {
            // 7. Close the statement
            st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            }
            }
        }
    
    select * from xx
    
    COL1
    begin
    Y
    Y
    Y
    N
    Y
    I
  • 2. Re: Pass java array to plsql procedure ARRAY
    MarcOtto Newbie
    Currently Being Moderated
    I use Oracle version: 11.2.0.3 , ojdbc6 and jdk: 1.7.0_07

    I have done quite some testing before posting, thats why my code was not clean. I have added exceptions and only use one callableStatement. I have tried with both the Oracle CallableStatement and the java.sql CallableStatement still the same result.

    I need to have the pack infront of the procedure call, since it is the package where it is located, but this should not make a difference.

    String plsql = "begin pack.set_table(?,?); end;";

    For the PUBLIC.VARCHAR2_ARRAY, It is because i have declared the VARCHAR2_ARRAY in another schema than the application user and i have created a public synonym to be used. When i try to call it without PUBLIC in front i get "invalid name pattern".

    I get no exceptions when running the code and i still get just 6 empty rows inserted.

    Any further ideas ?

    Thx for the previous response.
  • 3. Re: Pass java array to plsql procedure ARRAY
    rp0428 Guru
    Currently Being Moderated
    And what was the result when you used the same code that I used?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points