javaoracle-databasefunctionplsql

How to process tabular data from Oracle PL/SQL function in JAVA?


What I am used to is working with ResultSets from PreparedStatements, e.g.

try (PreparedStatement ps = dbConnection.prepareStatement(sqlQueryString)) {
    try (ResultSet queryResult = ps.executeQuery()) {
        while (queryResult.next()) {
            // do something with queryResult
        }
    }
}

Now, I created a PL/SQL function in an Oracle DB returning a table of data:

create or replace FUNCTION MY_FUNCTION 
(
  IN_PARAMETERS...
)
RETURN OUT_TABLE_TYPE IS
    RESULT_ROWS OUT_TABLE_TYPE;
BEGIN
    SELECT <fields>
    BULK COLLECT INTO RESULT_ROWS
    FROM SOME_TABLE
    WHERE <some complicated filter>

    RETURN RESULT_ROWS;
END MY_FUNCTION;

I would like to call this function in JAVA and iterate over the rows of data returned from the function. This is how far I am:

String dbCall = "{? = call MY_FUNCTION(?, ...)}

try (CallableStatement functionCall = dbConnection.prepareCall(dbCall)) {
            
    functionCall.registerOutParameter(1, Types.ARRAY, "SOME_TABLE");
    // register input parameter values:
    functionCall.setInt(2, value1);
    functionCall.set... // more input

    functionCall.executeUpdate();

    // now what??? I tried something like this:
    Array test = functionCall.getArray(1);
    while (test.getResultSet().next()) {
        // test contains data, but I don't know how to process it.
    }    
}

How do I iterate over the data row by row?


Solution

  • Write the function as:

    CREATE FUNCTION MY_FUNCTION 
    (
      IN_PARAMETERS...
    )
    RETURN SYS_REFCURSOR
    IS
      v_result SYS_REFCURSOR;
    BEGIN
      OPEN v_result FOR
        SELECT <fields>
        FROM SOME_TABLE
        WHERE <some complicated filter>;
    
        RETURN v_result;
    END MY_FUNCTION;
    

    Then loop through the result set in exactly the same way as you would from a procedure.