How to convert number to words in oracle

Method 1:
A common method for converting a number to words is to use the TO_DATE function and 'j' format to convert the number to a julian date (the number of days since January 1, 4712 B.C.), then use the TO_CHAR function and 'jsp' format to spell the julian date.

I am using below query to convert for this number(1235487) to word.

SELECT TO_CHAR (TO_DATE (1235487, 'j'), 'Jsp') FROM DUAL;

                                                                    OR

SELECT AP_AMOUNT_UTILITIES_PKG.ap_convert_number(1235487) FROM DUAL;

It's return for : One Million Two Hundred Thirty-Five Thousand Four Hundred Eighty-Seven

But i want result like :

Twelve Lakh Thirty Five Thousand Four Hundred Eighty Seven Only

And also it has range from 1 to 5373484 is we put value after 5373484, like 5373485

SELECT TO_CHAR (TO_DATE (5373485, 'j'), 'Jsp') FROM DUAL;

then it will throw you an error

"ORA-01854: julian date must be between 1 and 5373484"

How to achieve this?

Solution:
If you wish to spell numbers greater than the maximum julian date allowed (5373484) or if you wish to include decimal places, or if you wish to have the output in another format or language, then you will need a more complex function that works for larger numbers and include decimal places, other formats, and other languages.

======================Function for Indian Format========================

create or replace function amt_to_word( p_number in number )

return varchar2

as

type myArray is table of varchar2(255);

l_str myArray := myArray(' Thousand ', ' Lakh ',' Crore ', ' Arav ',' Kharav ',' Neel ',' Padma ', ' Shankh ');

l_num varchar2(50) default trunc( p_number );

l_return varchar2(4000);

begin

if (substr( l_num, length(l_num)-2,3 )<>0)

then

l_return := REPLACE (to_char(to_date(substr(l_num, length(l_num)-2, 3),'J'),'Jsp'),'-',' ');

end if;

l_num:=substr( l_num, 1,length(l_num)-3);

for i in 1 .. l_str.count

loop

exit when l_num is null;

if (substr( l_num, length(l_num)-1,2 )<>0)

then

l_return := REPLACE (to_char(to_date(substr(l_num, length(l_num)-1, 2),'J' ),'Jsp'),'-',' ')|| l_str(i) || l_return;

end if;

l_num:=substr( l_num, 1,length(l_num)-2);

end loop;

if to_char( p_number ) like '%.%'

then

l_num := substr(round(p_number,2),instr(p_number,'.')+1);

if (length(substr(round(p_number,2),instr(p_number,'.')+1 )))=1

then

l_num := to_number(to_char(l_num) ||'0');

end if;

if l_num>0

then

l_return :=l_return ||' And' || REPLACE (to_char(to_date(l_num,'J'),' Jsp'),'-',' ')||' Paise';

end if;

end if;

--return(l_return);

return(l_return||' Only');

end amt_to_word;

======================Query=================================

SQL>select amt_to_word(5373485) from dual;

It's return for: Fifty Three Lakh Seventy Three Thousand Four Hundred Eighty Five Only

======================Function for International Format==================

CREATE OR REPLACE FUNCTION APPS.num_to_word (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 (abs(p_number));

l_return VARCHAR2 (4000);

V_POS NUMBER;

V_FRACTION VARCHAR2(1000);

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;

------------------FOR FRACTION VALUE------------------------

V_POS := INSTR(TO_CHAR(P_NUMBER),'.');

IF V_POS > 0 THEN

SELECT INITCAP(TO_CHAR(TO_DATE(SUBSTR(P_NUMBER,V_POS+1,LENGTH(P_NUMBER)), 'J'), 'JSP')) INTO V_FRACTION FROM DUAL;

l_return := l_return||'Point '||V_FRACTION;

END IF;

RETURN l_return;

END;

/

======================Query=================================

SQL>select num_to_word(5373485) from dual

It's return for: Five million Three Hundred Seventy-Three thousand Four Hundred Eighty-Five

Method 2: 

Number to Word conversion in RTF

xdofx vs xdoxslt

<?xdofx:expression?> for extended SQL functions

<?xdoxslt:expression?> for extended XSL functions.

You cannot mix xdofx statements with XSL expressions in the same context

This function enables the conversion of numbers to words for RTF template output. This is a common requirement for check printing.

The new function is “to_check_number”. The syntax of this function is

<?xdofx:to_check_number(amount, precisionOrCurrency, caseType, decimalStyle)?>

RTF Template Entry

<?xdofx:to_check_number(12345.67, 2)?>

Returned Output

Twelve thousand three hundred forty-five and 67/100

Alternative

<?xdoxslt:toWordsAmt(TEST_AMOUNT)?>

Then it will display amount in words in Indian currency means Rupees not millions but it's showing some wrong spelling of particular number like Fourty.

Example:

<?xdoxslt: toWordsAmt(1244454.88)?>

Then it will display as

Twelve Lakh Fourty Four Thousand Four Hundred Fifty Four and paise Eighty Eight

Alternative

Use the following function in rdf level

IBY_AMOUNT_IN_WORDS.Get_Amount_In_Words(TEST_AMOUNT)