What I am used to is working with ResultSet
s from PreparedStatement
s, 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?
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.