How to convert numbers into character

Function Spell Numbers. 

Different Questions regarding converting numbers into character.
How to spell numbers?
How to convert Amount of numbers into text?
How to spell 6758493 six million...?
So here is answer.
According to Database 11g R1 Compiler or results.
Way 1:
The first way to resolve your problem is the simple statement.
SQL> select to_char( to_date(458784, 'J'), 'JSP') spell from dual;

SPELL
-----------------------------------------------------------
FOUR HUNDRED FIFTY-EIGHT THOUSAND SEVEN HUNDRED EIGHTY-FOUR

SQL> select to_char( to_date(956844, 'J'), 'JSP') spell from dual;

SPELL
--------------------------------------------------------
NINE HUNDRED FIFTY-SIX THOUSAND EIGHT HUNDRED FORTY-FOUR


SQL> select to_char( to_date(1956844, 'J'), 'JSP') spell from dual;

SPELL
--------------------------------------------------------------------
ONE MILLION NINE HUNDRED FIFTY-SIX THOUSAND EIGHT HUNDRED FORTY-FOUR

But this function has limitation. 

Till some specified number it will give you the output other wise it will return an error.

SQL> select to_char( to_date(6956844, 'J'), 'JSP') spell from dual;

select to_char( to_date(6956844, 'J'), 'JSP') spell from dual
                        *
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484


So you can find out the return message of compiler.
The limit is also define.
See the last limit results.
SQL> select to_char( to_date(5373484, 'J'), 'JSP') spell from dual;

SPELL
--------------------------------------------------------------------------
FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR
Now even you will increase a single number in the value it will return you the error.

SQL> select to_char( to_date(5373485, 'J'), 'JSP') spell from dual;

select to_char( to_date(5373485, 'J'), 'JSP') spell from dual
                        *
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
So the limit is decided.
But what if we require to work on more values.
Because business is not end. We have more values to work yet.
So the second and most reliable solution is given below.

Way 2:


Create the function on database level. 

--*************************************************

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 upper(l_return)||'  Rs. Only';

  end;

/

--*************************************************

Hope this is helpful.