1 2 Previous Next 16 Replies Latest reply: Oct 11, 2012 9:48 AM by Marwim RSS

    Procedure Exception

    893566
      Hi

      I have a procedure
      procedure PROC1(id number) is
      begin....
      exception when others dbms_output.put_line('other exception');
      when no_data_found
      dbms_output.put_line('no data');
      end;

      now I have an other procedure
      Porcedure PROC2
      begin
      PROC1(23412);
      end;

      now if the PROC1 returns an exception message I would like to assign the message to variable in PROC2 and display. How can I achieve this
        • 1. Re: Procedure Exception
          Centinul
          Would you mind explaining why you'd want to have exception processing structured like this?
          • 2. Re: Procedure Exception
            6363
            Please read all of these links -

            http://tkyte.blogspot.com/2006/09/classic-example-of-why-i-despise.html
            http://tkyte.blogspot.com/2006/08/ouch-that-hurts.html
            http://tkyte.blogspot.com/2008/06/when-others-then-null-redux.html
            http://tkyte.blogspot.com/2007/03/dreaded-others-then-null-strikes-again.html
            http://tkyte.blogspot.com/2012/05/pokemon-and-when-others.html

            And then explain why you want to do such a thing
            • 3. Re: Procedure Exception
              BluShadow
              a) when others exceptions (if there is a need to use them) should be the last exception handler in the exception block
              b) proc 1 will not "return" any message because you have handled the exception.

              You are not using exception handling as it is intended to be used. In fact you are using it completely wrongly.

              {thread:id=697262}
              • 4. Re: Procedure Exception
                812975
                i think you want this for learning purpose..
                anyways,
                you can create function or procedure with OUT parameter type..

                function PROC1(id number) return varchar2 is
                begin....
                return...
                ....
                exception when others
                return 'other exception';
                when no_data_found
                return 'no data';
                end;

                Procedure PROC2
                msg varchar2(100);
                begin
                msg := PROC1(23412);
                ...
                dbms_output.put_line(msg);
                ..
                end;
                /
                • 5. Re: Procedure Exception
                  893566
                  thank you

                  Edited by: 890563 on 11 Oct, 2012 7:04 AM
                  • 6. Re: Procedure Exception
                    6363
                    SaadL wrote:
                    i think you want this for learning purpose..
                    Why learn how to do something so wrong?
                    • 7. Re: Procedure Exception
                      MLBrown
                      You could also use the in out parameters in the procedure, but you should really avoid the when others.

                      procedure PROC1(p_id in number,
                      p_errm in out varchar2,
                      p_err in out varchar2)
                      is
                      begin....
                      exception
                      when others then
                      p_errm := 'Y';
                      p_err := 'other exception';
                      when no_data_found then
                      p_errm := 'Y';
                      p_err := 'no data';
                      end;

                      procedure PROC2
                      v_errm varchar2(1) := 'N';
                      v_err varchar2(240) := null;
                      is
                      begin
                      PROC1(23412, v_errm, v_err);
                      if v_errm = 'Y' then
                      dbms_output.put_line(v_errm);
                      end if;
                      end;
                      • 8. Re: Procedure Exception
                        812975
                        nothing is wrong i think. depends on requirements. thanks
                        • 9. Re: Procedure Exception
                          jeneesh
                          SaadL wrote:
                          nothing is wrong i think.
                          Your thinking is wrong... :(
                          • 10. Re: Procedure Exception
                            BluShadow
                            SaadL wrote:
                            nothing is wrong i think. depends on requirements. thanks
                            Actually it's completely wrong.
                            The passing of exceptions up to calling procedures should not be done using OUT parameters or return values from functions. That is NOT how to deal with exception handling. It's poor coding practice and leads to problems in it's own right.
                            • 11. Re: Procedure Exception
                              812975
                              sorry guys, i'm not challenging anyone.. its just a matter of learning..

                              anyways, lets talk about message which is the source for the calling procedure here.

                              function test1 .. return varchar2 is
                              lname varchar2(10);
                              begin
                              select name into lname
                              from employee
                              where... ;
                              return 'Y';
                              exception
                              when no_data_found then
                              return 'N';
                              end;
                              /

                              procedure test2 is
                              begin
                              ... calculations ..
                              ...
                              if test1(....) = 'N' then
                              ...different processing...
                              else
                              ...different processing...
                              .... calculations..
                              .... updations..
                              end if;
                              end;
                              /
                              • 12. Re: Procedure Exception
                                6363
                                SaadL wrote:

                                sorry guys, i'm not challenging anyone.. its just a matter of learning..
                                Why learn to do something so totally and idiotically wrong?

                                Isn't there enough to learn how do do it correctly?

                                http://www.oracle.com/pls/db112/homepage

                                Maybe after learning all this someone can learn how to shoot their foot off and give headaches to anyone who comes anywhere near anything they ever developed.
                                • 13. Re: Procedure Exception
                                  BluShadow
                                  SaadL wrote:
                                  sorry guys, i'm not challenging anyone.. its just a matter of learning..

                                  anyways, lets talk about message which is the source for the calling procedure here.

                                  function test1 .. return varchar2 is
                                  lname varchar2(10);
                                  begin
                                  select name into lname
                                  from employee
                                  where... ;
                                  return 'Y';
                                  exception
                                  when no_data_found then
                                  return 'N';
                                  end;
                                  /
                                  There's a difference between raising exception messages, which is what the OP is asking about and "handling" exceptions. Your code is a (poor) example of handling exceptions. A function should only have a single point of returning when properly designed:
                                  function test1 .. return varchar2 is 
                                    lname varchar2(10);
                                    v_retval varchar2(1) := 'Y';
                                  begin
                                    begin
                                      select name into lname
                                      from employee
                                      where... ;
                                    exception
                                      when no_data_found then
                                        v_retval := 'N';
                                    end;
                                    return v_retval;
                                  end;
                                  /
                                  Here the exception is handled within the nested execution block to set a value, and then the function returns that value at the end.
                                  • 14. Re: Procedure Exception
                                    812975
                                    + A function should only have a single point of returning when properly designed:.+

                                    Good to know. Thanks
                                    1 2 Previous Next