SELECT FOR UPDATE

ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "DFEFRRTP1.COIN_PACKAGE", line 88
06519. 00000 -  "active autonomous transaction detected and rolled back"
*Cause:    Before returning from an autonomous PL/SQL block, all autonomous
           transactions started within the block must be completed (either
           committed or rolled back). If not, the active autonomous
           transaction is implicitly rolled back and this error is raised.
*Action:   Ensure that before returning from an autonomous PL/SQL block,
           any active autonomous transactions are explicitly committed
           or rolled back.

FUNCTION GET_MONITORING_ON_APPS
    RETURN MONITORING_ON_APPLICATIONS PIPELINED
  IS
    PRAGMA AUTONOMOUS_TRANSACTION;
    REC MONITORING_ON_RECORD;
    l_MONITORING_ON_RECORDS MONITORING_ON_RECORD;
    CURSOR MONITORING_ON_RECORDS
    IS
     SELECT A.ID FROM A
   JOIN B CH ON A.ID = B.ID AND B.STATUS_ID = 21
   JOIN C ON A.ID = C.ID AND TR.OTHER_ID = (SELECT MAX(OTHER_ID) FROM C WHERE OTHER_ID= A.ID) FOR UPDATE SKIP LOCKED;
  BEGIN
    OPEN MONITORING_ON_RECORDS;
    LOOP
      FETCH MONITORING_ON_RECORDS INTO l_MONITORING_ON_RECORDS;
      EXIT WHEN MONITORING_ON_RECORDS%NOTFOUND;
      
      UPDATE C
      SET SYNC_FLAG = 1,
        SYNC_TIMESTAMP = SYSTIMESTAMP
      WHERE CURRENT OF MONITORING_ON_RECORDS;
      PIPE ROW(l_MONITORING_ON_RECORDS);
    END LOOP;
    COMMIT;
    RETURN;
  END GET_MONITORING_ON_APPS;

When a server select an ID and perform operations on it the other servers can not select the same ID.


It sort out sounds like you re-inventing Advanced Queuing, which is a free component of the Oracle database. In the example you gave above, the style of operation would be along the lines of (pseudo-code)

- single process does:

for each ID in
( SELECT A.ID FROM A
   JOIN B CH ON A.ID = B.ID AND B.STATUS_ID = 21
   JOIN C ON A.ID = C.ID AND TR.OTHER_ID = (SELECT MAX(OTHER_ID) FROM C WHERE OTHER_ID= A.ID) FOR UPDATE SKIP LOCKED;
)
do
   dbms_aq.enqueue(id)
done

Then you can have as many concurrent processes as you like that do:

repeat forever
  msg := dbms_aq.dequeue
  do your processing on (msg.id) 
  commit;
end loop

In your case, it is the combination of "pipe row" and the autonomous transaction. Piping means that you are "returning" from the function before all of the rows are exhausted, hence the "active" transaction error - we are *still* in that cursor loop when we pass control back to the calling environment.

Even putting aside concurrency and skip locked, we can see that

SQL> create table t as
  2  select rownum x from dual connect
  3  by level <= 100;

Table created.

SQL>
SQL> create or replace
  2  function fnc return sys.odcinumberlist pipelined as
  3    pragma autonomous_transaction;
  4  begin
  5   for i in ( select * from t for update )
  6   loop
  7     pipe row ( i.x );
  8   end loop;
  9   commit;
 10   return;
 11  end;
 12  /

Function created.

SQL>
SQL> select * from fnc();
select * from fnc()
              *
ERROR at line 1:
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "MCDONAC.FNC", line 6
ORA-06512: at "MCDONAC.FNC", line 6

To enable what you want (without too much code rework) you can fetch the rows, tag them as belonging to your session, and then return them to the client, eg

SQL> create table t as
  2  select rownum x from dual connect
  3  by level <= 100;

Table created.

SQL>
SQL> alter table t add acquired_by number;

Table altered.

SQL>
SQL> create or replace
  2  function fnc return sys.odcinumberlist pipelined as
  3    pragma autonomous_transaction;
  4    id_list sys.odcinumberlist := sys.odcinumberlist();
  5  begin
  6   select x
  7   bulk collect into id_list
  8   from t
  9   where acquired_by is null
 10   for update skip locked ;
 11
 12   forall i in 1 .. id_list.count
 13     update t set acquired_by = sys_context('userenv','sid')
 14     where x = id_list(i);
 15     commit;
 16
 17   for i in 1 .. id_list.count
 18   loop
 19     pipe row (id_list(i));
 20   end loop;
 21   return;
 22  end;
 23  /

Function created.

SQL>
SQL> select * from fnc();

COLUMN_VALUE
------------
           1
           2
           3
           4
           5
           6
...
...
          98
          99
         100

100 rows selected.

SQL>

Now you can process the ID's you've retrieved. If you're looking to "load balance" in some way across multiple sessions, then you'd would add a limiter on the query, eg (rownum < 500 ) so multiple sessions can grab a batch of rows and work on them.

But AQ just sounds nicer to me.