4 Replies Latest reply: Mar 27, 2014 5:13 AM by padders RSS

    Exception handling in procedure

    rajendra


      Hello guys,

       

      I am having a below code :

       

       

      BEGIN

       

      -- Calling procedure B;

       

      END;

       

       

      PROC B()

      AS

       

      USER_DEF EXCEPTION;

       

      BEGIN

           IF <SOME_CONDITION> THEN

                RAISE USER_DEF;

           END IF;

      END;

       

       

      I want to throw a exception from PROCEDURE B and want to handle it in the calling PL/SQL block.

       

      IS IT POSSIBLE ?

       

      Can someone please guide me on this ?

       

       

      Thanks,

      Rajendra

        • 1. Re: Exception handling in procedure
          Nimish Garg

          yes like below:

           

          BEGIN

          -- Calling procedure B;

          exception when others then

          -- handler;

          END;

          • 2. Re: Exception handling in procedure
            rajendra

            Hi NIMISH,

             

            Thanks for your reply.

             

            I don't want to use the OTHERS clause to catch the exception.
            Can I catch "USER_DEF" Exception which I have defined in the PROC B in the calling PL/SQL block ?

             

             

            Thanks,

            Rajendra

            • 3. Re: Exception handling in procedure
              Marwim

              Hello,

               

              since the exception is defined within B the calling procedure cannot see it.

              You can create a package with your exceptions, then B would raise errorpackage.user_def and the calling procedure can check WHEN errorpackage.user_def.

               

              Regards

              Marcus

               

              Edit:

              Example

              CREATE OR REPLACE PACKAGE my_errors

              AS

               

                  e_user_defined EXCEPTION;

                  c_user_defined CONSTANT INTEGER := -20500;

                  PRAGMA EXCEPTION_INIT (e_user_defined, -20500);

              ...

              Now you can

              RAISE my_errors.e_user_defined;

              and check for

              WHEN my_errors.e_user_defined THEN

              And your exception will be associated with SQLCODE -20500

              See also PL/SQL 101 : Exception Handling

              • 4. Re: Exception handling in procedure
                padders

                > Can I catch "USER_DEF" Exception which I have defined in the PROC B in the calling PL/SQL block ?

                 

                Yes - provided that the exception is declared somewhere that is in scope to both blocks.

                 

                e.g.

                 

                DECLARE

                   exception_name EXCEPTION;

                  

                   PROCEDURE procedure_name IS

                   BEGIN

                      RAISE exception_name;

                   END procedure_name;

                BEGIN

                   procedure_name;

                EXCEPTION

                   WHEN exception_name THEN

                      NULL;

                END;

                /