exception in stored procedure

Question

I have following procedure:

procedure mayFailProc() as
begin
    insert into t1 (id, val) values (1, '123');
    insert into t1 (id, val) values (2, '123');
    insert into t1 (id, val) values (3, '123'); //fails, i.e. due to pk uniqueness error
end;

this exception thrown in mayFailProc is a normal thing and it is handled by its caller. So the transaction is not rolled back and execution continues as if there was no exception in mayFailProc. I wonder what will happen to first two successfully executed inserts? Will they be retained or not?

Answer 1

See here for Oracle's explanation. You can jump to the How Oracle Does Implicit Rollbacks section to start.

Before executing an INSERT, UPDATE, or DELETE statement, Oracle marks an implicit savepoint (unavailable to you). If the statement fails, Oracle rolls back to the savepoint. Usually, just the failed SQL statement is rolled back, not the whole transaction. If the statement raises an unhandled exception, the host environment determines what is rolled back.

More:

You should explicitly commit or roll back every transaction. Whether you issue the commit or rollback in your PL/SQL program or from a client program depends on the application logic. If you do not commit or roll back a transaction explicitly, the client environment determines its final state.

For example, in the SQL*Plus environment, if your PL/SQL block does not include a COMMIT or ROLLBACK statement, the final state of your transaction depends on what you do after running the block. If you execute a data definition, data control, or COMMIT statement or if you issue the EXIT, DISCONNECT, or QUIT command, Oracle commits the transaction. If you execute a ROLLBACK statement or abort the SQL*Plus session, Oracle rolls back the transaction.

Answer 2

Just done a quick test... the rows in my test were not retained when the third insert caused a "unique constraint ... violated" error