Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: exception handling

    I'm having a tough time wrapping my mind around 'good' exception handling. I've taken the PL/SQL course, and read about it in the O'Reilly PL/SQL book, but I'm still having a rough time.

    Does anyone have a good link, or words of advice?

    I'm mainly confused about:
    Are there standard exceptions people put in most of their procedures?
    How far do you take it, when you're setting up custom exceptions (seems like you could plan for quite a few situations).
    What the difference between RAISE and RAISE_APPLICATION_ERROR?

    Thanks,
    Chuck

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Perhaps this is a better question:

    What is the hazard in starting every PACKAGE with:

    EXCEPTION
    WHEN OTHERS THEN
    RAISE

    or

    EXCEPTION
    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR

    and then adding additional exceptions on a package-by-package basis as your users encounter them?

    -Chuck

  3. #3
    Join Date
    Jan 2004
    Posts
    492
    You should always have at least WHEN OTHERS in an exception block just to catch unexpected errors. You dont need to go overboard with the built-in exceptions.

    If you are selecting into a variable, you may want to put a no data found error. If you are inserting into a table, you would want a dup val on index one to tell you that record already exists.

    If you use utl_file at all, there are a slew of handlers you should use. Sometimes the OTHERS error won't give you a great clue to what is going on, whereas a more specific handler can help you debug better.

    But every procedure and function should have at the least an OTHERS exception to answer your original questions. You can also define user errors based on conditions.

    EX:
    Code:
    select count(*)
    into v_count
    from some_table
    where conditions;
    
    if v_count = 0 then
    raise e_no_recs;
    
    else ...more processing
    
    EXCEPTION
    when e_no_recs then
    dbms_output.put_line ('No Records Were Found');
    
    END;
    Last edited by ss659; 02-25-05 at 16:43.
    Oracle OCPI (Certified Practicing Idiot)

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Just to re-iterate what ss659 said, in all my years of programming the best maxim that I came across was "If it can go wrong, it will go wrong", so ALWAYS use an OTHERS exception.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I disagree totally with the last two posts! Only handle exceptions that you anticipate, and when you intend to do something useful when you handle it. Any exception you do not handle will then be raised as an Oracle error to the calling program. By contrast, poor and exception handling like this...
    Code:
    exception
      when others then
        dbms_output.put_line ('Unexpected error: '||SQLERMM);
    end;
    ...does nothing more than hide the exception from the calling program, leading to errors. As far as the calling program is concerned, no exception has been raised above.

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    There is also the option to re-raise the exception back to the calling program (next outer exception block or calling procedure/function etc).

    Code:
    exception 
       when taxes_rise then
          ... inevitable, deal with it
       when pub_shuts then
          ... throw tantrum
       when others then
          dbms_output.put_line('unhandled');
          RAISE;
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    andrewst, whilst I agree with your statement, speaking from a designer's (& a programmer's) point of view, I would rather have an OTHERS exception, with a meaningful (but generic) message. This is for my peace of mind, in that I know that I have catered for every eventuality (albeit by default). Although Oracle does cater for unexpected errors, not all programming languages do; rather than wondering whether or not the language I'm currently designing or programming for does cater for this it's easier to have the OTHERS exception (or its 'other languages' equivalent).

    If I was dealing solely with Oracle then I'd agree with you, but being a crusty old git with far too much exposure to far too many languages I prefer to pay safe - hence my maxim.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  8. #8
    Join Date
    Jan 2004
    Posts
    492
    I was under the notion that at least the outer most block should have an others handler. If no others handler is there, won't an unhandled exception occur? This is what Oracle says in there docs, and I guess this is what I was basing my thought on.


    Unhandled exceptions can also affect subprograms. If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to OUT parameters (unless they are NOCOPY parameters). Also, if a stored subprogram fails with an unhandled exception, PL/SQL does not roll back database work done by the subprogram.

    You can avoid unhandled exceptions by coding an OTHERS handler at the topmost level of every PL/SQL program.


    PL/SQL is still a work in progress for me, so I may be misunderstanding it.
    Oracle OCPI (Certified Practicing Idiot)

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by ss659
    I was under the notion that at least the outer most block should have an others handler. If no others handler is there, won't an unhandled exception occur? This is what Oracle says in there docs, and I guess this is what I was basing my thought on.
    If no others handler is there then the exception will be raised up to the calling program as an Oracle error like:
    ORA-01403: no data found
    It does not just disappear, as if it never happened; that only happens if you do handle it, but badly e.g. "WHEN OTHERS THEN NULL;" or "WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM);" (with no RAISE).

    Quote Originally Posted by ss659
    Unhandled exceptions can also affect subprograms. If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to OUT parameters (unless they are NOCOPY parameters).
    That is correct behaviour, because if the subprogram raises an exception then it means something went wrong, and so value from the calling program should not be modified.

    Quote Originally Posted by ss659
    Also, if a stored subprogram fails with an unhandled exception, PL/SQL does not roll back database work done by the subprogram.
    Not so:
    Code:
    SQL> create table t (id int primary key);
    
    Table created.
    
    SQL> create procedure ins (p_id in int) is
      2  begin
      3    insert into t values (p_id);
      4    insert into t values (p_id);
      5  end;
      6  /
    
    Procedure created.
    
    SQL> begin
      2    ins(1);
      3  end;
      4  /
    begin
    *
    ERROR at line 1:
    ORA-00001: unique constraint (TANDREWS.SYS_C00168657) violated
    ORA-06512: at "TANDREWS.INS", line 4
    ORA-06512: at line 2
    
    
    SQL> select * from t;
    
    no rows selected
    Quote Originally Posted by ss659
    You can avoid unhandled exceptions by coding an OTHERS handler at the topmost level of every PL/SQL program.
    Yes, but only do so if it actually adds value, e.g. to re-format the error in a more user-friendly way, or to log it to a table.

    Quote Originally Posted by ss659
    PL/SQL is still a work in progress for me, so I may be misunderstanding it.
    And exception handling is a confusing topic!

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    Thanks for the followup - that line about rolling the work back from the subprogram came from their documentation, so apparently its a bit off.
    Oracle OCPI (Certified Practicing Idiot)

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Can you give a link to the relevant docs that say that? I'm not sure whether the docs are wrong, or whether they mean something subtly different.

  12. #12
    Join Date
    Jan 2004
    Posts
    492
    http://download-west.oracle.com/docs...htm#sthref1266

    Maybe its taken out of context, but thats what I was basing my posts on.
    Oracle OCPI (Certified Practicing Idiot)

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Well, may be in Tony's example it's SQLplus that is rolling back the changes. I think the documentation suggests that at the time when control is returned to the caller of a failed subprocedure the changes haven't been rolled back, and it is the responsibility of the caller to choose the best way to deal with the situation.

  14. #14
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I think it's unlikely to be SqlPlus doing the rollback - lets be honest, in this scenario the client app really shouldn't be messing with or assuming the authority to roll back or not.

    I just tried this in TOAD

    Code:
    create table test1 (id number(1) primary key);
    
    create table test2 (id number(1) primary key);
    
    create table test3 (id number(1) primary key);
    
    create or replace procedure ins_test1 is 
    begin
       insert into test1 values (1);
       insert into test1 values (1);
    end;
    /
    create or replace procedure ins_test2 is
    begin
       insert into test2 values (1);
       insert into test2 values (1);
    exception when others then
       raise;   
    end;
    /
    create or replace procedure ins_test3 is
    begin
       insert into test3 values (1);
       insert into test3 values (1);
    exception when others then
       null;
    end;
    /
    begin
       ins_test1;
    end;
    /
    begin
       ins_test2;
    end;
    /
    begin
       ins_test3;
    end;
    /
    select * from test1;
    no rows
    
    select * from test2;
    no rows
    
    select * from test3;
    id
    ==
    1
    The only difference to the test3 result was the exception when others then null which essentially says to oracle "ignore any exceptions".

    I think Oracle handles PL/SQL exceptions in a pretty consistent manner, consistent with what you would expect and what you can control.

    Oracle thinks...
    "I've got some code running with no exception handling. User must be daft, I'll roll back to be safe".

    or

    Oracle thinks
    "The user has caught and done something with the exceptions. Maybe I should trust them".

    That's generally in line with most other languages I work or have worked with.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by billm
    I think it's unlikely to be SqlPlus doing the rollback
    How do you explain this then?
    Code:
    SQL> edit
    Wrote file afiedt.buf
    
      1  declare v int;
      2  begin
      3  ins1;
      4  exception
      5  when others then
      6  select count(*) into v from test;
      7  dbms_output.put_line('count='||to_char(v));
      8  raise;
      9* end;
    SQL> /
    count=1
    declare v int;
    *
    ERROR at line 1:
    ORA-00001: unique constraint (NICK.SYS_C001453) violated
    ORA-06512: at line 8
    
    
    SQL> select count(*) from test
      2  /
    
      COUNT(*)
    ----------
             0
    While we are in the exception handler there is still one row in the table; there has been no rollback at this point. However, we decide not to do anything and fall through to SQLplus instead. SQLplus has its own exception handler, which in addition to printing wonderful error messages for us also rolls back the transaction, which is indicated by the fact that no records can be found in the table anymore.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •