Run Oracle select query as a cron job ? Oracle - force commit distributed transactions

user_12997859 0
Viewed 51

I would like to setup a cron job to execute the result of my select query output,

schedule this "force commit" every day before my export job... without running it manually.

Precisely the below SQL Query & its outcome to be automated ..

1)
select 'commit force '''||local_tran_id||''';' from DBA_2PC_PENDING where state='prepared';                    

'COMMITFORCE'''||LOCAL_TRAN_ID||''';'
--------------------------------------
commit force '5.20.61510';
commit force '15.4.1124117';


SQL> commit force '5.20.61510';
commit force '15.4.1124117';
Commit complete.
scheduled-tasks cron oracle sql
1 Answers

Just make a simple procedure with some dynamic plsql:

CREATE OR REPLACE PROCEDURE commit_prepared
AS
BEGIN
  FOR local_transaction IN (SELECT local_tran_id AS trans_id
                              FROM DBA_2PC_PENDING 
                             WHERE state = 'prepared')
  LOOP
    EXECUTE IMMEDIATE 'COMMIT FORCE '||local_transaction.trans_id;
  END LOOP;
END;
/
user_48918150
Related