How to Pass List from Java to Oracle Procedure

How to pass List from java to Oracle Procedure?

Convert your lists to a mutli-dimensional array and then you can do something like:

Oracle Setup:

CREATE TYPE stringlist AS TABLE OF VARCHAR2(100);
/

CREATE TYPE stringlist_list AS TABLE OF stringlist;
/

CREATE TYPE stringlist_list_list AS TABLE OF stringlist_list;
/

CREATE PROCEDURE load_list (
  in_list IN stringlist_list_list
)
AS
BEGIN
  NULL; -- Do something with the list
END;
/

Java:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class TestDatabase2 {
  public static void main(String args[]){
    try{
      Class.forName("oracle.jdbc.OracleDriver");

      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","username","password");

      // Convert your lists to arrays using #toArray( T[] )

      String[] l1 = { "Math", "Physics" };
      String[] l2 = { "English", "Spanish" };
      String[] l3 = { "French", "German" };

      ArrayDescriptor des = ArrayDescriptor.createDescriptor("STRINGLIST_LIST_LIST", con);

      ARRAY school = new ARRAY( des, con, newString[][][]{
        new String[][]{ l1, l3 },
        new String[][]{ l2, l3 }
      } );

      CallableStatement st = con.prepareCall("{ call add_school( :school )}");

      // Passing an array to the procedure - 
      ((OracleCallableStatement) st).setARRAYAtName( "school", school );

      st.execute();
    } catch(ClassNotFoundException | SQLException e) {
      System.out.println(e);
    }
  }
}

Java program to pass List of Bean to a oracle stored procedure - Pass entire list at one shot rather than appending objects one after the other

I would use a simpler table type:

CREATE OR REPLACE TYPE NUM_ARRAY AS TABLE OF NUMBER;

And then somewhat simplify the stored procedure:

CREATE OR REPLACE PROCEDURE GL_PROCESS_BULK_ENTRIES (
   p_array    IN     NUM_ARRAY,
   p_status   OUT    VARCHAR2)
IS
...

You then create an array of Integer that will easily fit in memory:

Integer[] idArray = new Integer[50000];

// fill the array of integers here
for (int i = 0; i < idArray.length; i++)
    idArray[i] = ....;

ARRAY a = new ARRAY(des, con, idArray);
cs.setObject(1, (Object)a);

There's no need to create any heavy-weight beans just to pass a list of IDs.

pass array to oracle procedure

Here's an example of how to do it.

The following script sets up a table, a type and a stored procedure in the database. The procedure takes a parameter of the array type and inserts each row of the array into the table:

CREATE TABLE strings (s VARCHAR(4000));

CREATE TYPE t_varchar2_array AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE PROCEDURE p_array_test(
    p_strings      t_varchar2_array
)
AS
BEGIN
  FOR i IN 1..p_strings.COUNT
  LOOP
    INSERT INTO strings (s) VALUES (p_strings(i));
  END LOOP;
END;
/

The Java code then demonstrates passing an array into this stored procedure:

import java.sql.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class ArrayTest {
    public static void main(String[] args) throws Exception {
        DriverManager.registerDriver(new OracleDriver());
        Connection conn = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:xe", "user", "pass");

        CallableStatement stmt = conn.prepareCall("BEGIN p_array_test(?); END;");

        // The first parameter here should be the name of the array type.
        // It's been capitalised here since I created it without using
        // double quotes.
        ArrayDescriptor arrDesc =
            ArrayDescriptor.createDescriptor("T_VARCHAR2_ARRAY", conn);

        String[] data = { "one", "two", "three" };
        Array array = new ARRAY(arrDesc, conn, data);
        stmt.setArray(1, array);
        stmt.execute();

        conn.commit();
        conn.close();
    }
}

If you run the SQL script and then the Java class, and then query the table strings, you should find that all of the data has been inserted into the table.

When you say 'an array of chars', I'm guessing that you mean an array of Java chars. If I've guessed right, then I think you'd be best off converting the chars to Strings and then using the same approach as above.

---

Related Topics