asked on

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;
/

ASKER CERTIFIED SOLUTION

pennnn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

andrewst

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.

adenosine

ASKER

What is the variable type of v_string? varchar2?

schwertner

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;
/

adenosine

ASKER

I try not to use substrings.

pennnn

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!

schwertner

andrewst

To avoid substrings I suppose you could do this:

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

;)

adenosine

ASKER

Thanks to everyone who helped. Pennn thanks for explaining first that FM would not work, and an alternative way without using substr