title: How to always show two decimal places on a number in PL/SQL
body: <p>Folks,</p>
  
  <p>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.</p>
  
  <p>This stored procedure builds out a message that has to show <code>v_credit_amt</code> as a two decimal number, yet the value assigned to <code>v_credit_amt</code> 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.  </p>
  
  <p>Here is the relevant stock pl/sql</p>
  
  <pre><code>v_credit_amt        number(22,2);
  select NVL(sit_credit_limit,0)
    into v_credit_amt
    from sites
   where sit_pkey = p_site_id;
  </code></pre>
  
  <p>I thought that formatting via to_char during the select would solve this, ala</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>As you can see from the commented out line above, I've also tried it once the v_credit_amt variable is defined </p>
  
  <p>I've also tried it with the to_number function</p>
  
  <pre><code>select NVL(to_number(sit_credit_limit, '99999.00'),0)
    into v_credit_amt
    from sites
   where sit_pkey = p_site_id;
  </code></pre>
  
  <p>However if the value stored in the sit_credit_limit column is a whole number without decimals, or a number like 250.5 <code>v_credit_amt</code> shows the same in all cases when querying SQL_TXT table I'm inserting to for debugging.</p>
  
  <pre><code>SQL&gt; select * from SQL_TXT;
  
  COL1
  --------------------------------------------------------------------------------
  175
  250.5
  </code></pre>
  
  <p>This particular event simply concatenates multiple message portions into a single long message string that is returned i.e.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>However I cannot get the final string to properly have two decimals in all use cases.</p>
  
  <p>For example if the value in the db is 125 I get this for a final string</p>
  
  <p><code>144450034629999008009Bob Smith05900100{89006125}1060073307542164001017400340917500
  34091830010</code></p>
  
  <p>however it should have been </p>
  
  <p><code>144450034629999008009Bob Smith05900100{89007125.00}1060073307542164001017400340917500
  34091830010</code></p>
  
  <p>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 {}</p>
  
  <p>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?</p>