0

Is following statement from doc: valid?

If you exit a stored subprogram with an unhandled exception, PL/SQL does not assign values to OUT parameters, and does not do any rollback

As per the statement ROLLBACK should not happen when my stored procedure testy raise an exception unhandled. And as per document insert 3, insert 2 should be successful, but no insert are successful. 

create table mytable (num int not null primary key);

insert into mytable values(1);

create or replace procedure testy is
begin
insert into mytable values(2);
insert into mytable values(1); //throws error: ORA-00001: unique constraint (SRISRI1.SYS_C0011447) violated
end;

create or replace procedure testp is
begin
insert into mytable values(3);
testy;
insert into mytable values(4);
end;

exec testp; 

select * from mytable;

mytable
_______
1
sql_dummy
  • 715
  • 8
  • 23

1 Answers1

0

We've discussed it in your previous topic, haven't we?

William Robertson said:

You can think of the whole anonymous block as behaving like a single DML statement. If it fails, it rolls back to its own start, just like an update etc.

  • you inserted value "1" via INSERT INTO statement
  • then you called TESTP which
    • inserted "3"
    • called TESTY which
    • inserted "2"
    • tried to insert "1" but failed

Oracle performed an implicit rollback and reverted INSERT 2 and INSERT 3, which returns you back to the starting point, i.e. to a situation you've had before executing the TESTP procedure, and that is value 1 in the table.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • no, I mean here my question is different. As per doc "does not do any rollback" in case of "stored subprogram with an unhandled exception". But there is roll back performed in actual. – sql_dummy Jan 04 '18 at 12:33
  • 1
    Not *explicitly*, but - it wouldn't do any harm if you actually read what you see. Oracle performed an *implicit* rollback as PL/SQL block failed. – Littlefoot Jan 04 '18 at 12:34
  • All the handling performed by Oracle would be called as implicit right? If not how is Oracle explicitly handling any different? – sql_dummy Jan 04 '18 at 13:06
  • It is you (a developer) who *explicitly* does something. For example, when you run ALTER TABLE statement, Oracle will *implicitly* commit any previous changes. When you INSERT INTO, UPDATE and DELETE and COMMIT at the end, then you *explicitly* ordered Oracle to save changes, i.e. *commit*. – Littlefoot Jan 04 '18 at 13:14
  • okay but oracle does not do any rollback in this case, neither did I. Then why are these rolled back; – sql_dummy Jan 04 '18 at 13:22
  • How do you mean, Oracle doesn't rollback? It does, as soon as you hit a TESTY's INSERT INTO statement that tries to insert yet another "1" into a column constrained by primary key constraint. – Littlefoot Jan 04 '18 at 15:15
  • I got the sol [here](https://stackoverflow.com/questions/25666826/pl-sql-contradiction-in-oracle-document-on-implicit-rollbacks?rq=1) – sql_dummy Jan 04 '18 at 16:17
  • What it says if you call the procedure(raising exception (unhandled)) in anonymous block and catch exception (without applying rollback). The other inserts are not rolled back. So that break downs if exception left unhandled no rollback at the procedure but the roll back happens at anonymous block – sql_dummy Jan 04 '18 at 16:38