?
Solved

How to use FM in PL/SQL

Posted on 2003-03-26
9
Medium Priority
?
1,771 Views
Last Modified: 2012-06-21
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;
/
0
Comment
Question by:adenosine
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 11

Accepted Solution

by:
pennnn earned 500 total points
ID: 8210563
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
 
LVL 15

Expert Comment

by:andrewst
ID: 8210587
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
 

Author Comment

by:adenosine
ID: 8210592
What is the variable type of v_string?  varchar2?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 48

Expert Comment

by:schwertner
ID: 8210683
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
 

Author Comment

by:adenosine
ID: 8210692
I try not to use substrings.
0
 
LVL 11

Expert Comment

by:pennnn
ID: 8210719
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
 
LVL 48

Expert Comment

by:schwertner
ID: 8210756
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
 
LVL 15

Expert Comment

by:andrewst
ID: 8211227
To avoid substrings I suppose you could do this:

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

;)
0
 

Author Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question