This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jul 29, 2013 5:13 AM by Pablolee RSS

PL SQL anonymous block procedure Exception Handling Problem?

fb281d46-72f0-4c50-aba5-ff42ccca74c7 Newbie
Currently Being Moderated

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

Legend

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