TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
shashank srivastav
NA
31
3.1k
Function
Jun 2 2014 4:29 AM
I am sending the query related to sql..
I want create a function for all currency..
I this qurey (var6:=' RUPESS ' ;)...assined the hardcore..so want remove this hardcore nd make the query for all currency..
Plzzz help me
Thanks
Shashank Srivastav
Noida
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:='RUPEES' ;
(This is hardcode...but it's wrong)
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;
/
Reply
Answers (
6
)
I want create a function for all currency
dnt retrive data from table