A cool piece of PL/SQL – Spelling out a number in Oracle !!

Unlike the usual posts of mine, this one would be related to one interesting beauty of code I learnt today while I was investigating on Google for a “quick” solution to one interesting issue in Oracle at my work-place. This one has originated from a reader of asktom.oracle.com from India and is definitely one of the coolest piece of code I’ve encountered recently. These are the snippets that keep me engaged in IT and makes me wonder of the still unearthed possibilities of software programming. 

Originating from a simple functionality, Thomas Kyte, the Tom behind the above-stated AskTom website, ran on Oracle APEX, delivers a quick and simple solution of spelling out a number, no matter how big it is! Yes – you’ve heard it correctly – he has actually spelled a number. I curse myself to be a late-comer to a gem of a post like this one, but as they say – it is never late than sorry! You can follow Tom on tkyte.blogspot.com, as well as on the above website. 

Here’s the code:

create or replace
function spell_number( p_number in number )
return varchar2
     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);
     for i in 1 .. l_str.count
         exit when l_num is null;
         if ( substr(l_num, length(l_num)-2, 3) 0 )
            l_return := to_char(
                             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;

Once ran on the SQL*Plus prompt, it creates a function called “spell_number” which takes a number as input, however long it might be, and spells it out on the screen as output. 

Here’s a small test run, and its results:

spell_number( 12345678901234567890123456789012345678 )
from dual;

Twelve undecillion Three Hundred Forty-Five decill
ion Six Hundred Seventy-Eight nonillion Nine Hundr
ed One octillion Two Hundred Thirty-Four septillio
n Five Hundred Sixty-Seven sextillion Eight Hundre
d Ninety quintillion One Hundred Twenty-Three quad
rillion Four Hundred Fifty-Six trillion Seven Hund
red Eighty-Nine billion Twelve million Three Hundr
ed Forty-Five thousand Six Hundred Seventy-Eight

Everyone should be reminded – yet again – that developers rule! 

Note: The script above and the output is taken “as it is written and described” from AskTom website itself, the direct URL of which is:


I hope developers have enjoyed it. 

P.S. : There might be further scope of improvisation as per requirement. Please feel free to leave your comments on the same. 

This entry was posted in Computers, database, Oracle, PL/SQL, programming, Tom Kyte. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s