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

Question

I have to communicate with an external Oracle procedure that has a SYS_REFCURSOR as an input parameter:

procedure merge_objects(p_table_name in varchar2, p_id_array in varchar2, p_cur_data in SYS_REFCURSOR)

I need to pass SYS_REFCURSOR parameter based on the data that I receive from a client. Is there any way to create such parameter in Java?

Answer 1

The solution to pass SYS_REFCURSOR from Java directly DOES exist. Without the need to insert data in database.

The following statement generates SYS_REFCURSOR in Oracle (example with values and column names):

OPEN cur_data FOR select '000000' inn, 'Ch' lastname from dual;

Now I will show how to implement this. Here is a test working example of code. The procedure merge_objects has SYS_REFCURSOR as a third input param. The example for Oracle:

public static void main(String[] args) {
    try {

         Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@...", "username", "password");

         String plsql =
                        "declare cur_data SYS_REFCURSOR;\n" +
                        "BEGIN\n" +
                        "OPEN cur_data FOR select '000000' inn, 'Ch' lastname from dual;\n" +
                        "END;\n" +
                        "merge_objects('tbl_o_persons',\n" +
                        "                '19863572,19863598',\n" +
                        "                cur_data);\n" +
                        "CLOSE cur_data;\n" +
                        "end;";

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

        conn.close();

    }catch(Exception ex){
        System.out.println("Error: " + ex.toString());
    }
}

So based on your data you can modify string with OPEN statement, include your data and so pass CURSOR to neccesary procedure directly from Java.

Answer 2

It is possible to do something like this, but it's a bit fiddly. I've come up with two ways to do this, but both of them rely on being able to create objects in the database. I appreciate you might not have permission to do this.

The bottom line is that the ref cursor object that gets passed into the stored procedure has to be created within the Oracle database itself. We have to put the data in the database somehow and then put a cursor around that. You can't create your own implementation of ResultSet and expect the JDBC driver and database to read data out of that.

For the purposes of demonstration 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'll also use the following simple Java class, which represents a row of the table:

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 putting all of the data to insert into a temporary table, and then creating a cursor to select data from that. For this to work we'll need the following to be created in the database:

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

Once this is 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

This approach uses types instead of a temporary table, and uses the SQLData interface to allow the JDBC driver to map the Java objects to Oracle objects. It requires the following types to be created in the database (feel free to choose better 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 modify the Row class to implement SQLData: this requires the following three methods to be added:

    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();
    }