Create and pass SYS_REFCURSOR as input parameter to Oracle procedure from Java

You can do something like this, but it's a little weird. I came up with two ways to do this, but both rely on the ability to create objects in the database. I appreciate that you may not have permission to do this.

The bottom line is that the ref cursor object that is passed to the stored procedure must be created in the Oracle database itself. We have to put the data into the database somehow and then put the cursor there. You cannot create your own implementation ResultSet

and expect the JDBC driver and database to read data from that.

For demonstration purposes, I'll create the following table and procedure:

CREATE TABLE example_table (id NUMBER, name VARCHAR2(100));

CREATE OR REPLACE PROCEDURE p_insert_objects (
  p_records                 IN SYS_REFCURSOR
)
IS
  l_id              example_table.id%TYPE;
  l_name            example_table.name%TYPE;
BEGIN
  LOOP
    FETCH p_records INTO l_id, l_name;
    EXIT WHEN p_records%NOTFOUND;
    INSERT INTO example_table (id, name) VALUES (l_id, l_name);
  END LOOP;
END;
/

      

        
        
        
      

    

We will also use the following simple Java class that represents a table row:

class Row {
    private int id;
    private String name;

    public Row(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public int getId() { return this.id; }
    public String getName() { return this.name; }
}

      

        
        
        
      

    

Approach 1: using a global temporary table

This approach involves including all data in a temporary table and then creating a cursor to select data from that. To do this, we need to create the following in the database:

CREATE GLOBAL TEMPORARY TABLE example_tmp (id NUMBER, name VARCHAR2(100))
  ON COMMIT DELETE ROWS;

      

        
        
        
      

    

Once that's done, the following code should work:

    // Clear out anything that happens to be in the temp table, e.g. because of a 
    // previous call to this code in the same transaction.
    try (PreparedStatement stmt = conn.prepareStatement("DELETE FROM example_tmp")) {
        stmt.execute();
    }

    List<Row> data = ... // get these from somewhere
    try (PreparedStatement stmt = conn.prepareStatement(
            "INSERT INTO example_tmp (id, name) VALUES (?, ?)")) {
        for (Row row : data) {
            stmt.setInt(1, row.getId());
            stmt.setString(2, row.getName());
            stmt.execute();
        }
    }

    String plsql =
        "DECLARE\n" +
        "  l_cursor SYS_REFCURSOR;\n" + 
        "BEGIN\n" +
        "  OPEN l_cursor FOR SELECT id, name FROM example_tmp;\n" + 
        "  p_insert_objects(l_cursor);\n"+
        "END;";

    try (PreparedStatement stmt = conn.prepareStatement(plsql)) {
        stmt.execute();
    }

      

        
        
        
      

    

Approach 2: types and SQLData h3>

This approach uses types instead of a temporary table and uses an interface SQLData

to allow the JDBC driver to map Java objects to Oracle objects. It requires the following types to be created in the database (feel free to choose the best names):

CREATE OR REPLACE TYPE row_t AS OBJECT (id NUMBER, name VARCHAR2(100));
/

CREATE OR REPLACE TYPE rows_t AS TABLE OF row_t;
/

      

        
        
        
      

    

We also need to change the class Row

for implementation SQLData

: for this we need to add the following three methods:

    public void readSQL(SQLInput input, String typeName) throws SQLException {
        this.id = Integer.parseInt(input.readString());
        this.name = input.readString();
    }

    public void writeSQL(SQLOutput output) throws SQLException {
        output.writeString(Integer.toString(this.id));
        output.writeString(this.name);
    }

    public String getSQLTypeName() { return "ROW_T"; }

      

        
        
        
      

    

Once you've done that, the following should allow you to call the procedure:

    // Tell the connection to associate the Row class with the ROW_T type
    Map<String, Class<?>> map = conn.getTypeMap();
    map.put("ROW_T", Row.class);
    conn.setTypeMap(map);        

    List<Row> data = ... // get these from somewhere.

    Array array = ((OracleConnection)conn).createOracleArray("ROWS_T", data.toArray());

    String plsql =
        "DECLARE\n" +
        "  l_rows   ROWS_T;\n" +
        "  l_cursor SYS_REFCURSOR;\n" + 
        "BEGIN\n" +
        "  l_rows := ?;\n" +
        "  OPEN l_cursor FOR SELECT id, name FROM TABLE(l_rows);\n" +
        "  p_insert_objects(l_cursor);\n"+
        "END;";

    try (PreparedStatement stmt = conn.prepareStatement(plsql)) {
        stmt.setObject(1, array);
        stmt.execute();
    }