Forum Stats

  • 3,815,999 Users
  • 2,259,128 Discussions
  • 7,893,360 Comments

Discussions

Procedure Calling one more Procedure

PreethiMR
PreethiMR Member Posts: 37
edited Feb 2, 2010 7:47AM in SQL & PL/SQL
Hi All ,

I have Proc A and Proc B

Proc B contains insert statement.
Proc A Calls Proc B and after that Update statement .

My query is if Proc A fails due to update stmt ,The insert statement in Proc B should get rollback(If proc A fails everything else should be rollback) .Please help ...Iam using Oracle 10g

Thanks in advance ..
Preethi MR
Tagged:

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Unless you dont issue a COMMIT inside ProcB thats how oracle will work. Every thing will be ROLLBACK. You dont have to do any thing special to achieve it.
  • 6363
    6363 Member Posts: 6,642
    edited Feb 2, 2010 7:47AM
    user1039461 wrote:

    My query is if Proc A fails due to update stmt ,The insert statement in Proc B should get rollback(If proc A fails everything else should be rollback)
    You don't say why the update statement would fail. If there is an exception everything will roll back unless -

    - There is a commit in procedure A when there shouldn't be
    - The exception is caught in procedure A when it shouldn't be

    If you mean the update modifies no rows, which is not failure, you would have to test the sql%rowcount variable and if it is zero raise your own exception.

    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/static.htm#LNPLS529

    http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/raise_statement.htm#LNPLS01337
This discussion has been closed.