Spelling numbers in PL/SQL
CREATE OR REPLACE FUNCTION spell_number(p_number IN NUMBER)
RETURN VARCHAR2 AS
TYPE myarray IS TABLE OF VARCHAR2(255);
l_str myarray := myarray(
'',
' thousand ',
' million ',
' billion ',
' trillion ',
' quadrillion ',
' quintillion ',
' sextillion ',
' septillion ',
' octillion ',
' nonillion ',
' decillion ',
' undecillion ',
' duodecillion '
);
l_num VARCHAR2(50) DEFAULT trunc(p_number);
l_return VARCHAR2(4000);
BEGIN
FOR i IN 1 .. l_str.count LOOP
EXIT WHEN l_num IS NULL;
IF (substr(l_num, length(l_num) - 2, 3) <> 0) THEN
l_return :=
to_char(to_date(substr(l_num, length(l_num) - 2, 3), 'J'), 'Jsp') || l_str(i) || l_return;
END IF;
l_num := substr(l_num, 1, length(l_num) - 3);
END LOOP;
RETURN l_return;
END;