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.