Re: help with commits and rollbacks in PL/SQL

On 14 Nov 1997 20:19:57 GMT, "Haresh Assumal" <assumal_at_sprynet.com> wrote:

>Hello,
>I have a question about exceptions in PL/SQL. I was under the impression
>that if an exception is raised a rollback is perfromed. However this is not
>the case. If I have a function a table called foo and a function called
>update foo as follows:
>
>create table foo(id number);
>
>create or replace function insert_foo
>return number
>
>is
> my_exception EXCEPTION;
>begin
> insert into foo values(1);
> raise my_exception;
> insert into foo values(2);
> return 0;
>exception
> when my_exception then
> return -1;
> when others then
> return -3;
>end;
>
>----
>When this function is called the data value 1 is inserted into foo, why
>doesn't is get rolled back ?
>Thanks,
>Haresh
>e-mail: assumal_at_sprynet.com

Extract from the PL/SQL Guide
Unhandled Exceptions
Remember, if it cannot find a handler for a raised exception, PL/SQL returns an unhandled exception error to the host environment, which determines the outcome. For example, in the Oracle Precompilers environment, any database changes made by a failed SQL statement or PL/SQL block are rolled back.
Unhandled exceptions can also affect subprograms. If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to OUTparameters.
Also, if a stored subprogram fails with an unhandled exception, PL/SQL does not roll back database work done by the subprogram. You can avoid unhandled exceptions by coding an OTHERS handler at the topmost level of every PL/SQL block and subprogram. Received on Fri Nov 14 1997 - 00:00:00 CST