Re: pl/sql question?

From: <lutz_ebeling_at_my-deja.com>
Date: Mon, 27 Sep 1999 02:44:12 GMT
Message-ID: <7smllq$h5q$1@nnrp1.deja.com>

Hi,

I am also quite new to pl/sql, but may be I already can help you a little bit:

1st question: What happened to all the processing before the exception occurs?
=> the code between the place where the exception occurs (for ex. an insert statement) and the exception handler is not executed. You must think it as a "goto". Of course everything before this for ex. insert statement is executed normaly.

2nd question: Does this mean all the processing are rolled back? No. Why should it when it passes no rollback statement ? Think again as a goto. For example

code normaly executed
insert .... -- which doesn't work, i.e. it --"throws" the exception code not executed
EXCEPTION
   WHEN OTHERS
      code executed

3r question: 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? Yes, because the "commit" statement is not executed, see example below

code executed
insert... -- which throws exeception
here you should place COMMIT
EXCEPTION
   WHEN OTHERS THEN
      here you should place ROLLBACK
END;--test procedure or function

4th question
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?

place your exception handler always directly behind the sql statement, for example

procedure test IS
BEGIN code executed
BEGIN
   insert ...throws exception
   EXCEPTION

      WHEN OTHERS THEN
          error code handling

END
code executed

---

Hope I helped you. Have fun. Perhaps you can help me to find a good single step source code debugger for pl/sql ?

regards,

Lutz

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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Sep 26 1999 - 21:44:12 CDT