Forum Stats

  • 3,769,359 Users
  • 2,252,957 Discussions
  • 7,875,002 Comments

Discussions

Can we rollback before job changes if anyhow job fails?

User_6RR94
User_6RR94 Member Posts: 1 Green Ribbon
edited May 28, 2021 4:47AM in SQL & PL/SQL

Hello every one , I have one small question I am trying to run my procedure in  anonymous block in a loop in that procedure some jobs are running .once that procedure executed again my job is running in same block .

I want to rollback my all the procedure changes when job in main transaction failed. Is this possible.Can we rollback job changes .

For example

declare

begin

begin

For i in 1..3

loop

savepoint abc;

Proc1();//calling a procedure which inside calling another job

end loop;

end;

sys.dbms_job ();//run a job then


if above sys job = 'faliled' // want to rollback my all procedure changes

rollback to ABC;

end if;

end;

Regards,

Sam

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,594 Red Diamond

    No.

    A DBMS_JOB runs as a completely different db session. If it fails, and it does not catch the exception, the job queue manager will schedule it to be rerun up to 16 times - if it fails the 16th time, the job is marked as broken.

    DBMS_JOB interface should be used within a current transaction in order to execute code in batch when the transaction commits.

    For example, an e-mail notification needs to be send to customer when the shipping order transaction is committed.

    DBMS_JOB.Submit() is used to schedule the e-mail notification job as part of the shipping order transaction. When that transaction commits, the DBMS_JOB is committed and the job queue manager will execute the e-mail notification code. Should the shipping transaction be rolled back, the notification job is not committed and will not execute.

    Why run the e-mail notification code separately? The e-mail ONLY must be send AFTER the shipping transaction is committed. If it is send inline as part of te shipping transaction, then it will be send while the shipping transaction may be rolled back. So a customer will then get a shipping e-mail notification for s shipping order that was rolled back and did not occur.