1 2 3 Previous Next 33 Replies Latest reply: Feb 5, 2013 5:02 AM by Bawer Go to original post RSS
      • 15. Re: good practice question
        SomeoneElse
        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
          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
            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
              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
                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
                  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
                    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
                      :-) 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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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