Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: help with commits and rollbacks in PL/SQL

Re: help with commits and rollbacks in PL/SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/11/16
Message-ID: <34764016.64812445@newshost>#1/1

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  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US