Re: pl/sql question?

In article <Pine.HPP.3.95.990922143420.28066A- 100000_at_penguin.creighton.edu>,
  <yliu_at_creighton.edu> wrote:
> Hi there,
>
> I am new to pl/sql. I have a generic pl/sql question. Usually, a
pl/sql
> block (or subprogram) has the following structure:
> DECLARE
> ...
> BEGIN
> ...
>
> EXCEPTION
> ...
> END;
> My understanding is when a exception happens, the control is passed
to the
> exception part of the pl/sql program, and is not returned to the
original
> block. So my question is: 'What happened to all the processing before
the
> exception occurs? Does this mean all the processing are rolled back?'
> I was told that always put a commit before the END of the pl/sql
program,
> but if a exception occurs, all the processing is uncommited even if I
have
> a commit right before the END of the pl/sql program, is this right?
What
> are the ways that I can use in pl/sql programs that will keep on
> processing the rest of data even though there is an exception?
>
> Thank you very much for your time and help.
>
> Yongge
> yliu_at_creighton.edu
>
>

IF you really want to commit, then you can put a commit in the exception also. I do want to point out though that you must be very clear in the understanding of the uses of your procedure or function. There are many cases where doing a commit in a procedure can be a bad thing.

As pointed out by another posted you can nest BEGIN/END blocks.

BEGIN
  <DO SOME WORK>

  BEGIN
    <WORK THAT MIGHT THROW AN EXCEPTION>   EXCEPTION
     <HANDLE IT>
  END;   <CLEAN UP WORK>

  COMMIT; --- HERE'S THE COMMIT YOU WANTED AFTER THE EXCEPTION   <EVEN MORE WORK? COULD BE A BIG LOOP>

EXCEPTION
  <HANDLE MAINLINE EXCEPTIONS OR ONES NOT CAUGHT BY THE NESTED BLOCK>

END; Just remember the implications of COMMIT;

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3700 <<<NOTE new number

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 23 1999 - 11:31:22 CDT