Home » SQL & PL/SQL » SQL & PL/SQL » varchar to number
varchar to number [message #347491] Thu, 11 September 2008 20:31 Go to next message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

hi all,

i hv create a function that will return multiple rows. i wnt to return it as number. But it gives me a numeric error when i run it. Got any idea? Below is the code:

function exchangerate return number is

cursor c_rate
is
Select rate
from re06rttyp
where yr=:yr
and seqno=:seqno
and brncd=:brncd
and rate is not null;

exchange_rate number;
xrate varchar2(100);

Begin
for i in c_rate
loop
xrate := xrate||chr(9)||i.rate;
end loop;

If xrate is null then
xrate := :fcrate;

exchange_rate := to_number(xrate);
--dbms_output.put_line (exchange_rate );
return exchange_rate;

end if;

exchange_rate := to_number(xrate);
return exchange_rate;

end;
Re: varchar to number [message #347492 is a reply to message #347491] Thu, 11 September 2008 20:33 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

>it gives me a numeric error
ERROR? What error? I don't see any error!

[Updated on: Thu, 11 September 2008 20:34] by Moderator

Report message to a moderator

Re: varchar to number [message #347533 is a reply to message #347491] Fri, 12 September 2008 00:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
myura wrote on Fri, 12 September 2008 03:31
for i in c_rate
loop
xrate := xrate||chr(9)||i.rate;
end loop;

<snip>

exchange_rate := to_number(xrate);


How would you convert "1<TAB>2" to a number?
Re: varchar to number [message #347538 is a reply to message #347533] Fri, 12 September 2008 01:05 Go to previous messageGo to next message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

ouh...ok..thnks frank..
Re: varchar to number [message #347552 is a reply to message #347538] Fri, 12 September 2008 01:48 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Lucky us, we don't have to tell you the same more than twice:
LF
It seems that TO_NUMBER can't do the job against xrate || chr(9) || i.rate.
Re: varchar to number [message #347562 is a reply to message #347552] Fri, 12 September 2008 02:50 Go to previous message
myura
Messages: 66
Registered: July 2007
Location: Malaysia
Member

sorry..my mistake Neutral
Previous Topic: PLS-00103: Encountered the symbol "QRY_STMT" when expecting one
Next Topic: Running for multiple countries
Goto Forum:
  


Current Time: Sat Dec 03 01:23:20 CST 2016

Total time taken to generate the page: 0.15201 seconds