I want create a function for all currency
I want create a function in sql for currency like USD,RUPEES,POND,CENT.with the help of this Query..
CREATE OR REPLACE function NEWTON.amount_in_words (str5 number) return varchar2 is
LENT NUMBER(8);
STR1 NUMBER(10);
str2 varchar2(10);
str3 number(10);
STR4 NUMBER(10);
numstr2 number(10);
stt1 NUMBER(10);
stt2 NUMBER(10);
stt3 NUMBER(10);
stt4 NUMBER(10);
stt5 NUMBER(10);
VAR0 VARCHAR2(40);
var01 varchar2(40);
var1 varchar2(40);
var2 varchar2(40);
var3 varchar2(40);
var4 varchar2(40);
var5 varchar2(40);
var6 varchar2(40);
var7 varchar2(40);
VAR10 VARCHAR2(40);
RUPEE VARCHAR2(10);
exp_x exception;
RUPEE_IN_WORDS VARCHAR2(150);
ansr varchar2(180);
BEGIN
var6:=' RUPESS ' ; "(This is Hardcore Value..but its worng)"
VAR10:= substr(TO_NUMBER((ABS(STR5)-FLOOR(STR5))),2,2);
if length(var10) =1
then
var10:=var10*10;
end if;
if TO_NUMBER((ABS(STR5)-FLOOR(STR5)))=0
then
var0:=' Only';
else
var0:=' Paise '||initcap(TO_CHAR(TO_DATE(ROUND(VAR10),'J'),'JSP'))||' Only';
var01:=' and ';
end if;
STR1:=FLOOR(STR5);
lent:=length(str1);
if lent >10
then
RAISE EXP_X;
ELSE
str3:=to_number(str5);
if str5=0
then
ansr:= 'Zero Rupees Only';
else
str2:=reverse_strin(str1);
stt1:=reverse_strin(SUBSTR(str2,8,3));
stt2:=reverse_strin(SUBSTR(str2,6,2));
stt3:=reverse_strin(SUBSTR(str2,4,2));
stt4:=reverse_strin(SUBSTR(str2,3,1));
stt5:=reverse_strin(SUBSTR(str2,1,2)) ;
if stt1= 0
then
var1:=' ';
else
var1:=initcap(TO_CHAR(TO_DATE(ROUND(stt1),'J'),'JSP'))||' Crore ';
end if;
if stt2=0
then
var2:=' ';
else
var2:=initcap(TO_CHAR(TO_DATE(ROUND(stt2),'J'),'JSP'))||' Lakhs ';
end if;
if stt3=0
then
var3:=' ';
else
var3:=initcap(TO_CHAR(TO_DATE(ROUND(stt3),'J'),'JSP'))||' Thousand ';
end if;
if stt4=0
then
var4:='';
else
var4:=initcap(TO_CHAR(TO_DATE(ROUND(stt4),'J'),'JSP'))||' Hundred ';
end if;
if stt5=0
then
var5:='';
-- var6:='';
var01:='';
else
var5:=initcap(TO_CHAR(TO_DATE(ROUND(stt5),'J'),'JSP'));
end if;
if lent between 8 and 10
then
ansr:=var6||var1||var2||var3||var4||var5||var01||var0;
elsif
lent between 6 and 7
then
ansr:=var6||var2||var3||var4||var5||var01||var0;
elsif
lent between 4 and 5
then
ansr:=var6||var3||var4||var5||var01||var0;
elsif
lent = 3
then
ansr:=var6||var4||var5||var01||var0;
else
ansr:= var6||var5||var01||var0;
end if;
end if;
return ansr;
DBMS_OUTPUT.PUT_LINE(ansr);
dbms_output.put_line(numstr2);
END IF;
EXCEPTION
WHEN EXP_X THEN
DBMS_OUTPUT.PUT_LINE('PLEASE ENTER THE NUMBER<=10 DIGITS');
END;
/