Question:

I am creating a Function in Oracle 10g  coding is as below but when  I am going to compile it's giving error  "IN  is not a valid inter value". 

CREATEORREPLACEFUNCTIONNumtoword(nom INNUMBER,Curr 
INVARCHAR2:='',mincurr INVARCHAR2:='paise')RETURNVARCHAR2ISword 
VARCHAR2(2000);no1 VARCHAR2(30);no2
VARCHAR2(30);BEGINno1 :=TO_CHAR(nom);no2 
:=TO_CHAR(TRUNC(nom));SELECTDECODE(nom,0,'Rs.
Zero',DECODE(TRUNC(nom),0,TO_CHAR(TO_DATE(TO_CHAR(TRUNC(100*nom)),'J'),
'JSP')||
mincurr ,DECODE(SIGN(nom-TRUNC(nom)),0
-,DECODE(SIGN(LENGTH(no1)-6),1,Curr||TO_CHAR(TO_DATE(no1,'J'),'JSP'),DE
CODE
-(SIGN(LENGTH(no1)-8),1,Curr
||TO_CHAR(TO_DATE(SUBSTR(no1,-1*LENGTH(no1),LENGTH(no1)-5),'J'),'JSP')|
|||' Lakhs
'||DECODE(SUBSTR(no1,-5),'00000',NULL,TO_CHAR(TO_DATE(SUBSTR(no1,-5),'J
'),'JSP')),Curr

||TO_CHAR(TO_DATE(SUBSTR(no1,-1*LENGTH(no1),LENGTH(no1)-7),'J'),'JSP')|
|||' CRORES
'||DECODE(SUBSTR(no1,-7,2),'00',NULL,TO_CHAR(TO_DATE(SUBSTR(no1,-7,2),'J'),'JSP')||'
 LAKHS
')||DECODE(SUBSTR(no1,-5),'00000',NULL,TO_CHAR(TO_DATE(SUBSTR(no1,-5),'
J'),'JSP')))),DECODE

-(SIGN(LENGTH(no2)-6),1,Curr||TO_CHAR(TO_DATE(no2,'J'),'JSP'),DECODE
-(SIGN(LENGTH(no2)-8),1,Curr
||TO_CHAR(TO_DATE(SUBSTR(no2,-1*LENGTH(no2),LENGTH(no2)-5),'J'),'JSP')|
|||' Lakhs
'||DECODE(SUBSTR(no2,-5),'00000',NULL,TO_CHAR(TO_DATE(SUBSTR(no2,-5),'J
'),'JSP')),Curr

||TO_CHAR(TO_DATE(SUBSTR(no2,-1*LENGTH(no2),LENGTH(no2)-7),'J'),'JSP')|
|||' CRORES
'||DECODE(SUBSTR(no2,-7,2),'00',NULL,TO_CHAR(TO_DATE(SUBSTR(no2,-7,2),'J'),'JSP')||'
 LAKHS
')||DECODE(SUBSTR(no2,-5),'00000',NULL,TO_CHAR(TO_DATE(SUBSTR(no2,-5),'
J'),'JSP'))))||  ' and '||TO_CHAR(TO_DATE(TO_CHAR(TRUNC(100*(nom 
-TRUNC(nom)))),'J'),'JSP')||
mincurr)
)
)INTOwordFROMdual;RETURN(INITCAP(word)||' Only.');END

Answer:

Please find the solution as following:

CREATE OR REPLACE FUNCTION Numtoword(nom IN NUMBER,Curr IN VARCHAR2:='',mincurr IN VARCHAR2:='paise')RETURN VARCHAR2 IS word VARCHAR2(2000);
no1 VARCHAR2(30);no2
VARCHAR2(30);
BEGIN
BEGIN
no1 :=TO_CHAR(nom);
no2 :=TO_CHAR(TRUNC(nom));
SELECT
DECODE(nom,0,'Rs.Zero',
DECODE(TRUNC(nom),0,TO_CHAR(TO_DATE(TO_CHAR(TRUNC(100*nom)),'J'),'JSP')||
mincurr ,
DECODE(SIGN(nom-TRUNC(nom)),'0-',DECODE(SIGN(LENGTH(no1)-6),1,Curr||TO_CHAR(TO_DATE(no1,'J'),'JSP'),
DECODE(SIGN(LENGTH(no1)-8),1,Curr)
||TO_CHAR(TO_DATE(SUBSTR(no1,-1*LENGTH(no1),LENGTH(no1)-5),'J'),'JSP')||
||' Lakhs'||DECODE(SUBSTR(no1,-5),'00000',
NULL,TO_CHAR(TO_DATE(SUBSTR(no1,-5),'J'),'JSP')),
Curr ||TO_CHAR(TO_DATE(SUBSTR(no1,-1*LENGTH(no1),LENGTH(no1)-7),'J'),'JSP')||' CRORES '|| DECODE(SUBSTR(no1,-7,2),'00',NULL,TO_CHAR(TO_DATE(SUBSTR(no1,-7,2),'J'),'JSP')||'
LAKHS
 

')||DECODE(SUBSTR(no1,-5),'00000',NULL,TO_CHAR(TO_DATE(SUBSTR(no1,-5),'J'),'JSP'))),
DECODE(SIGN(LENGTH(no2)-6),1,Curr||TO_CHAR(TO_DATE(no2,'J'),'JSP'),
DECODE(SIGN(LENGTH(no2)-8),1,Curr
||TO_CHAR(TO_DATE(SUBSTR(no2,-1*LENGTH(no2),LENGTH(no2)-5),'J'),'JSP')||
||' Lakhs
'||DECODE(SUBSTR(no2,-5),'00000',NULL,TO_CHAR(TO_DATE(SUBSTR(no2,-5),'J'),'JSP')),Curr

||TO_CHAR(TO_DATE(SUBSTR(no2,-1*LENGTH(no2),LENGTH(no2)-7),'J'),'JSP')||
||' CRORES
'||DECODE(SUBSTR(no2,-7,2),'00',NULL,TO_CHAR(TO_DATE(SUBSTR(no2,-7,2),'J'),'JSP')||'
LAKHS
')||DECODE(SUBSTR(no2,-5),'00000',NULL,TO_CHAR(TO_DATE(SUBSTR(no2,-5),'J'),'JSP'))))||
' and '||TO_CHAR(TO_DATE(TO_CHAR(TRUNC(100*(nom -TRUNC(nom)))),'J'),'JSP')||
mincurr)
)
)
INTO word FROM dual;
RETURN(INITCAP(word)||' Only.');
END;
END;

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips