How to handle exception in oracle package?

Question

I have a oracle package consisting of many procedures. e.g.

pkg(
proc 1
proc 2
proc 3
);

while executing the package proc 1 gets called first and within proc 1,proc 2 is called. SO what if i face an exception in proc 2 then i want to rollback all the DML's done in proc 1.

you can use rollback with savepoint by which you can rollback to current transaction — Kandy, Jan 21 '19 at 07:47
please clear these: 1-**Proc1** calls **Proc2**? 2-**Proc2** does some DML? — hmmftg, Jan 21 '19 at 08:02

score 1 · Answer 1 · answered Jan 21 '19 at 08:30

You don't have to do anything, Oracle will perform rollback for you. Just don't commit anywhere within those procedures - let the caller decide whether to commit or not, after everything is done.

Also, don't DDL as it'll implicitly commit everything that has been done so far.

score 0 · Answer 2 · answered Jan 22 '19 at 17:32

0

Adding to what Littlefoot mentioned, please please have in consideration whatever IDE you are working with. There are some settings of auto-commit that I recommend to you that should be turn off. Otherwise, rollbacks won't help.

answered Jan 22 '19 at 17:32

Pedro Orellana Manzano

23
6

How to handle exception in oracle package?

2 Answers2