Create Function in Oracle 10g

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

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.