Rollback Stored Procedure 1

[scohan]

In a stored procedure, if you update 2 tables in 2 separate update statements, if the first update succeeds but the second fails, will the first update be automatically rolled back, or would you have to set up a transaction and roll it back?

Thanks.

 

[SantaMufasa]

SCohan,

My motto is "One test is worth ten expert opinions," so here is my test, which shows that if the first update is successful, but the second update statement fails, Oracle rollsback the entire transaction (i.e., all previous DML statements back to the previous commit):

Section 1 -- Creation and initial population of two tables:

SQL> create table scohan1 (id number primary key);

Table created.

SQL> insert into scohan1 values (1);

1 row created.

SQL> create table scohan2 (id number primary key);

Table created.

SQL> insert into scohan2 values (1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from scohan1;

        ID
----------
         1

1 row selected.

SQL> select * from scohan2;

        ID
----------
         1

1 row selected.

Section 2 -- PL/SQL block that updates both tables, including failure on second update:

SQL> begin
  2   insert into scohan1 values(2);
  3   insert into scohan2 values(1);
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.SYS_C008958) violated
ORA-06512: at line 3


SQL> commit;

Commit complete.

Section 3 -- Confirmation that first, succssful DML in PL/SQL block rolls back if subsequent failure occurs without intervening COMMIT;

SQL> select * from scohan1;

        ID
----------
         1

1 row selected.

SQL> select * from scohan2;

        ID
----------
         1

1 row selected.

SQL>

Let me know if this satisfactorily resolves your question.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:08 (06Mar04) UTC (aka "GMT" and "Zulu"), 11:08 (06Mar04) Mountain Time)

 

[scohan]

Mufasa,

Very much so. You're response was crystal clear. Thanks much.

 

[scohan]

DOH. typo... your response