1

I have procedure A with PRAGMA AUTONOMOUS_TRANSACTION to log the activity of a main program.

Now I am calling this program A in a procedure B to log the activity process in program B. If any error occurs in program A it fails the program B also. How I can avoid failing main program B.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
Vikas
  • 107
  • 7
  • Transactions and exceptions are two different things, if that’s what you are asking. A runtime error doesn’t stop being an error just because you declared something as `pragma autonomous transaction`. – William Robertson Feb 26 '19 at 23:27

1 Answers1

3

What does that logging procedure do so that it might fail? Is it not a pure INSERT + COMMIT?

Anyway: the simplest (and probably the worst) option is to use exception handler section, e.g.

your_procedure is
begin
  do something;
exception
  when others then null;
end;

It would be OK if you really don't care whether something bad happened or not, but - what's the purpose, then? You think you did something, Oracle doesn't complain, the procedure does nothing and you have no idea what's going on.

Therefore, you'd better make sure that this "logging" procedure doesn't fail.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • My question is PRAGMA AUTONOMOUS_TRANSACTION is independent to the main program then why the Main program is getting impacted if program A is fail. – Vikas Feb 25 '19 at 21:15
  • 1
    It is independent in a way that commit or rollback issued in such a procedure doesn't affect the main trasaction. It doesn't mean that error raised within the autonomous transaction procedure will be silently neglected. Nope. – Littlefoot Feb 25 '19 at 21:34