from asktom
Code:
create or replace
  2  function spell_number( p_number in number )
  3  return varchar2
  4  as
  5      type myArray is table of varchar2(255);
  6      l_str    myArray := myArray( '',
  7                             ' thousand ', ' million ',
  8                             ' billion ', ' trillion ',
  9                             ' quadrillion ', ' quintillion ',
 10                             ' sextillion ', ' septillion ',
 11                             ' octillion ', ' nonillion ',
 12                             ' decillion ', ' undecillion ',
 13                             ' duodecillion ' );
 14
 15      l_num   varchar2(50) default trunc( p_number );
 16      l_return varchar2(4000);
 17  begin
 18      for i in 1 .. l_str.count
 19      loop
 20          exit when l_num is null;
 21
 22          if ( substr(l_num, length(l_num)-2, 3) <> 0 )
 23          then
 24             l_return := to_char(
 25                             to_date(
 26                              substr(l_num, length(l_num)-2, 3),
 27                                'J' ),
 28                         'Jsp' ) || l_str(i) || l_return;
 29          end if;
 30          l_num := substr( l_num, 1, length(l_num)-3 );
 31      end loop;
 32
 33      return l_return;
 34  end;
 35  /