Hi Experts,
I have a doubt regarding the use of GTT in an AUTONOMOUS_TRANSACTION procedure.
Scenario -
I have a procedure X which sends records from table T1 to T2. ( Actual business is something different, but simplifying/masking things here )
Say this thing is done using a cursor CUR_X inside a loop, record-by-record. And, after each record is moved there's a SUCCESS_LOG table where the record_id is logged. This is done through a logging procedure made as AUTONOMOUS_TRANSACTION.
Table SUCCESS_LOG is a GTT ( with ON COMMIT PRESERVE ROWS ).
The cursor is something like this -
CURSOR cur_x
IS
SELECT * FROM T1
WHERE NOT EXISTS ( select 1 from SUCCESS_LOG sl where t1.record_id = sl.record_id ) ;
Basically, this checks and send only those T1 records which are not sent (i.e. present in SUCCESS_LOG).
My doubt -
If i have a COMMIT in the logging procedure after insertion into the SUCCESS_LOG ( GTT ), will the records be reflecting immediately in the actual transaction flow i.e. in the cursor NOT EXISTS of procedure X ?
Please advise.
Thanks,
-Ranit
( on Oracle 11.2.0.4.0 )