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>