ITTONE Création des applications Web

pass array to stored Procedure(Oracle) from Java

I’m using Java 8 with Oracle DB 19c and trying to access a SP:

public static void main(String[] args) {
String dbURL = “MY_DB_URL”;
String user = “USER”;
String password = “PASS”;

    Integer[] ids = {213};
    try {
        try {
            Class.forName("oracle.jdbc.OracleDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        Connection conn = DriverManager.getConnection(dbURL, user, password);
        try {
            conn.setAutoCommit(true);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        CallableStatement statement = conn.prepareCall("{call MY_PROCEDURE(?,?)}");
        ArrayDescriptor desc = ArrayDescriptor.createDescriptor("COLUMN_TYPE", conn);

        ARRAY inArray = new ARRAY(desc, conn, ids);

        statement.setObject(1, inArray);

        statement.registerOutParameter(2, OracleTypes.VARCHAR);

        statement.execute();
        conn.commit();

        statement.close();

    } catch (SQLException ex) {
        ex.printStackTrace();
    }
}

SP looks like this(expecting an array of Integer)

PROCEDURE MY_PROCEDURE    (
  i_id  IN COLUMN_TYPE
 ,o_err_msg OUT VARCHAR    )

The code runs ok, but nothing is changing inside DB. The procedure should update a field. I”m guessing it’s something wrong with the way I’m passing the array of integers , but I could really need some advice. Thanks and appreciate

Product of the Month September 2016

Source link

Leave a Comment

Your email address will not be published. Required fields are marked *