Convert a number to word

select to_char(to_date('1983','J'),'JSP') from dual;

In this query the number is converting into Julian date then by using fromat  'SP' to spell out

Example

  select to_char(sysdate,'DDSP'),to_char(sysdate,'MMSP'),to_char(sysdate,'YYYYSP') from dual  -- Spells the day ,month and the year

 In this only value upto 5373484 can be spelled  ; giving greater value will result in the following error

 ORA-01854: Julian date must be between 1 and 5373484

 select to_char(to_date('190000083','J'),'JSP') from dual;

 ORA-01830: date format picture ends before converting entire input string


Query to Spell  Rupee and Paise

select
TO_CHAR(TO_DATE(substr(10000.123,1,instr(10000.123,'.')-1),'J'),'JSP')||' RUPEES AND  '||
replace(replace(replace(replace(TO_CHAR(TO_DATE(substr(10000.123,instr(10000.123,'.')+1,length(10000.123)),'J'),'JSP'),'MILLION',''),'HUNDRED',''),'THOUSAND',''),'-',' ')  ||' PAISE ONLY' as number_char
from dual;

----------

Query to convert NUMBER to words

In Oracle 9i


SELECT  LEVEL+1 asnumber FROM dual WHERE to_char(to_date(LEVEL+1,'J'), 'JSP')  = 'ONE THOUSAND'
CONNECT BY to_char(to_date(LEVEL,'J'), 'JSP')  != 'ONE THOUSAND';

OR

SELECT  LEVEL FROM dual WHERE to_char(to_date(LEVEL,'J'), 'JSP')  = 'ONE THOUSAND ONE HUNDRED ELEVEN'
CONNECT BY to_char(to_date(LEVEL-1,'J'), 'JSP')  != 'ONE THOUSAND ONE HUNDRED ELEVEN'
AND LEVEL < 10001  -- Your get out of jail clause!;


In Oracle 10g

select sp, n from (select 'FIVE THOUSAND ONE' sp from dual)
   model dimension by (1 dim) measures (0 n, sp)  rules iterate (10000) until (to_char(date '2000-01-01' +
(ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])  ( n[1]=ITERATION_NUMBER);


-------