Home » SQL & PL/SQL » SQL & PL/SQL » Convert Number to Word (currency conversion)
icon1.gif  Convert Number to Word (currency conversion) [message #221184] Sat, 24 February 2007 21:20 Go to next message
Solary
Messages: 1
Registered: February 2007
Junior Member
Hi all

I am trying to write up a function to convert number to word. I was kinda new to PL/SQL on oracle. In order to write up a quick function I try to adapt a function from PL/SQL on MS SQL. The script runs fine on PLEdit without any warning, but on SQL Plus I try to run;

Select spell_number_thai(100) from dual;

and got an Oracle Warning
ORA-06502: PL/SQL: numberic or value erros: charecter to number conversion error
ORA-06512: at Spell_number_thai, line 118

Though changing value to 0 through 9 will return an expect result. 8)

Just wonder what I have done wrong?

Here's the modification so far;



create or replace
function spell_number_thai( p_number in number )
return varchar2

as

sMoney varchar(30); nLenMoney number; sDecMoney varchar(30); nLenDec number;
sTemp varchar(30) default CAST( p_number as varchar) ;
nLenTemp number default LENGTH(sTemp); nDotPos number default SUBSTR('.', sTemp);
sReturn varchar(150);
sTemp1 varchar(30); sTemp2 varchar(255); sTemp3 varchar(255); s1 number;
s2 varchar(30); s3 varchar(30); s4 varchar(30);
pos number;



Begin

IF nDotPos > 0
Then
sMoney := SUBSTR(sTemp, 1, nDotPos - 1);
sDecMoney := SUBSTR(sTemp, nDotPos + 1, LENGTH(sTemp) - nDotPos);
nLenMoney := LENGTH(sMoney) ;
nLenDec := LENGTH(RTRIM(sDecMoney));

ELSE

sMoney := sTemp;
nLenMoney := LENGTH(sMoney);
END IF;

pos := 1;
WHILE pos <= LENGTH(sMoney) loop

IF pos = 1
THEN
s1 := SUBSTR(sMoney, nLenMoney, 1);
sTemp1 :=
case when s1 = 0 and nLenMoney = 1 then 'ศูนย์'
when s1 = 0 and nLenMoney > 1 then ''
when s1 = 1 and nLenmoney = 1 then 'หนึ่ง'
when s1 = 1 then 'เอ็ด'
when s1 = 2 then 'สอง'
when s1 = 3 then 'สาม'
when s1 = '4' then 'สี่'
when s1 = '5' then 'ห้า'
when s1 = '6' then 'หก'
when s1 = '7' then 'เจ็ด'
when s1 = '8' then 'แปด'
when s1 = '9' then 'เก้า'
end ;
pos := pos + 1;
sReturn := sTemp1;

ELSE

s2 := SUBSTR(sMoney, nLenMoney - pos + 1, 1);
sTemp2 :=
case when s2 = '0' then ''
when s2 = '1' and pos in (2,8) then 'สิบ'
when s2 = '1' and pos in (3,9) then 'หนึ่งร้อย'
when s2 = '1' and pos in (4,10) then 'หนึ่งพัน'
when s2 = '1' and pos in (5,11) then 'หนึ่งหมื่น'
when s2 = '1' and pos in (6,12) then 'หนึ่งแสน'
when s2 = '1' and pos =7 and nLenmoney = 7 then 'หนึ่งล้าน'
when s2 = '1' and pos = 7 and nLenMoney > 7 then 'เอ็ดล้าน'
when s2 = '1' and pos = 13 and nLenmoney = 13 then 'หนึ่งล้าน'
when s2 = '1' and pos = 13 and nLenMoney > 13 then 'เอ็ดล้าน'
when s2 = '2' and pos in (2,8) then 'ยี่สิบ'
when s2 = '2' and pos in (3,9) then 'สองร้อย'
when s2 = '2' and pos in (4,10) then 'สองพัน'
when s2 = '2' and pos in (5,11) then 'สองหมื่น'
when s2 = '2' and pos in (6,12) then 'สองแสน'
when s2 = '2' and pos in (7,13) then 'สองล้าน'
when s2 = '3' and pos in (2,8) then 'สามสิบ'
when s2 = '3' and pos in (3,9) then 'สามร้อย'
when s2 = '3' and pos in (4,10) then 'สามพัน'
when s2 = '3' and pos in (5,11) then 'สามหมื่น'
when s2 = '3' and pos in (6,12) then 'สามแสน'
when s2 = '3' and pos in (7,13) then 'สามล้าน'
when s2 = '4' and pos in (2,8) then 'สี่สิบ'
when s2 = '4' and pos in (3,9) then 'สี่ร้อย'
when s2 = '4' and pos in (4,10) then 'สี่พัน'
when s2 = '4' and pos in (5,11) then 'สี่หมื่น'
when s2 = '4' and pos in (6,12) then 'สี่แสน'
when s2 = '4' and pos in (7,13) then 'สี่ล้าน'
when s2 = '5' and pos in (2,8) then 'ห้าสิบ'
when s2 = '5' and pos in (3,9) then 'ห้าร้อย'
when s2 = '5' and pos in (4,10) then 'ห้าพัน'
when s2 = '5' and pos in (5,11) then 'ห้าหมื่น'
when s2 = '5' and pos in (6,12) then 'ห้าแสน'
when s2 = '5' and pos in (7,13) then 'ห้าล้าน'
when s2 = '6' and pos in (2,8) then 'หกสิบ'
when s2 = '6' and pos in (3,9) then 'หกร้อย'
when s2 = '6' and pos in (4,10) then 'หกพัน'
when s2 = '6' and pos in (5,11) then 'หกหมื่น'
when s2 = '6' and pos in (6,12) then 'หกแสน'
when s2 = '6' and pos in (7,13) then 'หกล้าน'
when s2 = '7' and pos in (2,8) then 'เจ็ดสิบ'
when s2 = '7' and pos in (3,9) then 'เจ็ดร้อย'
when s2 = '7' and pos in (4,10) then 'เจ็ดพัน'
when s2 = '7' and pos in (5,11) then 'เจ็ดหมื่น'
when s2 = '7' and pos in (6,12) then 'เจ็ดแสน'
when s2 = '7' and pos in (7,13) then 'เจ็ดล้าน'
when s2 = '8' and pos in (2,8) then 'แปดสิบ'
when s2 = '8' and pos in (3,9) then 'แปดร้อย'
when s2 = '8' and pos in (4,10) then 'แปดพัน'
when s2 = '8' and pos in (5,11) then 'แปดหมื่น'
when s2 = '8' and pos in (6,12) then 'แปดแสน'
when s2 = '8' and pos in (7,13) then 'แปดล้าน'
when s2 = '9' and pos in (2,8) then 'เก้าสิบ'
when s2 = '9' and pos in (3,9) then 'เก้าร้อย'
when s2 = '9' and pos in (4,10) then 'เก้าพัน'
when s2 = '9' and pos in (5,11) then 'เก้าหมื่น'
when s2 = '9' and pos in (6,12) then 'เก้าแสน'
when s2 = '9' and pos in (7,13) then 'เก้าล้าน'
end ;
pos := pos + 1 ;
sReturn := sTemp2 + sReturn;
END IF ;
END LOOP ;

s1 := '';
s2 := '' ;
s3 := '' ;
s4 := '' ;
IF nDotPos > 0
THEN
s1 := SUBSTR(sDecMoney, 1, 1);
s2 := SUBSTR(sDecMoney, 2, 1);
s3 :=
case when s1 = '0' then ''
when s1 = '1' then 'สิบ'
when s1 = '2' then 'ยี่สิบ'
when s1 = '3' then 'สามสิบ'
when s1 = '4' then 'สี่สิบ'
when s1 = '5' then 'ห้าสิบ'
when s1 = '6' then 'หกสิบ'
when s1 = '7' then 'เจ็ดสิบ'
when s1 = '8' then 'แปดสิบ'
when s1 = '9' then 'เก้าสิบ'
end +
case when s2 = '0' then ''
when s2 = '1' and s2 = '0' then 'หนึ่ง'
when s2 = '1' and s2 <> '0' then 'เอ็ด'
when s2 = '2' then 'สอง'
when s2 = '3' then 'สาม'
when s2 = '4' then 'สี่'
when s2 = '5' then 'ห้า'
when s2 = '6' then 'หก'
when s2 = '7' then 'เจ็ด'
when s2 = '8' then 'แปด'
when s2 = '9' then 'เก้า'
end + 'สตางค์' ;

IF s1 = '0' and s2 = '0'
THEN sReturn := sReturn + 'บาทถ้วน';
ELSE
sReturn := sReturn + 'บาท' + s3 ;
END IF;

IF nLenMoney > 13
THEN sReturn := 'Over Flow' ;
END IF;
END IF;
RETURN sReturn ;
END spell_number_thai;


[Updated on: Sat, 24 February 2007 21:20]

Report message to a moderator

Re: Convert Number to Word (currency conversion) [message #221194 is a reply to message #221184] Sun, 25 February 2007 01:37 Go to previous messageGo to next message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Probably this thread can help you
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1407603857650

Gints Plivna
http://www.gplivna.eu
Re: Convert Number to Word (currency conversion) [message #221432 is a reply to message #221184] Mon, 26 February 2007 13:42 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I do not see your logic either deduce what contains your line 118 (where is the error reported). Just noticed you use '+' for concatenating strings. So replace '+' with '||' for VARCHAR2 concatenation and let it only for number addition.
Previous Topic: Explain Plan
Next Topic: Alter in PL/SQL
Goto Forum:
  


Current Time: Sun Dec 04 08:55:59 CST 2016

Total time taken to generate the page: 0.07823 seconds