Oracle 12c table function to select subset of rows with FOR UPDATE SKIP LOCKED

Question

I have a requirement to return a subset of rows from a table using FOR UPDATE SKIP LOCKED. Based on application parameters this subset may or may not be ordered by a column. I can't use ROWNUM since the numbers are assigned before SKIP LOCKED happens, so using cursors and FETCH ... LIMIT seems to be the way to go.

That works using an anonymous PL/SQL block, but I need to expose the data back to the java application. The most straightforward way would be to use a table function, so I can just do SELECT * FROM table(my_function(<params>)).

I tried a standard table function returning a collection first, but I got the error [902] ORA-00902: invalid datatype. This is roughly what I had in place:

Package specification:

CREATE OR REPLACE PACKAGE ACTIVITY_UTILS AS

TYPE ActivityList IS TABLE OF ACTIVITY_TABLE%ROWTYPE;

FUNCTION activity_batch(batch_size IN INTEGER, order_by_source IN VARCHAR2)
RETURN ActivityList;

END ACTIVITY_UTILS;

Package body:

CREATE OR REPLACE PACKAGE BODY ACTIVITY_UTILS AS
FUNCTION activity_batch(batch_size IN INTEGER, order_by_source IN VARCHAR2)
RETURN ActivityList
IS
    batch ActivityList := ActivityList();
    selectStatement VARCHAR2(200);
    TYPE CursorType IS REF CURSOR;
    activitiesCursor CursorType;
BEGIN
    IF UPPER(order_by_source) = 'TRUE' THEN
        selectStatement := 'SELECT * FROM ACTIVITY_TABLE ORDER BY source FOR UPDATE SKIP LOCKED';
    ELSE
        selectStatement := 'SELECT * FROM ACTIVITY_TABLE FOR UPDATE SKIP LOCKED';
    OPEN activitiesCursor FOR selectStatement;
    FETCH activitiesCursor BULK COLLECT INTO batch LIMIT batch_size;
    CLOSE activitiesCursor;
    RETURN batch;
END activity_batch;

While debugging the ORA-00902 error I ran into this question: Return collection from packaged function for use in select

My (limited) understanding was I was trying to use a PL/SQL type on plain SQL, which is not allowed. I tried using a pipelined table function, as mentioned in the answer, but then I got the error ORA-14551: cannot perform a DML operation inside a query.

This seemed odd, is SELECT ... FOR UPDATE considered DML? At any rate, I noticed I could workaround this by using pragma autonomous_transaction, but that defeats the purpose of having FOR UPDATE SKIP LOCKED.

My question is, is this requirement achievable at all using functions, or would I have to use a procedure with an OUT parameter?

Answer 1

Option 1: create a function (or procedure) that returns a cursor and let your java application fetch it normally.

Option 2: Use Implicit statement results: in this case your java application can run something like call proc() where proc returns implicit statement results.

PS. It's not a good idea to hide DML under SQL select...