How to always show two decimal places on a number in PL/SQL

Question

Folks,

I have a existing stored procedure that I'm trying to update to always show two decimal places even when it's a whole number or single decimal number.

This stored procedure builds out a message that has to show v_credit_amt as a two decimal number, yet the value assigned to v_credit_amt can be either a whole number or single decimal or a two decimal value i.e. 175 should display as 175.00, 250.5 should display as 250.50, 395.95 should display as 395.95.

Here is the relevant stock pl/sql

v_credit_amt        number(22,2);
select NVL(sit_credit_limit,0)
  into v_credit_amt
  from sites
 where sit_pkey = p_site_id;

I thought that formatting via to_char during the select would solve this, ala

select NVL(to_char(sit_credit_limit, 'fm99999.00'),0)
  into v_credit_amt
  from sites
 where sit_pkey = p_site_id;

--v_credit_amt := to_char(v_credit_amt, 'fm99999.00');
insert into SQL_TXT values (v_credit_amt);
commit;

As you can see from the commented out line above, I've also tried it once the v_credit_amt variable is defined

I've also tried it with the to_number function

select NVL(to_number(sit_credit_limit, '99999.00'),0)
  into v_credit_amt
  from sites
 where sit_pkey = p_site_id;

However if the value stored in the sit_credit_limit column is a whole number without decimals, or a number like 250.5 v_credit_amt shows the same in all cases when querying SQL_TXT table I'm inserting to for debugging.

SQL> select * from SQL_TXT;

COL1
--------------------------------------------------------------------------------
175
250.5

This particular event simply concatenates multiple message portions into a single long message string that is returned i.e.

if p_message_id = '14' then
   v_message := 'Comtrol Check In Message Posted';
   v_string  := v_string || '008' || lpad(length(v_fname || ' ' || v_lname), 3, '0') || v_fname || ' ' || v_lname;
   v_string  := v_string || '059' || lpad(1, 3, '0') || '0';
   --v_string  := v_string || '089' || lpad(1, 3, v_credit_amt) || to_char(v_credit_amt);
   v_string  := v_string || '089' || lpad(length(to_char(v_credit_amt, 'fm9999999.00')), 3, '0') || to_char(v_credit_amt, 'fm9999999.00');
   v_string  := v_string || '106' || lpad(length(nvl(v_acct_number,'')), 3, '0') || v_acct_number;
   --v_string  := v_string || '106' || v_acct_number;
   v_string  := v_string || '164' || lpad(1, 3, '0') || '0';
   v_string  := v_string || '174' || lpad(length(v_rm_phone_num), 3, '0') || v_rm_phone_num;
   v_string  := v_string || '175' || lpad(length(v_rm_id), 3, '0') || v_rm_id;
   v_string  := v_string || '183' || lpad(1, 3, '0') || '0';
endif;

However I cannot get the final string to properly have two decimals in all use cases.

For example if the value in the db is 125 I get this for a final string

144450034629999008009Bob Smith05900100{89006125}1060073307542164001017400340917500 34091830010

however it should have been

144450034629999008009Bob Smith05900100{89007125.00}1060073307542164001017400340917500 34091830010

Sorry for the formatting above, I can't see how to bold a section without a code block so I've highlighted the relative portions instead {}

What am I missing if I need to always display a number to two decimals even if a whole or 1 decimal value is given?

Answer 1

You actually, have to insert the data with formatting. (Assuming the targeted column is VARCHAR) What ever format you fetch and put into a NUMBER variable, will be a NUMBER.

A number doesn't have a format to be saved after-all. Only for display, the formatting comes into picture.

insert into SQL_TXT values (TO_CHAR(v_credit_amt,'FM99999.00'));
commit;

If the INSERT-ed column is NUMBER again.

You still want to go with

SELECT TO_CHAR(COL1,'FM99999.00') FROM SQL_TEXT;