How to use FM in PL/SQL

This is my code and I can't get FM to work.  It's suppose to output
Kim Cox's phone number is (715) 555-1234.

The book doesn't explain it well and I can't find it anywhere online.  There's no example to look at it, it just says FM"("999")"999"-"9999, no real example in use.  

set serveroutput on size 4000;
--variable declaration block
declare
     faculty_last_name varchar2 (25);
     faculty_first_name varchar2 (25);
     faculty_phone number (10);
begin
--assigning variables
     faculty_last_name := 'COX';
     faculty_first_name := 'KIM';
     faculty_phone := 7155551234;
--begin formatting
     faculty_last_name := initcap (faculty_last_name);
     faculty_first_name := initcap (faculty_first_name);
     faculty_phone := FM"("999")" 999"-"9999;
--code block begins
     dbms_output.put_line(faculty_first_name || faculty_last_name || '''s' || ' phone number is ' || faculty_phone) || '.');
end;
/
adenosineAsked:
Who is Participating?
 
pennnnConnect With a Mentor Commented:
There are several problems:
- faculty_phone is a NUMBER, so you can't assign a character string to it, as in this statement:
faculty_phone := FM"("999")" 999"-"9999;
- The format masks work only in to_char statements, like that:
v_string := to_char(phone_number, '999,999,999');
- The format mask you are trying to use is not a standard SQL or PL/SQL format mask for numbers. It can be used only in Forms. In SQL you can't include characters (like "(") in the mask. Here's a link to the list of the valid number format masks:
http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96540/sql_elements4a.htm#34597

If you want to display the phone number in the specified format you just need to cut it into pieces and concatenate the parenthesis where required.
Hope that helps!
0
 
andrewstCommented:
Your book must be talking about Oracle Forms mumber format masks.  The format mask you have is NOT valid in PL/SQL.  The "FM" bit is not the problem, it is the embedded "(", ")" and "-" characters that cannot be handled.

In Forms you can use a format just like that on a number item.  In PL/SQL you can only use TO_CHAR which doesn't allow such fancy formatting.  Instead you will need to format it in code like:

'(' || SUBSTR(faculty_phone,1,3) || ') ' || SUBSTR(faculty_phone,4,3) || '-' || SUBSTR(faculty_phone,7)

Of course, you can define a function that returns the above expression and then just use that.
0
 
adenosineAuthor Commented:
What is the variable type of v_string?  varchar2?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
schwertnerCommented:
set serveroutput on size 4000;
--variable declaration block
declare
    faculty_last_name varchar2 (25);
    faculty_first_name varchar2 (25);
    faculty_phone number (10);
    faculty_phone_1 varchar2(11);

begin
--assigning variables
    faculty_last_name := 'COX';
    faculty_first_name := 'KIM';
    faculty_phone := 7155551234;
--begin formatting
    faculty_last_name := initcap (faculty_last_name);
    faculty_first_name := initcap (faculty_first_name);
    faculty_phone_1 := TO_CHAR(faculty_phone,'FM9999999999');
--code block begins
    dbms_output.put_line(faculty_first_name || faculty_last_name || '''s' || ' phone number is ' ||
SUBSTR( faculty_phone_1, 1,3)
|| '-'||
SUBSTR( faculty_phone_1, 4,3)
|| '-'||
SUBSTR( faculty_phone_1, 8,4)
|| '.');
end;
/
0
 
adenosineAuthor Commented:
I try not to use substrings.
0
 
pennnnCommented:
Yes, v_string is VARCHAR2. I don't think you can do it withour substr, and I don't see why you don't want to use substr...
Hope that helps!
0
 
schwertnerCommented:
set serveroutput on size 4000;
--variable declaration block
declare
    faculty_last_name varchar2 (25);
    faculty_first_name varchar2 (25);
    faculty_phone number (10);
    faculty_phone_1 varchar2(11);

begin
--assigning variables
    faculty_last_name := 'COX';
    faculty_first_name := 'KIM';
    faculty_phone := 7155551234;
--begin formatting
    faculty_last_name := initcap (faculty_last_name);
    faculty_first_name := initcap (faculty_first_name);
    faculty_phone_1 := TO_CHAR(faculty_phone,'FM9999999999');
--code block begins
    dbms_output.put_line(faculty_first_name || faculty_last_name || '''s' || ' phone number is ' ||
SUBSTR( faculty_phone_1, 1,3)
|| '-'||
SUBSTR( faculty_phone_1, 4,3)
|| '-'||
SUBSTR( faculty_phone_1, 8,4)
|| '.');
end;
/
0
 
andrewstCommented:
To avoid substrings I suppose you could do this:

'(' || floor(faculty_phone/10000000) || ')'
|| floor(mod(faculty_phone,10000000)/10000)
|| '-'
|| mod(faculty_phone,10000)

;)
0
 
adenosineAuthor Commented:
Thanks to everyone who helped.  Pennn thanks for explaining first that FM would not work, and an alternative way without using substr
0
All Courses

From novice to tech pro — start learning today.