This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Feb 7, 2013 3:52 AM by 978878 Go to original post RSS
  • 15. Re: PL/SQL 101 : Exception Handling
    BluShadow Guru Moderator
    Currently Being Moderated
    Hi Rob,
    One suggestion though: can you edit some code
    examples to make the lines less long. Now I have to
    scroll when reading.
    Now edited. Hopefully that's a little better? (if not, get yourself a larger screen. hehe!)
  • 16. Re: PL/SQL 101 : Exception Handling
    561093 Oracle ACE
    Currently Being Moderated
    Good work Blu.

    Hope Oracle community takes advantage of your hard work.
  • 17. Re: PL/SQL 101 : Exception Handling
    Karthick_Arp Guru
    Currently Being Moderated
    This is a great post... But still i would like to ask some thing.

    You said...

    c) we don't recognise the exception and we want the calling code to deal with it

    Example of c)
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    v_name VARCHAR2(20);
      3    v_empno NUMBER := &empno;
      4  begin
      5    select ename
      6    into   v_name
      7    from   emp
      8    where  empno = v_empno;
      9    dbms_output.put_line(v_name);
     10  EXCEPTION
     11    WHEN no_data_found THEN
     12      INSERT INTO sql_errors (txt)
     13      VALUES ('Search for '||v_empno||' failed.');
     14      COMMIT;
     15      RAISE;
     16    WHEN others THEN
     17      RAISE;
     18* end;
    SQL> /
    Enter value for empno: 'ABC'
    old   3:   v_empno NUMBER := &empno;
    new   3:   v_empno NUMBER := 'ABC';
    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 3
    As you can see from the sql_errors log table, no log was written so the WHEN others exception
    was the exception that raised the error to the calling code (SQL*Plus)

    I would like to ask...

    Why would i want to have a WHEN OTHERS block just to do a RAISE. If feel its just an extra code. The same thing
    is achieved just be not doing any thing.
    SQL> declare
      2    v_name VARCHAR2(20);
      3    v_empno NUMBER := &empno;
      4  begin
      5    select ename
      6    into   v_name
      7    from   emp
      8    where  empno = v_empno;
      9    dbms_output.put_line(v_name);
     10  EXCEPTION
     11    WHEN no_data_found THEN
     12      INSERT INTO sql_errors (txt)
     13      VALUES ('Search for '||v_empno||' failed.');
     14      COMMIT;
     15      RAISE;
     16  end;
     17  /
    Enter value for empno: 'a'
    old   3:   v_empno NUMBER := &empno;
    new   3:   v_empno NUMBER := 'a';
    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 3
    What is the purpose of WHEN OTHERS here? There is not even any logging carried out. Please correct me if iam not looking it the right way.

    Thank you,
    Karthick.
  • 18. Re: PL/SQL 101 : Exception Handling
    Karthick_Arp Guru
    Currently Being Moderated
    As you can see from the sql_errors log table, no log was written so the WHEN others exception
    was the exception that raised the error to the calling code (SQL*Plus)
    With further testing i found that actually WHEN OTHERS is not the one that raised the error.
    SQL> declare
      2    v_name VARCHAR2(20);
      3    v_empno NUMBER := &empno;
      4  begin
      5    select ename
      6    into   v_name
      7    from   emp
      8    where  empno = v_empno;
      9    dbms_output.put_line(v_name);
     10  EXCEPTION
     11    WHEN no_data_found THEN
     12      INSERT INTO sql_errors (txt)
     13      VALUES ('Search for '||v_empno||' failed.');
     14      COMMIT;
     15      RAISE;
     16    WHEN others THEN
     17      DBMS_OUTPUT.PUT_LINE('Error into when others');
     18      RAISE;
     19  end;
     20  /
    Enter value for empno: 'a'
    old   3:   v_empno NUMBER := &empno;
    new   3:   v_empno NUMBER := 'a';
    declare
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 3
    I dont get any dbms_output message.

    Here you can see the line number of the exception is shown as 3. But if the exception was raised by the RAISE
    statement in WHEN OTHERS then the line number must have been 18. See below.
    SQL> declare
      2     a number := 1;
      3  begin
      4     select 1 into a from dual where 1=2;
      5  exception
      6     when others then
      7             dbms_output.put_line('Error into when others');
      8             raise;
      9  end;
     10  /
    Error into when others
    declare
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 8
    Here the error was actually raised at line 4. But the WHEN OTHERS has handled the error and raised it back the line
    numbe of the error is shown as 8.

    Thanks,
    Karthick.
  • 19. Re: PL/SQL 101 : Exception Handling
    BluShadow Guru Moderator
    Currently Being Moderated
    karthick_arp wrote:
    This is a great post... But still i would like to ask some thing.

    I would like to ask...

    Why would i want to have a WHEN OTHERS block just to do a RAISE. If feel its just an extra code. The same thing
    is achieved just be not doing any thing.
    ...
    What is the purpose of WHEN OTHERS here? There is not even any logging carried out. Please correct me if iam not looking it the right way.
    a) It didn't have to be a when others, it could have been trapping a specific exception, that was just an example. I did say at the beginning of the article that the code examples where for the structure, so the fact it doesn't do any logging or anything useful is not important. However...

    b) you are correct that this example doesn't even trap and raise the exception taking place. that is because the exception is in the declaration section. Thanks for highlighting my mistake, I'll go and change the example to something a little better.
  • 20. Re: PL/SQL 101 : Exception Handling
    Solomon Yakobson Guru
    Currently Being Moderated
    karthick_arp wrote:
    Why would i want to have a WHEN OTHERS block just to do a RAISE. If feel its just an extra code. The same thing
    is achieved just be not doing any thing.
    Actually, "not doing any thing" would be better in this case, since reraising results, as BluShadow already pointed out, in losing actual error line number. Now as exception handling 101, suggestion not to use WHEN OTHERS is a good one. It forms a good habit. Later, when 101 is mastered, one will come to realize there are cases where WHEN OTHERS is justified. For example. Your code opens a cursor. What if an exception is raised? Cursor will remain open. So we are forced to write WHEN OTHERS handler which check if cursor is open and close it. Same applies to file handles (e.g. UTL_FILE). And since, as I already pointed out, we lose actual error line number, it is always a good idea to use DBMS_UTILITY.FORMAT_ERROR_BACKTRACE to display/save original line number.One more thing. Error handling and packages. Error handling and package initialization block. When package is referenced by a session for the first time Oracle will:

    a) instantiate package
    b) execute package initialization block

    and only then execute package reference. Assume an error was raised in package initialization block. This means the rest of package initialization block is not executed. However package is instantiated. And since package initialization block is executed only once, consecutive package calls might work but produce wrong results. For example:
    SQL> create or replace
    2 package pkg1
    3 is
    4 v_n number;
    5 v_s varchar2(1);
    6 v_d date;
    7 end;
    8 /
    
    Package created.
    
    SQL> create or replace
    2 package body pkg1
    3 is
    4 begin
    5 v_s := 'XX';
    6 v_n := 0;
    7 v_d := sysdate;
    8 end;
    9 /
    
    Package body created.
    
    SQL> set serveroutput on
    SQL> exec dbms_output.put_line('');
    BEGIN dbms_output.put_line(pkg1.v_s); END;
    
    *
    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character string buffer too small
    ORA-06512: at "SCOTT.PKG1", line 4
    ORA-06512: at line 1
    
    
    SQL> exec dbms_output.put_line('
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_output.put_line('
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_output.put_line('
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    {code}
    SY.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 21. Re: PL/SQL 101 : Exception Handling
    BluShadow Guru Moderator
    Currently Being Moderated
    Hi Solomon,
    Solomon Yakobson wrote:

    For example. Your code opens a cursor. What if an exception is raised? Cursor will remain open. So we are forced to write WHEN OTHERS handler which check if cursor is open and close it. Same applies to file handles (e.g. UTL_FILE).
    I think "forced" is perhaps a little strong. I would say that it's good practice to ensure the cursors are closed when exceptions happen, however the PL/SQL engine has the ability to do garbage collection on open cursors when the cursor goes out of scope. Of course this doesn't guarantee that the cursor is closed immediately it goes out of scope, just that it is likely to be at some point in time. As for file handles, I agree, definitely a good idea to close those as I'm not sure how reliable garbage collection is on those and I'm sure there will be some differences dependant on the operating system and it's willingness to release the underlying operating system file handles.

    Good addition about the packages. ;)
  • 22. Re: PL/SQL 101 : Exception Handling
    Karthick_Arp Guru
    Currently Being Moderated
    I always felt a FINALLY block would be a nice addition to PL/SQL

    Thanks,
    Karthick.
  • 23. Re: PL/SQL 101 : Exception Handling
    user13328581 Explorer
    Currently Being Moderated
    nice article
  • 24. Re: PL/SQL 101 : Exception Handling
    978878 Newbie
    Currently Being Moderated
    Thanks for the link, it is very helpful to know the behavior of exceptions.
1 2 Previous Next