How can I lock and return multiple rows from an Oracle function?

Question

I have been trying to address the issue of how Oracle processes ROWNUM and SELECT ... FOR UPDATE SKIP LOCKED while trying to return several rows that aren't locked. I have tried a number of the solutions from the following: Force Oracle to return TOP N rows with SKIP LOCKED, as well as several other examples that look very similar to the ones found on that question. I know Oracle AQ is probably the best solution to this, but we have very little control over the databases and I have met with considerable resistance to the idea.

The problem I am running into is trying to get the results back to Java using JDBC. I have tried setFetchSize(20), but I run into the issue where only the top 20 rows are distributed to the clients. I usually see one processing agent getting all 20 rows or a few processors getting some rows, all of them adding up to 20. This is very much like the behavior one would see with using ROWNUM in conjunction with SELECT ... FOR UPDATE SKIP LOCKED.

The most promising solution I have tried is the following function:

create type IND_ID as object
(
   ID varchar2(200)
);

create type IND_ID_TABLE as table of IND_ID;

create or replace function SELECTIDS return IND_ID_TABLE
   pipelined is

   ST_CURSOR SYS_REFCURSOR;
   ID_REC IND_ID := IND_ID(null);

begin
   open ST_CURSOR for
      select ID
        from TABLE
    /* where clause */
         for update SKIP LOCKED;   
   loop
      fetch ST_CURSOR
         into ID_REC.ID;
      exit when ST_CURSOR%rowcount > 20 or ST_CURSOR%notfound;
      pipe row(ID_REC);
   end loop;
   close ST_CURSOR;
   return;
end;

However, when I try invoking it like so:

select * from table(SELECTIDS)

I get an ORA-14551: cannot perform a DML operation inside a query error, which I now understand is an issue with transactions. Removing the locks causes the function to return rows.

How can I get multiple rows out of this function into JDBC while preserving the locks?

Answer 1

This not gonna work. You calling a pl/sql function as part of a select statement and try to start a transaction in that function. I think the error is pretty clear.