misunderstanding in formating a phone number using oracle pl/sql

Question

CREATE OR REPLACE PROCEDURE format_phone_number
 (p_phone_number IN OUT VARCHAR2)
IS 
BEGIN
 p_phone_number := '(' || SUBSTR(p_phone_number,1,3) ||  ')' ||
                   '-' || SUBSTR(p_phone_number,4,3) ||
                   '.' || SUBSTR(p_phone_number,7,4);

END format_phone_number;               
-------------------------------------------------
DECLARE
 v_number VARCHAR2(25) := '8002019201';
BEGIN
 format_phone_number(v_number);
 DBMS_OUTPUT.PUT_LINE(v_number);
END;

Output is ok (800)-201.9201. Question is why after i cut from the procedure like this for example :

CREATE OR REPLACE PROCEDURE format_phone_number
    (p_phone_number IN OUT VARCHAR2)
IS 
BEGIN
p_phone_number := '(' || SUBSTR(p_phone_number,1,3) ||  ')';
END format_phone_number;

After invoking again, it gives me only this (800) and not (800)2019201 "unformated" like. Is this a kind of a regular expression and it just can't parse the whole thing because of the limitation of initalization in the p_phone_number from the procedure?

Answer 1

The code is doing exactly what you told it to do. In the second code block only three characters are being taken from the initial value of p_phone_number. These three characters, and the leading and trailing parentheses, then replace the original contents of p_phone_number when the assignment is made.

To get the result you're expecting you'd need to use:

p_phone_number := '(' || SUBSTR(p_phone_number,1,3) ||  ')' ||
                  SUBSTR(p_phone_number, 4, 7);