Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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 ?
Because you handled the exception. pl/sql blocks/procedures will be rolled back IF the exception is propagated all the way up the call stack. If you catch and handle the exception, It is as if it never happened.
You might want to replace the
exception
when my_exception then
return -1;
when others then
return -3;
end;
with
exception
when my_exception then raise_application_error( -20001, 'My Exception Raised' ); when others then raise_application_error( -20003, sqlerrm );end;
that way
Or use savepoints:
begin
savepoint foo;
insert into x values ( 1 );
raise program_error;
exception
when program_error then
rollback to foo;
end;
/
>Thanks,
>Haresh
>e-mail: assumal_at_sprynet.com
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Nov 16 1997 - 00:00:00 CST