Re: Rollback problem, please help

I would attack this problem by writing a trace message from each logic branch so I can see exactly the flow of execution, and ensure that I am not making any false assumptions (i.e. is it actually executing the rollback?). In SQL*Plus, insert DBMS_OUTPUT.PUT_LINE statements. In Oracle Developer, TOAD and other GUI tools, you may be able to directly trace the execution using the PL/SQL step and trace tools.

When you say 'the information in the tables is still there', exactly how are you testing for that? If you are querying the table from the same session that executed the pl/sql code, you could be looking at uncommitted data. You should also query from a different session that will only show you the committed data.

Also a comment on your exception handling logic: you are testing for no_data_found after doing some updates. An update statement will never raise this exception. If a zero-row update is an error, you need to explicitly catch it in the block's body with IF SQL%ROWCOUNT = 0 THEN ....

Martin Doherty

Wasim wrote:

>Hello,
>
>I am having strange problem with rollback statement. My code looks
>like this
>
>Begin
> insert into some tables;
> Begin
> get a value from database;
> exception when no_data_found
> handle it;
> when others
> raise;
> end
> do some other stuff
> begin
> do some updates;
> exception when no_data_found
> handle it;
> when others
> then
> raise;
> end;
> commit;
>exception when others
> rollback;
> raise
>end;
>
>When an exception occurs its not rolling back the transaction (the
>information put in tables is still there). I don't know any reason why
>its doing that because the outer block should rollback the
>transaction.
>
>Regards,
>Wasim.
>
>
Received on Wed Nov 13 2002 - 14:15:14 CST