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

    Unanswered: Exception Handling

    Here's something that surprised me. I was playing around some more with EXCEPTION handling and using the WHEN OTHERS clause. Up until today, I thought every exception handler, when it existed in your code, had to have a WHEN OTHERS clause:

    Code:
    declare
      num number;
    begin
      begin
        select 1 into num from (select 1 from dual union all select 1 from dual);
      exception 
        WHEN TOO_MANY_ROWS THEN
          null;
        WHEN OTHERS THEN
          raise;
      end;
    end;
    And when I started playing around the code it was working as expected:

    Code:
    SQL> create table test_rollback (f1 varchar2(50));
    
    Table created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    num number;
      3  begin
      4    begin
      5      insert into test_rollback values ('roll me back');
      6      select 1 into num from (select 1 from dual union all select 1 from dual);
      7      dbms_output.put_line('no error');
      8    exception
      9      WHEN TOO_MANY_ROWS THEN
     10        null;
     11    end;
     12* end;
    SQL> /
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from test_rollback;
    
    F1
    --------------------------------------------------
    roll me back
    but when I forced another error, and omitted the WHEN OTHERS clause, I received a rollback in the code. Has it always worked this way?

    Code:
    SQL> rollback;
    
    Rollback complete.
    
    SQL> select * from test_rollback;
    
    no rows selected
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  declare
      2    num number;
      3  begin
      4    begin
      5      insert into test_rollback values ('roll me back');
      6      num := 1/0;
      7      select 1 into num from (select 1 from dual union all select 1 from dual);
      8    exception
      9      WHEN TOO_MANY_ROWS THEN
     10        null;
     11    end;
     12* end;
    SQL> /
    declare
    *
    ERROR at line 1:
    ORA-01476: divisor is equal to zero
    ORA-06512: at line 6
    
    
    SQL> select * from test_rollback;
    
    no rows selected
    -Chuck

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Dont know what to tell -- I just see a block that completed successfully vs a block that didn't and exited abnormally due to an error and thus rolled back any pending transactions it had. I believe that's the expected behaivour.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, but Chuck was under the same illusion that I was many years ago, that if you handle any exceptions then you must handle them all or the others will get "lost" somehow. I used to litter my code with unnecessary "WHEN OTHERS THEN RAISE;" statements because I believed that!

    I think when I was an Oracle newbie I was instructed to do that by someone else, who probably mistook "unhandled" as meaning "un-raised".

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    You're correct andrewst. I was just making sure that I was just mis-informed, rather than confused.

    Guess I'll start removing all those WHEN OTHERS THEN RAISE

    -Chuck

  5. #5
    Join Date
    Sep 2004
    Posts
    60
    Chuck / Andrewst ,

    It was good discussion.
    Can we summarize it in this way:
    ' In code you one should handle all the exceptions that can come due to code & not all the unseen exception.'

    Please correct my understanding, if it is wrong.

    Thanks..
    AD

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    This is very interesting : it seems like if an exception is raised to the external (non PL/SQL) program then there is a rollback, but I can't find that in the PL/SQL documentation... I'd be glad if someone could point it to me.

    However, beware of subprograms called from PL/SQL : here there is NO rollback.
    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.
    This little example emphasizes this :

    Code:
    rbaraer@Ora10g> drop table test_rollback;
    
    Table dropped.
    
    rbaraer@Ora10g> create table test_rollback (f1 varchar2(50));
    
    Table created.
    
    rbaraer@Ora10g> create or replace procedure fct_test_rollback as
       num number;
    begin
         insert into test_rollback values ('roll me back');
         num := 1/0;
         select 1 into num from (select 1 from dual union all select 1 from dual);
    end fct_test_rollback;
    /  2    3    4    5    6    7    8
    
    Procedure created.
    
    rbaraer@Ora10g> create or replace procedure fct_call_test_rollback as
       num number;
      2  begin
      3    4      begin
      5          fct_test_rollback();
      6      exception
      7          when others then
      8              select count(*) into num
      9              from test_rollback;
     10              DBMS_OUTPUT.PUT_LINE('After fct_test_rollback count = '||num);
     11              raise;
     12      end;
     13  end fct_call_test_rollback;
     14  /
    
    Procedure created.
    
    rbaraer@Ora10g> execute fct_call_test_rollback();
    After fct_test_rollback count = 1
    BEGIN fct_call_test_rollback(); END;
    
    *
    ERROR at line 1:
    ORA-01476: divisor is equal to zero
    ORA-06512: at "RBARAER.FCT_CALL_TEST_ROLLBACK", line 11
    ORA-06512: at line 1
    
    
    rbaraer@Ora10g> select count(*) from test_rollback;
    
      COUNT(*)
    ----------
             0
    
    rbaraer@Ora10g>
    The insert is effective as long as you are in the PL/SQL context but seems to be rolled back automatically when the exception is raised to SQL*Plus.

    What bothers me too is :
    Remember, if it cannot find a handler for a raised exception, PL/SQL returns an unhandled exception error to the host environment, which determines the outcome. For example, in the Oracle Precompilers environment, any database changes made by a failed SQL statement or PL/SQL block are rolled back.
    They take the example of PreCompiler, but what of other programmatical environments ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Ok, maybe we can clear up another misconception of mine then:

    I thought that the "raise;" in your WHEN OTHERS clause is what caused the rollback. When I execute your series of statements, and comment that out, the single row remains:

    Code:
    SQL> drop table test_rollback;
    
    Table dropped.
    
    SQL> create table test_rollback (f1 varchar2(50));
    
    Table created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace procedure fct_test_rollback as
      2     num number;
      3  begin
      4       insert into test_rollback values ('roll me back');
      5       num := 1/0;
      6       select 1 into num from (select 1 from dual union all select 1 from dual);
      7* end fct_test_rollback;
    SQL> /
    
    Procedure created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace procedure fct_call_test_rollback as
      2     num number;
      3    begin
      4        begin
      5            fct_test_rollback();
      6        exception
      7            when others then
      8                select count(*) into num
      9                from test_rollback;
     10                DBMS_OUTPUT.PUT_LINE('After fct_test_rollback count = '||num);
     11                --raise;
     12        end;
     13* end fct_call_test_rollback;
    SQL> /
    
    Procedure created.
    
    SQL> execute fct_call_test_rollback();
    After fct_test_rollback count = 1
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from test_rollback;
    
    F1
    --------------------------------------------------
    roll me back
    -cf

  8. #8
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    When all PLSQL exceptions are handled (wherever in PLSQL code they are handled) there is no implicit rollback, that's why commenting the RAISE ends up in no rollback : from a PLSQL point of view everything went fine since all exceptions were handled, ie all problems were solved.

    The problem comes with unhandled exceptions that are raised to the external caller program (SQL*Plus here, but it could be a Java, OCI, OCCI (...) program). Is there an expected behaviour whatever the calling program is (eg a rollback) ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    If a PL/SQL block raises an exception, then only the statement that caused the exception is rolled back. It is up to the caller to decide whether to roll back everything before the exception.

    We are used to SQL Plus (and maybe other environments) catching the exception and rolling everything back for us, which gives the false impression that everything the block did is always rolled back. But it was SQL Plus that did it, not PL/SQL.

    It seems to me that it would be better if PL/SQL did roll everything back to the start of the block, but perhaps there are good reasons why this should not be?

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    "then only the statement that caused the exception is rolled back"

    O man. So then the solution is to let outermost calling program have a clause like:

    Code:
    WHEN OTHERS THEN
      ROLLBACK;
      RAISE;
    like

    Code:
    SQL> ed
    Wrote file afiedt.buf
    
      1  create or replace procedure fct_call_test_rollback as
      2      num number;
      3      begin
      4          begin
      5              fct_test_rollback();
      6         exception
      7              when others then
      8                  select count(*) into num
      9                  from test_rollback;
     10                 DBMS_OUTPUT.PUT_LINE('After fct_test_rollback count = '||num);
     11                 rollback;
     12                  select count(*) into num
     13                  from test_rollback;
     14                 DBMS_OUTPUT.PUT_LINE('Completely After fct_test_rollback count = '||num);
     15                 raise;
     16          end;
     17* end fct_call_test_rollback;
    SQL> /
    
    Procedure created.
    
    SQL> execute fct_call_test_rollback;
    After fct_test_rollback count = 1
    Completely After fct_test_rollback count = 0
    BEGIN fct_call_test_rollback; END;
    
    *
    ERROR at line 1:
    ORA-01476: divisor is equal to zero
    ORA-06512: at "FORBESC.FCT_CALL_TEST_ROLLBACK", line 15
    ORA-06512: at line 1
    -Chuck

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

    I'm not quite sure there is anything abnormal going on here. AIUI an unhandled exception is propagated upwards through the call stack until it is eventually handled. In the case of some SQL Plus code (which also doesn't handle it) it results in an error message.

    This is generally considered pretty standard exception type behaviour for any language with exception type functionality.

    Consider the compiler directives found in many languages, which can switch on or off exception behaviour as and when required (and is widely utilised).

    Maybe I'm missing something!

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

  12. #12
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by billm
    Hi,

    I'm not quite sure there is anything abnormal going on here. AIUI an unhandled exception is propagated upwards through the call stack until it is eventually handled. In the case of some SQL Plus code (which also doesn't handle it) it results in an error message.

    This is generally considered pretty standard exception type behaviour for any language with exception type functionality.

    Consider the compiler directives found in many languages, which can switch on or off exception behaviour as and when required (and is widely utilised).

    Maybe I'm missing something!

    Cheers
    Bill
    The problem is not about exceptions in general, which behave in PLSQL as in any other language, just as you say... The problem is much more about exceptions and their impact on implicit transaction commit/rollback.

    Quote Originally Posted by andrewst
    If a PL/SQL block raises an exception, then only the statement that caused the exception is rolled back. It is up to the caller to decide whether to roll back everything before the exception.
    So we agree that if we want the stored procedure to either commit if the transaction went OK or rollback otherwise, we have to do as Chuck said and use

    WHEN OTHERS THEN
    ROLLBACK;
    RAISE;

    in the procedure we are calling to execute the transaction (which may of course call other procedures or functions). That's what I'm currently doing.

    This supposes that we want to either commit or rollback in the PLSQL procedure of course. If one wants the caller to take the rollback/commit decision, then it will be up to it : we agree that there is NO PLSQL implicit rollback when an exception is raised to the caller, don't we ?

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  13. #13
    Join Date
    Dec 2003
    Posts
    1,074
    I agree...now.

    When you learn that you've misunderstood something at this basic level, you wonder where else you've got a similar misconception.

    -Chuck

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by RBARAER
    So we agree that if we want the stored procedure to either commit if the transaction went OK or rollback otherwise, we have to do as Chuck said and use

    WHEN OTHERS THEN
    ROLLBACK;
    RAISE;

    in the procedure we are calling to execute the transaction (which may of course call other procedures or functions). That's what I'm currently doing.

    This supposes that we want to either commit or rollback in the PLSQL procedure of course.
    As a rule I don't want to COMMIT or ROLLBACK the whole transaction in a procedure. If you wanted to roll back just the work done by the procedure, you could code it like this:
    Code:
    procedure proc1 is
    begin
      savepoint sp_proc1;
      ...
    exception
      when others then
        rollback to sp_proc1;
        raise;
    end;

  15. #15
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by andrewst
    As a rule I don't want to COMMIT or ROLLBACK the whole transaction in a procedure.
    Well, this can be discussed. I personally put all business rules in stored procedures : each stored procedure called from the client program is a transaction in itself, with all the meaning it can have concerning business rules. The client program doesn't know what is done inside the procedure, it only knows that in such a situation it has to call this procedure with these arguments and it will either do the whole work or nothing, so I find it more natural to put the COMMIT/ROLLBACK decision inside the stored procedure. Do you see any objective drawback of doing so here ?

    This wouldn't be possible of course if it were the client program that would be in charge of business rules enforcement, but I'm firmly against such solutions.

    Quote Originally Posted by andrewst
    If you wanted to roll back just the work done by the procedure, you could code it like this:
    Code:
    procedure proc1 is
    begin
      savepoint sp_proc1;
      ...
    exception
      when others then
        rollback to sp_proc1;
        raise;
    end;
    Yeah, thanks for the "savepoint" ... "rollback to" part. Even if in the way I'm programming it is not really needed, it is "neater" than simply "rollback".

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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