title: How can I lock and return multiple rows from an Oracle function?
body: <p>I have been trying to address the issue of how Oracle processes <code>ROWNUM</code> and <code>SELECT ... FOR UPDATE SKIP LOCKED</code> while trying to return several rows that aren't locked. I have tried a number of the solutions from the following: <a href="https://stackoverflow.com/questions/6117254/force-oracle-to-return-top-n-rows-with-skip-locked">Force Oracle to return TOP N rows with SKIP LOCKED</a>, 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.</p>
  
  <p>The problem I am running into is trying to get the results back to Java using JDBC. I have tried <code>setFetchSize(20)</code>, 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 <code>ROWNUM</code> in conjunction with <code>SELECT ... FOR UPDATE SKIP LOCKED</code>.</p>
  
  <p>The most promising solution I have tried is the following function:</p>
  
  <pre class="lang-sql prettyprint-override"><code>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 &gt; 20 or ST_CURSOR%notfound;
        pipe row(ID_REC);
     end loop;
     close ST_CURSOR;
     return;
  end;
  </code></pre>
  
  <p>However, when I try invoking it like so:</p>
  
  <pre class="lang-sql prettyprint-override"><code>select * from table(SELECTIDS)
  </code></pre>
  
  <p>I get an <code>ORA-14551: cannot perform a DML operation inside a query</code> error, which I now understand is an issue with transactions. Removing the locks causes the function to return rows.</p>
  
  <p>How can I get multiple rows out of this function into JDBC while preserving the locks?</p>