Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PL SQL anonymous block procedure Exception Handling Problem?

1028857Jul 29 2013 — edited Jul 29 2013

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.

This post has been answered by Pablolee on Jul 29 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 26 2013
Added on Jul 29 2013
18 comments
1,400 views