Show Me The Money ....oracle

Money, money, money. It's a rich man's world. It can't buy you love, but as we are constantly reminded it can buy you cheap Levitra and pre-approved loans, and those are just as good and will probably boost this site's stats. It makes the world go round. It's great - but how to format it?

Well, here is one way. The editing history tells the whole story.

/*=============================================================================
Procedure:    FN_FORMAT_DOLLARS
Description:  Formats a number as dollars (2 decimals)

MODIFICATION HISTORY:
Person        Date        Comments
---------     ------      -------------------------------------------
XX            01/24/03    Created
SF            08/05/03    Rewrote the function as a TO_CHAR statement
==============================================================================*/

FUNCTION fn_format_dollars (p_dollars IN NUMBER)
   RETURN VARCHAR2
IS
   -- v_dollars   VARCHAR2 (50);
BEGIN

   /*
   v_dollars := TO_CHAR (p_dollars);
   IF INSTR (v_dollars, '.') = 0
   THEN
       v_dollars :=  v_dollars  || '.';
   END IF;

   -- too many decimal places...
   WHILE   LENGTH (v_dollars) - INSTR (v_dollars, '.') > 2
   LOOP
       v_dollars := SUBSTR (v_dollars, 1, LENGTH (v_dollars) - 1);
   END LOOP;

   -- not enough decimal places...
   WHILE   LENGTH (v_dollars) - INSTR (v_dollars, '.') < 2
   LOOP
       v_dollars := v_dollars || '0';
    END LOOP;
   */

   RETURN TO_CHAR(p_dollars, 'FM999999999990.00');

END fn_format_dollars;

Many thanks to rd for sending this in.