Re: Dynamic tables in PL/SQL proc

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 06 Mar 1999 12:36:02 GMT
Message-ID: <36e71f8f.5008702@192.86.155.100>

A copy of this was sent to "Arjan van Bentem" <avbentem_at_DONT-YOU-DAREdds.nl> (if that email address didn't require changing) On Sat, 6 Mar 1999 11:50:00 +0100, you wrote:

>Thomas Kyte wrote
>>no, you don't need the exception handlers. a procedure is treated as
>>an atomic statement -- it either SUCCEEDS and all statements
>>succeed or it fails (and all statements fail).
>
>Thomas, thanks again!
>
>I got a bit worried about my knowledge. However, I have come up with an excuse
>that I'd like to share here :-)) Being a bit more awake now, I actually did
>know that a procedure is treated as a single statement. I got confused as I
>almost always have an exception handler. I know you'll know, but just for the
>sake of www.dejanews.com :
>
>If one sets up an exception handler, one should also rollback any transactions
>within that block (if needed, of course), or re-raise the error. For example:
>

Yes, i did not cover this case since

• using a when others is the worst possible thing ever in the world to do. I truely wish we did not support it. It just masks errors. I've seen more then one program behave erratically when someone catches a "when others" breaking the logic...
• catching an exception and not re-raising it implies the error wasn't an error to begin with but the 'exception to the rule' (you expected it to maybe happen and you have a way to deal with it). for example

      ...
      delete from x where ....;
      begin
         insert into t values ( a, b, c );
      exception
         when dup_val_on_index then
             update t set c2 = b, c3 = c
              where c1 = a;
      end;
      .....

so, here the insert is expected to 'fail' and in the event it does fail with a dup, we do an update.

> create function my_function
> return varchar2
> as
> begin
> savepoint my_savepoint;
> delete from emp;
>
> select 1/0
> from dual; -- force exception
>
> return 'All rows deleted';
> exception
> when others then
> rollback to my_savepoint
> return 'No rows deleted';
> end;
>
>In this function, it would be wrong to ommit the rollback, unless returning
>'No rows deleted' is simply replaced with raise.
>

right, simply because you decided the error was *not* an error by having the exception block with a when others.

>Arjan.
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Mar 06 1999 - 06:36:02 CST