Как я могу заблокировать и вернуть несколько строк из функции Oracle?

Question

Я пытался решить вопрос о том, как Oracle обрабатывает ROWNUM а также SELECT ... FOR UPDATE SKIP LOCKED при попытке вернуть несколько строк, которые не заблокированы. Я испробовал ряд решений из следующих: Принудительно Oracle возвращает TOP N строк с SKIP LOCKED, а также несколько других примеров, которые очень похожи на те, которые были найдены по этому вопросу. Я знаю, что Oracle AQ, вероятно, является лучшим решением для этого, но у нас очень мало контроля над базами данных, и я встретил значительное сопротивление этой идее.

Проблема, с которой я сталкиваюсь, заключается в попытке вернуть результаты в Java с помощью JDBC. я пытался setFetchSize(20), но я сталкиваюсь с проблемой, когда только первые 20 строк распределяются между клиентами. Я обычно вижу, что один агент обработки получает все 20 строк или несколько процессоров получают несколько строк, все они добавляют до 20. Это очень похоже на поведение, которое можно увидеть при использовании ROWNUM в сочетании с SELECT ... FOR UPDATE SKIP LOCKED,

Наиболее многообещающее решение, которое я попробовал, - это следующая функция:

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;

Тем не менее, когда я пытаюсь вызвать его так:

select * from table(SELECTIDS)

Я получаю ORA-14551: cannot perform a DML operation inside a query ошибка, которую я теперь понимаю, является проблемой с транзакциями. Снятие блокировок заставляет функцию возвращать строки.

Как я могу получить несколько строк из этой функции в JDBC, сохраняя блокировки?

Answer 1

Это не сработает. Вы вызываете функцию pl/sql как часть оператора select и пытаетесь запустить транзакцию в этой функции. Я думаю, что ошибка довольно очевидна.