Format money : Utility Function « Function Procedure Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Function Procedure Packages
3. Utility Function

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION format_money (p_value_num NUMBER)
  2     RETURN VARCHAR2 IS
  3     returnString VARCHAR2(100);
  4  BEGIN
  5     IF (p_value_num >= 1000000) THEN
  6        returnString := LTRIM(TO_CHAR(p_value_num, '$999,999,999.00'));
  7     ELSIF (p_value_num < 1000000) AND (p_value_num >= 1000) THEN
  8        returnString := LTRIM( TO_CHAR(p_value_num, '$999,999.00'));
  9     ELSE
 10        returnString := LTRIM(TO_CHAR(p_value_num, '$999.00'));
 11     END IF;
 12     RETURN returnString;
 13  EXCEPTION
 14     WHEN OTHERS THEN
 15        RAISE_APPLICATION_ERROR( -20123, 'Error occurred in MONEY ' ||
 16           'function for incoming value:' || TO_CHAR(p_value_num) ||
 17           ' and outgoing value:' || returnString);
 18  END format_money;
 19  /

Function created.

SQL>
SQL>