To spell a number in SQL [message #507803] |
Wed, 18 May 2011 01:29 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/eeed21864f80e5fa549e3c52be037eea?s=64&d=mm&r=g) |
poojamunj
Messages: 64 Registered: May 2011 Location: MUMBAI
|
Member |
|
|
hi all1
I have two fields in my form
In one field I am entering cetain amount
I want the conversion of that amount to char in next field
for e.g 1000 one thousand
how can i achieve this.
please help.
thnks.
|
|
|
|
|
|
|
|
|
|
Re: to spell a number in sql [message #508209 is a reply to message #508202] |
Fri, 20 May 2011 00:11 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
That should be easy to fix.
Oracle
ORA-06502: PL/SQL: numeric or value errorstring
Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).
Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.
|
|
|
Re: To spell a number in SQL [message #508717 is a reply to message #507803] |
Tue, 24 May 2011 03:27 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
oralover2006
Messages: 144 Registered: January 2010 Location: India
|
Senior Member |
|
|
first create this function in your Forms program unit then after entry in :TIT_AMT call this function as:
:RUPEES := GET_IN_WORDS (:TIT_AMT);
CREATE OR REPLACE FUNCTION GET_IN_WORDS (MNUM NUMBER) return varchar2 is
mil varchar2(50) := ' TRILLIONBILLION MILLION THOUSAND ';
mWords varchar2(180) := '';
mAmount number := 0;
ctr number(3) := 0;
num3 number(3) := 0;
num4 number(3) := instr(to_char(mNum),'.');
numDecimal varchar2(5) := 0;
begin
if num4 = 0 then
numDecimal := '0.00';
mAmount := mNum;
else
numDecimal := '0'||ltrim(substr(to_char(mNum),num4,4));
mAmount := to_number(substr(to_char(mNum),1,num4-1));
end if;
LOOP
ctr := nvl(ctr,0) + 1;
if ctr > 5 then
exit ;
end if;
Num3 := to_number(nvl(substr(lpad(to_char(mAmount),15,'0'),ctr*3-2,3),'0'));
if Num3 > 0 then
mWords := mWords || ' ' || to_char(to_date(Num3,'J'),'JSP');
mWords := mWords ||' '|| rtrim(substr(Mil,ctr*8,8));
end if;
END LOOP;
mWords := mWords || ' And '||numDecimal;
return (mwords);
end;
/
you have to play with this function to trap/catch errors, if any.
don't forget to give your feedback.
regards.
|
|
|
|
|