This discussion is archived
1 2 3 Previous Next 33 Replies Latest reply: Feb 5, 2013 3:02 AM by Bawer Go to original post RSS
  • 15. Re: good practice question
    SomeoneElse Guru
    Currently Being Moderated
    select count(*) ...
    won't generate a NO_DATA_FOUND exception.
    It's possible.
    SQL> declare
      2     l_count number;
      3  begin
      4     select count(*)
      5     into   l_count
      6     from   dual
      7     where  dummy = 'q'
      8     group  by dummy;
      9  end;
     10  /
    declare
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 4
  • 16. Re: good practice question
    Bawer Journeyer
    Currently Being Moderated
    SomeoneElse wrote:
    select count(*) ...
    won't generate a NO_DATA_FOUND exception.
    It's possible.
    SQL> declare
    2     l_count number;
    3  begin
    4     select count(*)
    5     into   l_count
    6     from   dual
    7     where  dummy = 'q'
    8     group  by dummy;
    9  end;
    10  /
    nice trick to get the error but I wouldn't use group by to get the existence :-) In manytimes, It is used to avoid duplicates but there is another ways to do this.
  • 17. Re: good practice question
    Bawer Journeyer
    Currently Being Moderated
    Rahul India wrote:
    Can you just give a small example?
    declare
      type t_err is record(
        code number default 0 ,
        msg varchar2(4000) 
      );
      
      type t_variables is record (
        a number := -1
        ,b number
        --,c ...
      );
      
      v t_variables;
      err t_err;
      
      function check_error( var t_variables) return t_err is 
        result t_err;
      begin    
        if var.a = -1 then
          result.code := -1;
          result.msg := 'Message for first Error';
        --... another checks
        end if;
        return result;
      end;
    
    
    begin
    
      savepoint before_start_process;
      
      process...
      ...
      
      err := check_error(v);
      
      if err.code != 0 then  
          dbms_output.put_line(
              'code=>'||err.code || ', Msg=>'||err.msg 
              ||' rolling back'
          );
          rollback to before_start_process;
          --log/email
      else
          commit;
      end if;
      
      exception
      when others then
          dbms_output.put_line(        
            dbms_utility.format_error_backtrace()
          );
          rollback to before_start_process;
          --log/email
    end;
  • 18. Re: good practice question
    Marwim Expert
    Currently Being Moderated
    Wouldn't there be less code when you simply write
    declare
     
    begin
     
      savepoint before_start_process;
      
      process...
      ...
      
    
      commit;
      
      exception
      when others then
          dbms_output.put_line(        
            dbms_utility.format_error_backtrace()
          );
          rollback to before_start_process;
          --log/email
    end;
    When there is an exception that requires a rollback, then let it go to the end of the procedure and catch it. In your example you would have to catch every exception, put it into your record and then check whether an exception occured.

    Marcus
  • 19. Re: good practice question
    Bawer Journeyer
    Currently Being Moderated
    Marwim wrote:
    When there is an exception that requires a rollback, then let it go to the end of the procedure and catch it.
    this is already done. My example is just an example, to show my logic. In case, you should use it, it is possible that you should customize it for you.
    Marwim wrote:
    In your example you would have to catch every exception, put it into your record and then check whether an exception occured.
    user defined exception does this already. you must check anything every time to raise/catch the exception but in this method, you can ignore rest of check process, if you find any error. this gives you more possibility to get information about problem through keeping the additional data in variables instead of raising an exception.
  • 20. Re: good practice question
    Marwim Expert
    Currently Being Moderated
    user defined exception does this already. you must check anything every time to raise/catch the exception but in this method, you can ignore rest of check process, if you find any error. this gives you more possibility to get information about problem through keeping the additional data in variables instead of raising an exception.
    When I raise a user defined exception it will propagate to the exception handler at the end of the process and it will also ignore the rest of the procedure. I can easily include the additional data in my user defined exception. No need to add another layer.

    Perhaps you can give an example to explain your way in more detail. Mine would be
    DECLARE
    
    BEGIN
    
      SAVEPOINT before_start_process;
    
      do_something1();
      -- here some exception occures
      do_something2();
    
      COMMIT;
    
    EXCEPTION
      WHEN OTHERS THEN
          dbms_output.put_line(
            dbms_utility.format_error_backtrace()
          );
          ROLLBACK TO before_start_process;
          --log/email
    END;
    Since the exception is raised within do_something2 with a meaningful message I don't have to assign the message to a record and then check whether a record element has a special value. And I don't have to quit processing by an IF logic.
    Whenever an error occures in do_something2 I get an exception, the process stops, the exception is caught at the end and I can handle it.
  • 21. Re: good practice question
    Stew Ashton Expert
    Currently Being Moderated
    Marwim wrote:
    DECLARE
    BEGIN
    SAVEPOINT before_start_process;
    do_something1();
    -- here some exception occures
    do_something2();
    COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(
    dbms_utility.format_error_backtrace()
    );
    ROLLBACK TO before_start_process;
    --log/email
    END;
    You know, this is exactly what Oracle does anyway!

    If you call the PL/SQL engine using an anonymous block, Oracle considers that a "statement" and guarantees "statement-level atomicity".

    There will be an implicit savepoint just before the anonymous block starts executing. If any exception is unhandled and goes back to the caller, Oracle will roll back the statement, meaning everything that got changed within that anonymous block.

    The best way to do exactly the same thing is:
    DECLARE
    BEGIN
    do_something1();
    do_something2();
    END;
    /
    By catching the exceptions, or by using transaction control statements, we are keeping Oracle from doing what it is designed to do.

    If we want to log the error before returning, then we should catch it, use an autonomous transaction to log it, then re-RAISE it (or use RAISE_APPLICATION_ERROR to raise a homemade exception). Personally, I would do this in the very top-level code just before returning to whoever the "client" is.
  • 22. Re: good practice question
    Marwim Expert
    Currently Being Moderated
    :-) I should't have copied too much from Bower's example.

    My point was, that I don't understand why he wants to store the error in a record and then check whether an error occured, instead of simply raising the error and catching it at the end, be it an anonymous block or a calling procedure.
  • 23. Re: good practice question
    Bawer Journeyer
    Currently Being Moderated
    Marwim wrote:
    My point was, that I don't understand why he wants to store the error in a record and then check whether an error occured
    Sometimes, we don't want rollback complete process,
    only a step in entire process will be rolled back and this step is saved to tell the another applications to get corrected the inputs!
    In this case, which you didn't understand, I would rather to rollback only an input instead of rolling back entire process.
  • 24. Re: good practice question
    Bawer Journeyer
    Currently Being Moderated
    Stew Ashton wrote:
    Marwim wrote:
    DECLARE
    BEGIN
    SAVEPOINT before_start_process;
    do_something1();
    -- here some exception occures
    do_something2();
    COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(
    dbms_utility.format_error_backtrace()
    );
    ROLLBACK TO before_start_process;
    --log/email
    END;
    You know, this is exactly what Oracle does anyway!
    I think you have catched very different pointing.
    again this was only an example, to show how to use a function to check errors instead of raising exception!
  • 25. Re: good practice question
    Stew Ashton Expert
    Currently Being Moderated
    Bawer wrote:
    again this was only an example, to show how to use a function to check errors instead of raising exception!
    All right, but I still disagree. You are doing a lot of things Oracle would do for you if you let it. Don't you think most programmers who will someday maintain your code understand Oracle standard behavior better than they would understand your rewrite of that behavior?

    When there is a specific exception that the code knows how to handle, I handle it.

    When there is a requirement to hide the Oracle exception, I use an autonomous transaction to log the original exception, then I raise a homemade exception.

    I don't change the language semantics to replace exceptions by error codes.

    I don't put transaction control statements all over the place, but only in the top-level code. Most of the time that top-level code isn't even in PL/SQL, but in some application-layer language.

    I also don't start updating the database until all user inputs have been verified.

    Edited by: Stew Ashton on Feb 4, 2013 4:30 PM
  • 26. Re: good practice question
    Bawer Journeyer
    Currently Being Moderated
    Stew Ashton wrote:
    Don't you think most programmers who will someday maintain your code understand Oracle standard behavior better than they would understand your rewrite of that behavior?
    No, I don't think, I have no much time to write big code in forum, so I keep it small to show the logic (and in this case, not to show the behavior of oracle, user defined exception isn't produced by oracle)
    Stew Ashton wrote:
    When there is a specific exception that the code knows how to handle, I handle it.
    No, I don't do this, I would write the code differently, to avoid an exception if possible.
    I dislike to see multiple begin/exception/end blocks in a small code. It might be your style, but not mine.
    Stew Ashton wrote:
    When there is a requirement to hide the Oracle exception, I use an autonomous transaction to log the original exception, then I raise a homemade exception.
    do you think you only do this? surely not. but I don't do this too, my logger is completely anonymous and I call it overall, not only in case of error! (I didn't explain it, because it wasn't asked)!
    Stew Ashton wrote:
    I don't change the language semantics to replace exceptions by error codes.
    I don't put transaction control statements all over the place, but only in the top-level code. Most of the time that top-level code isn't even in PL/SQL, but in some application-layer language.
    Bravo, bravo, bravo (!) you understood the pointing here.

    Stew Ashton wrote:
    I also don't start updating the database until all user inputs have been verified.
    You also didn't meet different business models, even if it is required starting the process to identify the wrong inputs, and your %1 wrong-Inputs mayn't stop the process (and mayn't effect the process until corrected). Nice for you!
  • 27. Re: good practice question
    Marwim Expert
    Currently Being Moderated
    Stew Ashton wrote:
    When there is a specific exception that the code knows how to handle, I handle it.
    No, I don't do this, I would write the code differently, to avoid an exception if possible.
    Maybe we're talking at crossed purposes.

    One thing is to avoit exceptions by checking conditions before they occur (your statement). Another, completely different thing is how to handle exception (Stew's statement).
    When you know that there are conditions that might hinder a normal processing, then check and handle them, this is what most of us will do. Though even then handling an exception might be the more performant way. Example: You select from a table where you expect a single row to be returned. In one out of a thousand cases you will get no row, but you can continue the process with a default value. Do you really want to check the existence each time? This might be a performance issue on a big table. So you simply select and when you get a NO_DATA_FOUND then you can handle it by using the default. Should you get TOO_MANY_ROWS, then this is an unexpected condition and the process has to be terminated. Therefore you don't handle the exception and propagate it to the topmost level in your application.
    I dislike to see multiple begin/exception/end blocks in a small code.
    You wouldn't, because they are each hidden in their own procedure/function.

    Regards
    Marcus

    Edited by: Marwim on 05.02.2013 10:50
  • 28. Re: good practice question
    Bawer Journeyer
    Currently Being Moderated
    Marwim wrote:
    Do you really want to check the existence each time? This might be a performance issue on a big table. So you simply select and when you get a NO_DATA_FOUND then you can handle it by using the default. Should you get TOO_MANY_ROWS, then this is an unexpected condition and the process has to be terminated. Therefore you don't handle the exception and propagate it to the topmost level in your application.
    this is your mind, as suggested, I try to give least chance to exceptions. this is my pointing!

    If you really want to know, what I would do in this case, just small example:
    instead of
    select col1, ...  into val1,... from ...
    I would use
    for i in (select col1, ... from ...) loop
      val1 := i.col1;
      ...
    end loop;
    Edited by: Bawer on 05.02.2013 11:08
  • 29. Re: good practice question
    Marwim Expert
    Currently Being Moderated
    I would use ...
    This kind of code would not pass my code review.
    1. a LOOP to select a single value hides the fact that you expect a single value and makes the code less clear.
    2. you need to explicitly check for a none existing value or too many values

    Instead write
    Function getValue
      ...
      SELECT  col1
      INTO    val1
      FROM
      ...
    
      RETURN val1;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        RETURN default_value;
    END;
    In your code you simply call
    variable_xy := getValue();
    Nice, clean, no anonymous block and clear about the purpose.

    An exception is nothing to avoid at any price. It's a tool just like other features of PL/SQL and should be used.

    Regards
    Marcus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points