Oracle Number to Word conversion

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL ) ||

       decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )

from dual

/

First one has a limitation of number of digits. Below one is good in terms of number of digits.

Set Serveroutput On 1000000

Declare

  V_Input NUMBER := &TESTED;

  Function Numbertowords(P_Number In Out Number) Return Varchar2 Is

    V_Words Varchar2(32767) := ' ';

    V_Temp Number;   

    Type Unitsmap Is Table Of Varchar2(250) Index By Binary_Integer;

    Type Tensmap Is Table Of Varchar2(250) Index By Binary_Integer;   

    V_Unitsmap Unitsmap ;

    V_Tensmap Tensmap   ;

  Begin

    V_Unitsmap(0) := 'Zero';

    V_Unitsmap(1) := 'One';

    V_Unitsmap(2) := 'Two';

    V_Unitsmap(3) := 'Three';

    V_Unitsmap(4) := 'Four';

    V_Unitsmap(5) := 'Five';

    V_Unitsmap(6) := 'Six';

    V_Unitsmap(7) := 'Seven';

    V_Unitsmap(8) := 'Eight';

    V_Unitsmap(9) := 'Nine';

    V_Unitsmap(10) := 'Ten';

    V_Unitsmap(11) := 'Eleven';

    V_Unitsmap(12) := 'Twelve';

    V_Unitsmap(13) := 'Thirteen';

    V_Unitsmap(14) := 'Fourteen';

    V_Unitsmap(15) := 'Fifteen';

    V_Unitsmap(16) := 'Sixteen';

    V_Unitsmap(17) := 'Seventeen';

    V_Unitsmap(18) := 'Eighteen';

    V_Unitsmap(19) := 'Nineteen';

    V_Tensmap(2)  := 'Twenty';

    V_Tensmap(3)  := 'Thirty';

    V_Tensmap(4)  := 'Forty';

    V_Tensmap(5)  := 'Fifty';

    V_Tensmap(6)  := 'Sixty';

    V_Tensmap(7)  := 'Seventy';

    V_Tensmap(8)  := 'Eighty';

    V_Tensmap(9)  := 'Ninety';

    If (P_Number = 0) Then

      Return 'Zero';

    End If;

    If (P_Number < 0) Then

      V_Temp := Abs(P_Number);     

      Return 'Minus ' || Numbertowords(V_Temp);

    End If;

    V_Temp := TRUNC(P_Number / 1000000);

    If ( V_Temp > 0) Then

      V_Words := V_WORDS || Numbertowords(V_Temp) || ' Million';

      P_Number := Mod(P_Number,1000000);

    End If;

    V_Temp := TRUNC(P_Number / 1000);

    If ( V_Temp > 0)Then

      V_Words := V_Words || Numbertowords(V_Temp) || ' Thousand';

      P_Number := Mod(P_Number,1000);

    End If;

    V_Temp := Trunc(P_Number / 100);

    If ( V_Temp > 0) Then

      V_Words := V_Words ||  Numbertowords(V_Temp) || ' Hundred ';

      P_Number := Mod(P_Number,100);

    End If;

    V_Temp := P_Number;

    If (V_Temp > 0) Then

      If (V_Words != ' ') Then

        V_Words := V_Words || 'And ';

       End If;

      If (V_Temp < 20) Then

        V_Words := V_Words || V_Unitsmap(V_Temp);

        Return V_Words;

      Else

       V_Temp := TRUNC(P_Number/ 10);

        V_Words := V_Words || V_Tensmap(V_Temp);

        If ((Mod(P_Number ,10)) > 0) Then

          V_Words :=V_Words|| '-' ||V_Unitsmap(Mod(P_Number,10));

        End If;

      End If;       

    End If;

    Return V_Words;

  End;

Begin   

  Dbms_Output.Put_Line(&TESTED||' ='||Numbertowords(V_Input));

End;