Number formatting in Oracle using TO_CHAR

Question

Proper way to format the numbers in ORACLE stored procedures.

I need to display currency fields with 2 decimals. Expected output is as follows:

• 0 > 0.00
• 5 > 5.00
• 1253.6 > 1253.60
• 1253.689 > 1253.69

Below worked for me:

select to_char(9876.23 , 'fm999990.00') from dual;

But this has the issue of hard coding a bunch of 9s. If I give a larger number it will be displayed as "##############"

Is there any other way I can do this?

Answer 1

I need to display currency fields with 2 decimals.

Ensure you use the number data-type with scale and precision appropriate to the data rather than using NUMBER without scale and precision. If you are going to be storing dollars/euros/pounds etc. then the Gross World Product was of the order of $100,000,000,000,000 in 2014. Let's assume that you are not going to be dealing with more than this^{[citation needed]} then your currency column can be:

NUMBER(17,2)

If you get a value that is bigger than that then you need to perform a sanity check on your data and think whether an amount bigger than the world's gross product makes sense. If you are going to store the values as, for example, Yen or Zimbabwean dollars then adjust the scale appropriately.

You could even define a sub-type in a package as:

CREATE PACKAGE currencies_pkg IS
  SUBTYPE currency_type IS NUMBER(17,2);

  FUNCTION formatCurrency(
    amount IN CURRENCY_TYPE
  ) RETURN VARCHAR2;
END;
/

And your code to format it can be:

CREATE PACKAGE BODY currencies_pkg IS
  FUNCTION formatCurrency(
    amount IN CURRENCY_TYPE
  ) RETURN VARCHAR2
  IS
  BEGIN
    RETURN TO_CHAR( currency_value, 'FM999999999999990D00' );
  END;
END;
/

Then if you reference that sub-type in your stored procedures/packages you will not be able to exceed the maximum size of the currency data type without an exception being raised. The format model for displaying the value only needs to be defined in a single place and since the input is limited to the currency sub-type, then the formatting function will never exceed the imposed scale/precision and cannot output #s.

CREATE PROCEDURE your_procedure(
  in_value1 IN ACCOUNTS_TABLE.ACCOUNT_BALANCE%TYPE,
  in_value2 IN ACCOUNTS_TABLE.ACCOUNT_BALANCE%TYPE
)
IS
  v_value CURRENCIES_PKG.CURRENCY_TYPE;
BEGIN
  -- Do something
  v_value := in_value1 + in_value2;
  -- Output formatted value
  DBMS_OUTPUT.PUT_LINE( CURRENCIES_PKG.formatCurrency( v_value ) );
END;
/