1 2 Previous Next 18 Replies Latest reply: Jul 29, 2013 7:13 AM by Pablolee RSS

    PL SQL anonymous block procedure Exception Handling Problem?

    fb281d46-72f0-4c50-aba5-ff42ccca74c7

      Hello,

      I am a newbie to PL/SQL.

      I am having difficulty trying to handle exceptions for an anonymous PLSQL block procedure I have created that will format any 10 digit block and format it into the following (XXX)-XXX-XXXX.

      I need to -

      1. handle if there is more than 10 digits

      2. handle if there less than 10 digits

      3. handle if there are any inappropriate characters (non number)

      4. handle if there are no characters.

                  I have created the following procedure.  However, my if/else logic attempts or raise exceptions have create errors thus far.

      Please advise the following code.

      create or replace

      PROCEDURE format_phone

          (

          p_phne_no IN OUT VARCHAR2

          ) IS

        extra_digits EXCEPTION; -- Number must be a 10 digit number. Please enter 10 digits

        no_digits EXCEPTION; -- Please enter digits there were no digits inputed.

        invalid_char EXCEPTION; --You have entered an inappropriate character please enter number values 0-9.

        less_digits EXCEPTION; --You have entered too few digits. Please enter a 10 digit phone number.

      BEGIN

        p_phne_no :='('    

                    || SUBSTR(p_phne_no,1,3) ||

                    ')'

                    ||'-'||

                    SUBSTR(p_phne_no,4,3)

                    ||'-'||

                    SUBSTR(p_phne_no,7);

        --DBMS_OUTPUT.PUT_LINE (p_phne_no);

      EXCEPTION

        WHEN  invalid_char

        THEN

          dbms_output.put_line('You have entered an inappropriate character please enter number values 0-9.');

       

        WHEN no_digits

        THEN

          dbms_output.put_line('Please enter digits there were no digits inputed.');

         

        WHEN less_digits

        THEN

          dbms_output.put_line('You have entered too few digits. Please enter a 10 digit phone number.');

         

        WHEN extra_digits

        THEN

          dbms_output.put_line('You have entered too many digits. Number must be a 10 digit number. Please enter 10 digits.');

      END format_phone;

       

       

      Thank you for your concern.

        1 2 Previous Next