Query/Function for writing Amount into Words.

DECLARE
a number(15);
ans varchar2(1000);
BEGIN
a := &temp;
SELECT
DECODE(SIGN(LENGTH(a)-9),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-LENGTH(a),LENGTH(a)-9)),'J'),'Jsp') || ' Billion '
||DECODE(SIGN(LENGTH(a)-6),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),LENGTH(a)-8,3)),'J'),'Jsp') || ' Million '
||TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-6,6)),'J'),'Jsp'),
TO_CHAR(TO_DATE(a,'J'),'Jsp')),
DECODE(SIGN(LENGTH(a)-6),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-LENGTH(a),LENGTH(a)-6)),'J'),'Jsp') || ' Million '
||TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-6,6)),'J'),'Jsp'),
TO_CHAR(TO_DATE(a,'J'),'Jsp')))

|| ' Dollars '
INTO ans
FROM DUAL;
DBMS_OUTPUT.PUT_LINE(INITCAP(ans));
END;
/

DECLARE
a number(15);
ans varchar2(1000);
BEGIN
a := &temp;

SELECT
DECODE(SIGN(LENGTH(a)-8),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-LENGTH(a),LENGTH(a)-7)),'J'),'JSP')||' Crore ',
0,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-8,1)),'J'),'JSP')||' Crore ')
||
DECODE(SIGN(LENGTH(a)-6),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-7,2)),'J'),'JSP')||' Lakh ',
0,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-6,1)),'J'),'JSP')||' Lakh ')
||
DECODE(SIGN(LENGTH(a)-4),
1,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-5,2)),'J'),'JSP')||' Thousand ',
0,TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-4,1)),'J'),'JSP')||' Thousand ')
||
DECODE(SIGN(LENGTH(a)-2),
1,DECODE(SUBSTR(TO_CHAR(a),-3,3),'000',NULL,
TO_CHAR(TO_DATE(TO_NUMBER(SUBSTR(TO_CHAR(a),-3,3)),'J'),'JSP')),
0,TO_CHAR(TO_DATE(a,'J'),'JSP'),-1,TO_CHAR(TO_DATE(a,'J'),'JSP'))
|| ' Rupees'
INTO
ans
FROM
DUAL;

DBMS_OUTPUT.PUT_LINE(INITCAP(ans));
END;
/