sitaparijat
asked on
Issue with an Oracle function works in .
Hi!,
Follow-up to https://www.experts-exchange.com/questions/21154434/Oracle-function-works-in-8-0-5-but-fails-in-Oracle-8-1-7.html.
The function, whose text is provided below, is used to provide the number in text. It works in Oracle 8.0.5 but fails in 8.1.7.
It works upto three digits but fails beyond 999. Pl see the query results below:
SQLWKS> select newmsr(999) from dual
2>
NEWMSR(999)
--------------------------
NINE HUNDRED NINETY NINE
1 row selected.
SQLWKS> select newmsr(100) from dual
2>
NEWMSR(100)
--------------------------
ONE HUNDRED
1 row selected.
SQLWKS> select newmsr(1000) from dual
2>
NEWMSR(1000)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 100
ORA-06512: at line 1
Btw, the same function works fine on Oracle 8.0.5, see the results in the other window.
SQLWKS> select newmsr(1000) from dual
2>
NEWMSR(1000)
--------------------------
ONE THOUSAND
1 row selected.
Thanks in advance,
Srini
The function text is given below.:-
FUNCTION nsc_num_to_words (
temp_var IN CHAR )
RETURN VARCHAR2 IS
input_data CHAR(30);
alpha_var CHAR(30);
total_var VARCHAR2(2000);
chk_number1 NUMBER(1);
chk_number2 NUMBER(1);
chk_number3 NUMBER(1);
last_two NUMBER(2);
start_position NUMBER;
array_index NUMBER;
higher_position NUMBER;
disp_position NUMBER;
use_number NUMBER;
TYPE data_text IS TABLE OF CHAR(40)
INDEX BY binary_integer;
one_thru_nine data_text;
ten_thru_ninety data_text;
higher_group data_text;
disp_var data_text;
BEGIN
one_thru_nine (001) := ' ONE';
one_thru_nine (002) := ' TWO';
one_thru_nine (003) := ' THREE';
one_thru_nine (004) := ' FOUR';
one_thru_nine (005) := ' FIVE';
one_thru_nine (006) := ' SIX';
one_thru_nine (007) := ' SEVEN';
one_thru_nine (008) := ' EIGHT';
one_thru_nine (009) := ' NINE';
one_thru_nine (010) := ' TEN';
one_thru_nine (011) := ' ELEVEN';
one_thru_nine (012) := ' TWELVE';
one_thru_nine (013) := ' THIRTEEN';
one_thru_nine (014) := ' FOURTEEN';
one_thru_nine (015) := ' FIFTEEN';
one_thru_nine (016) := ' SIXTEEN';
one_thru_nine (017) := ' SEVENTEEN';
one_thru_nine (018) := ' EIGHTEEN';
one_thru_nine (019) := ' NINETEEN';
ten_thru_ninety (001) := ' TEN';
ten_thru_ninety (002) := ' TWENTY';
ten_thru_ninety (003) := ' THIRTY';
ten_thru_ninety (004) := ' FORTY';
ten_thru_ninety (005) := ' FIFTY';
ten_thru_ninety (006) := ' SIXTY';
ten_thru_ninety (007) := ' SEVENTY';
ten_thru_ninety (008) := ' EIGHTY';
ten_thru_ninety (009) := ' NINETY';
higher_group (001) := ' HUNDRED';
higher_group (002) := ' THOUSAND';
higher_group (003) := ' MILLION';
higher_group (004) := ' BILLION';
higher_group (005) := ' TRILLION';
higher_group (006) := ' QUADRILLION';
higher_group (007) := ' QUINTILLION';
higher_group (008) := ' SEXTILLION';
higher_group (009) := ' SEPTILLION';
higher_group (010) := ' OCTILLION';
input_data := temp_var;
SELECT NVL(LENGTH(temp_var),0)
INTO array_index
FROM DUAL;
higher_position := 1;
disp_position := 0;
start_position := 0;
WHILE ( array_index > start_position )
LOOP
IF ( higher_position > 1 ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (higher_position);
END IF;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number1 := SUBSTR(input_data,use_numb
ELSE
chk_number1 := 0;
END IF;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number2 := SUBSTR(input_data,use_numb
ELSE
chk_number2 := 0;
END IF;
last_two := SUBSTR(input_data,use_numb
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number3 := SUBSTR(input_data,use_numb
ELSE
chk_number3 := 0;
END IF;
IF ( higher_position > 1 AND
chk_number1 = 0 AND
chk_number2 = 0 AND
chk_number3 = 0 ) THEN
disp_position := disp_position - 1;
disp_var (disp_position) := ' ';
END IF;
IF ( chk_number1 <> 0 AND
chk_number1 <> ' ' AND
chk_number2 <> '1' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number1);
END IF;
IF ( chk_number2 <> 0 AND
chk_number2 <> ' ' ) THEN
IF ( chk_number1 <> '0' AND
chk_number1 <> ' ' AND
chk_number2 = '1' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (last_two);
ELSE
disp_position := disp_position + 1;
disp_var (disp_position) := ten_thru_ninety (chk_number2);
END IF;
END IF;
IF ( chk_number3 <> 0 AND
chk_number3 <> ' ' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (1) ;
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number3) ;
END IF;
higher_position := higher_position + 1 ;
END LOOP;
higher_position := disp_position ;
WHILE ( higher_position > 0 )
LOOP
total_var := RTRIM(total_var) || RTRIM( disp_var(higher_position) );
higher_position := higher_position - 1;
END LOOP;
RETURN total_var;
END;
Follow-up to https://www.experts-exchange.com/questions/21154434/Oracle-function-works-in-8-0-5-but-fails-in-Oracle-8-1-7.html.
The function, whose text is provided below, is used to provide the number in text. It works in Oracle 8.0.5 but fails in 8.1.7.
It works upto three digits but fails beyond 999. Pl see the query results below:
SQLWKS> select newmsr(999) from dual
2>
NEWMSR(999)
--------------------------
NINE HUNDRED NINETY NINE
1 row selected.
SQLWKS> select newmsr(100) from dual
2>
NEWMSR(100)
--------------------------
ONE HUNDRED
1 row selected.
SQLWKS> select newmsr(1000) from dual
2>
NEWMSR(1000)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 100
ORA-06512: at line 1
Btw, the same function works fine on Oracle 8.0.5, see the results in the other window.
SQLWKS> select newmsr(1000) from dual
2>
NEWMSR(1000)
--------------------------
ONE THOUSAND
1 row selected.
Thanks in advance,
Srini
The function text is given below.:-
FUNCTION nsc_num_to_words (
temp_var IN CHAR )
RETURN VARCHAR2 IS
input_data CHAR(30);
alpha_var CHAR(30);
total_var VARCHAR2(2000);
chk_number1 NUMBER(1);
chk_number2 NUMBER(1);
chk_number3 NUMBER(1);
last_two NUMBER(2);
start_position NUMBER;
array_index NUMBER;
higher_position NUMBER;
disp_position NUMBER;
use_number NUMBER;
TYPE data_text IS TABLE OF CHAR(40)
INDEX BY binary_integer;
one_thru_nine data_text;
ten_thru_ninety data_text;
higher_group data_text;
disp_var data_text;
BEGIN
one_thru_nine (001) := ' ONE';
one_thru_nine (002) := ' TWO';
one_thru_nine (003) := ' THREE';
one_thru_nine (004) := ' FOUR';
one_thru_nine (005) := ' FIVE';
one_thru_nine (006) := ' SIX';
one_thru_nine (007) := ' SEVEN';
one_thru_nine (008) := ' EIGHT';
one_thru_nine (009) := ' NINE';
one_thru_nine (010) := ' TEN';
one_thru_nine (011) := ' ELEVEN';
one_thru_nine (012) := ' TWELVE';
one_thru_nine (013) := ' THIRTEEN';
one_thru_nine (014) := ' FOURTEEN';
one_thru_nine (015) := ' FIFTEEN';
one_thru_nine (016) := ' SIXTEEN';
one_thru_nine (017) := ' SEVENTEEN';
one_thru_nine (018) := ' EIGHTEEN';
one_thru_nine (019) := ' NINETEEN';
ten_thru_ninety (001) := ' TEN';
ten_thru_ninety (002) := ' TWENTY';
ten_thru_ninety (003) := ' THIRTY';
ten_thru_ninety (004) := ' FORTY';
ten_thru_ninety (005) := ' FIFTY';
ten_thru_ninety (006) := ' SIXTY';
ten_thru_ninety (007) := ' SEVENTY';
ten_thru_ninety (008) := ' EIGHTY';
ten_thru_ninety (009) := ' NINETY';
higher_group (001) := ' HUNDRED';
higher_group (002) := ' THOUSAND';
higher_group (003) := ' MILLION';
higher_group (004) := ' BILLION';
higher_group (005) := ' TRILLION';
higher_group (006) := ' QUADRILLION';
higher_group (007) := ' QUINTILLION';
higher_group (008) := ' SEXTILLION';
higher_group (009) := ' SEPTILLION';
higher_group (010) := ' OCTILLION';
input_data := temp_var;
SELECT NVL(LENGTH(temp_var),0)
INTO array_index
FROM DUAL;
higher_position := 1;
disp_position := 0;
start_position := 0;
WHILE ( array_index > start_position )
LOOP
IF ( higher_position > 1 ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (higher_position);
END IF;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number1 := SUBSTR(input_data,use_numb
ELSE
chk_number1 := 0;
END IF;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number2 := SUBSTR(input_data,use_numb
ELSE
chk_number2 := 0;
END IF;
last_two := SUBSTR(input_data,use_numb
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number3 := SUBSTR(input_data,use_numb
ELSE
chk_number3 := 0;
END IF;
IF ( higher_position > 1 AND
chk_number1 = 0 AND
chk_number2 = 0 AND
chk_number3 = 0 ) THEN
disp_position := disp_position - 1;
disp_var (disp_position) := ' ';
END IF;
IF ( chk_number1 <> 0 AND
chk_number1 <> ' ' AND
chk_number2 <> '1' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number1);
END IF;
IF ( chk_number2 <> 0 AND
chk_number2 <> ' ' ) THEN
IF ( chk_number1 <> '0' AND
chk_number1 <> ' ' AND
chk_number2 = '1' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (last_two);
ELSE
disp_position := disp_position + 1;
disp_var (disp_position) := ten_thru_ninety (chk_number2);
END IF;
END IF;
IF ( chk_number3 <> 0 AND
chk_number3 <> ' ' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (1) ;
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number3) ;
END IF;
higher_position := higher_position + 1 ;
END LOOP;
higher_position := disp_position ;
WHILE ( higher_position > 0 )
LOOP
total_var := RTRIM(total_var) || RTRIM( disp_var(higher_position) );
higher_position := higher_position - 1;
END LOOP;
RETURN total_var;
END;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI grim_toaster,
It is not a synonym but the same fucntion with a different name.I am sorry I used a copy of the function so that is the reason for the confusion.
I am not an expert in Oracle programming but the issue is the same code works fine in 8.0.5 and that was what I was showing below that the same procedure works fine in the other version (8..0.5) , even in ver 8.1.7 it works fine for values under 999.
I am not claiming that the script is perfect but some one like you could spot an easy fix to overcome the issue.
Thanks
It is not a synonym but the same fucntion with a different name.I am sorry I used a copy of the function so that is the reason for the confusion.
I am not an expert in Oracle programming but the issue is the same code works fine in 8.0.5 and that was what I was showing below that the same procedure works fine in the other version (8..0.5) , even in ver 8.1.7 it works fine for values under 999.
I am not claiming that the script is perfect but some one like you could spot an easy fix to overcome the issue.
Thanks
ASKER
Hi gt,
i tried but it did not work. can you take a look? I reproduce the output. May be I made some mistake..
Regards,
Srini
SQLWKS> CREATE OR REPLACE FUNCTION newmsr (temp_var IN VARCHAR2 )
2> RETURN VARCHAR2
3> IS input_data VARCHAR2(30);
4> alpha_var CHAR(30);
5> total_var VARCHAR2(2000);
6> chk_number1 NUMBER(1);
7> chk_number2 NUMBER(1);
8> chk_number3 NUMBER(1);
9> last_two NUMBER(2);
10> start_position NUMBER;
11> array_index NUMBER;
12> higher_position NUMBER;
13> disp_position NUMBER;
14> use_number NUMBER;
15> TYPE data_text IS TABLE OF CHAR(40)
16> INDEX BY binary_integer;
17> one_thru_nine data_text;
18> ten_thru_ninety data_text;
19> higher_group data_text;
20> disp_var data_text;
21> BEGIN
22> one_thru_nine (001) := ' ONE';
23> one_thru_nine (002) := ' TWO';
24> one_thru_nine (003) := ' THREE';
25> one_thru_nine (004) := ' FOUR';
26> one_thru_nine (005) := ' FIVE';
27> one_thru_nine (006) := ' SIX';
28> one_thru_nine (007) := ' SEVEN';
29> one_thru_nine (008) := ' EIGHT';
30> one_thru_nine (009) := ' NINE';
31> one_thru_nine (010) := ' TEN';
32> one_thru_nine (011) := ' ELEVEN';
33> one_thru_nine (012) := ' TWELVE';
34> one_thru_nine (013) := ' THIRTEEN';
35> one_thru_nine (014) := ' FOURTEEN';
36> one_thru_nine (015) := ' FIFTEEN';
37> one_thru_nine (016) := ' SIXTEEN';
38> one_thru_nine (017) := ' SEVENTEEN';
39> one_thru_nine (018) := ' EIGHTEEN';
40> one_thru_nine (019) := ' NINETEEN';
41> ten_thru_ninety (001) := ' TEN';
42> ten_thru_ninety (002) := ' TWENTY';
43> ten_thru_ninety (003) := ' THIRTY';
44> ten_thru_ninety (004) := ' FORTY';
45> ten_thru_ninety (005) := ' FIFTY';
46> ten_thru_ninety (006) := ' SIXTY';
47> ten_thru_ninety (007) := ' SEVENTY';
48> ten_thru_ninety (008) := ' EIGHTY';
49> ten_thru_ninety (009) := ' NINETY';
50> higher_group (001) := ' HUNDRED';
51> higher_group (002) := ' THOUSAND';
52> higher_group (003) := ' MILLION';
53> higher_group (004) := ' BILLION';
54> higher_group (005) := ' TRILLION';
55> higher_group (006) := ' QUADRILLION';
56> higher_group (007) := ' QUINTILLION';
57> higher_group (008) := ' SEXTILLION';
58> higher_group (009) := ' SEPTILLION';
59> higher_group (010) := ' OCTILLION';
60> input_data := temp_var;
61> SELECT NVL(LENGTH(temp_var),0)
62> INTO array_index
63> FROM DUAL;
64> higher_position := 1;
65> disp_position := 0;
66> start_position := 0;
67> WHILE ( array_index > start_position )
68> LOOP
69> IF ( higher_position > 1 ) THEN
70> disp_position := disp_position + 1;
71> disp_var (disp_position) := higher_group (higher_position);
72> END IF;
73> use_number := array_index - start_position ;
74> start_position := start_position + 1;
75> IF ( use_number != ' ') THEN
76> chk_number1 := SUBSTR(input_data,use_numb
77> ELSE
78> chk_number1 := 0;
79> END IF;
80> use_number := array_index - start_position ;
81> start_position := start_position + 1;
82> IF ( use_number != ' ') THEN
83> chk_number2 := SUBSTR(input_data,use_numb
84> ELSE
85> chk_number2 := 0;
86> END IF;
87> last_two := SUBSTR(input_data,use_numb
88> use_number := array_index - start_position ;
89> start_position := start_position + 1;
90> IF ( use_number != ' ') THEN
91> chk_number3 := SUBSTR(input_data,use_numb
92> ELSE
93> chk_number3 := 0;
94> END IF;
95> IF ( higher_position > 1 AND
96> chk_number1 = 0 AND
97> chk_number2 = 0 AND
98> chk_number3 = 0 ) THEN
99> disp_position := disp_position - 1;
100> disp_var (disp_position) := ' ';
101> END IF;
102> IF ( chk_number1 <> 0 AND
103> chk_number1 <> ' ' AND
104> chk_number2 <> '1' ) THEN
105> disp_position := disp_position + 1;
106> disp_var (disp_position) := one_thru_nine (chk_number1);
107> END IF;
108> IF ( chk_number2 <> 0 AND
109> chk_number2 <> ' ' ) THEN
110> IF ( chk_number1 <> '0' AND
111> chk_number1 <> ' ' AND
112> chk_number2 = '1' ) THEN
113> disp_position := disp_position + 1;
114> disp_var (disp_position) := one_thru_nine (last_two);
115> ELSE
116> disp_position := disp_position + 1;
117> disp_var (disp_position) := ten_thru_ninety (chk_number2);
118> END IF;
119> END IF;
120> IF ( chk_number3 <> 0 AND
121> chk_number3 <> ' ' ) THEN
122> disp_position := disp_position + 1;
123> disp_var (disp_position) := higher_group (1) ;
124> disp_position := disp_position + 1;
125> disp_var (disp_position) := one_thru_nine (chk_number3) ;
126> END IF;
127> higher_position := higher_position + 1 ;
128> END LOOP;
129> higher_position := disp_position ;
130> WHILE ( higher_position > 0 )
131> LOOP
132> total_var := RTRIM(total_var) || RTRIM( disp_var(higher_position) );
133> higher_position := higher_position - 1;
134> END LOOP;
135> RETURN total_var;
136> END;
137>
138>
Statement processed.
SQLWKS> select newmsr(10000) from dual
2>
NEWMSR(10000)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 75
ORA-06512: at line 1
i tried but it did not work. can you take a look? I reproduce the output. May be I made some mistake..
Regards,
Srini
SQLWKS> CREATE OR REPLACE FUNCTION newmsr (temp_var IN VARCHAR2 )
2> RETURN VARCHAR2
3> IS input_data VARCHAR2(30);
4> alpha_var CHAR(30);
5> total_var VARCHAR2(2000);
6> chk_number1 NUMBER(1);
7> chk_number2 NUMBER(1);
8> chk_number3 NUMBER(1);
9> last_two NUMBER(2);
10> start_position NUMBER;
11> array_index NUMBER;
12> higher_position NUMBER;
13> disp_position NUMBER;
14> use_number NUMBER;
15> TYPE data_text IS TABLE OF CHAR(40)
16> INDEX BY binary_integer;
17> one_thru_nine data_text;
18> ten_thru_ninety data_text;
19> higher_group data_text;
20> disp_var data_text;
21> BEGIN
22> one_thru_nine (001) := ' ONE';
23> one_thru_nine (002) := ' TWO';
24> one_thru_nine (003) := ' THREE';
25> one_thru_nine (004) := ' FOUR';
26> one_thru_nine (005) := ' FIVE';
27> one_thru_nine (006) := ' SIX';
28> one_thru_nine (007) := ' SEVEN';
29> one_thru_nine (008) := ' EIGHT';
30> one_thru_nine (009) := ' NINE';
31> one_thru_nine (010) := ' TEN';
32> one_thru_nine (011) := ' ELEVEN';
33> one_thru_nine (012) := ' TWELVE';
34> one_thru_nine (013) := ' THIRTEEN';
35> one_thru_nine (014) := ' FOURTEEN';
36> one_thru_nine (015) := ' FIFTEEN';
37> one_thru_nine (016) := ' SIXTEEN';
38> one_thru_nine (017) := ' SEVENTEEN';
39> one_thru_nine (018) := ' EIGHTEEN';
40> one_thru_nine (019) := ' NINETEEN';
41> ten_thru_ninety (001) := ' TEN';
42> ten_thru_ninety (002) := ' TWENTY';
43> ten_thru_ninety (003) := ' THIRTY';
44> ten_thru_ninety (004) := ' FORTY';
45> ten_thru_ninety (005) := ' FIFTY';
46> ten_thru_ninety (006) := ' SIXTY';
47> ten_thru_ninety (007) := ' SEVENTY';
48> ten_thru_ninety (008) := ' EIGHTY';
49> ten_thru_ninety (009) := ' NINETY';
50> higher_group (001) := ' HUNDRED';
51> higher_group (002) := ' THOUSAND';
52> higher_group (003) := ' MILLION';
53> higher_group (004) := ' BILLION';
54> higher_group (005) := ' TRILLION';
55> higher_group (006) := ' QUADRILLION';
56> higher_group (007) := ' QUINTILLION';
57> higher_group (008) := ' SEXTILLION';
58> higher_group (009) := ' SEPTILLION';
59> higher_group (010) := ' OCTILLION';
60> input_data := temp_var;
61> SELECT NVL(LENGTH(temp_var),0)
62> INTO array_index
63> FROM DUAL;
64> higher_position := 1;
65> disp_position := 0;
66> start_position := 0;
67> WHILE ( array_index > start_position )
68> LOOP
69> IF ( higher_position > 1 ) THEN
70> disp_position := disp_position + 1;
71> disp_var (disp_position) := higher_group (higher_position);
72> END IF;
73> use_number := array_index - start_position ;
74> start_position := start_position + 1;
75> IF ( use_number != ' ') THEN
76> chk_number1 := SUBSTR(input_data,use_numb
77> ELSE
78> chk_number1 := 0;
79> END IF;
80> use_number := array_index - start_position ;
81> start_position := start_position + 1;
82> IF ( use_number != ' ') THEN
83> chk_number2 := SUBSTR(input_data,use_numb
84> ELSE
85> chk_number2 := 0;
86> END IF;
87> last_two := SUBSTR(input_data,use_numb
88> use_number := array_index - start_position ;
89> start_position := start_position + 1;
90> IF ( use_number != ' ') THEN
91> chk_number3 := SUBSTR(input_data,use_numb
92> ELSE
93> chk_number3 := 0;
94> END IF;
95> IF ( higher_position > 1 AND
96> chk_number1 = 0 AND
97> chk_number2 = 0 AND
98> chk_number3 = 0 ) THEN
99> disp_position := disp_position - 1;
100> disp_var (disp_position) := ' ';
101> END IF;
102> IF ( chk_number1 <> 0 AND
103> chk_number1 <> ' ' AND
104> chk_number2 <> '1' ) THEN
105> disp_position := disp_position + 1;
106> disp_var (disp_position) := one_thru_nine (chk_number1);
107> END IF;
108> IF ( chk_number2 <> 0 AND
109> chk_number2 <> ' ' ) THEN
110> IF ( chk_number1 <> '0' AND
111> chk_number1 <> ' ' AND
112> chk_number2 = '1' ) THEN
113> disp_position := disp_position + 1;
114> disp_var (disp_position) := one_thru_nine (last_two);
115> ELSE
116> disp_position := disp_position + 1;
117> disp_var (disp_position) := ten_thru_ninety (chk_number2);
118> END IF;
119> END IF;
120> IF ( chk_number3 <> 0 AND
121> chk_number3 <> ' ' ) THEN
122> disp_position := disp_position + 1;
123> disp_var (disp_position) := higher_group (1) ;
124> disp_position := disp_position + 1;
125> disp_var (disp_position) := one_thru_nine (chk_number3) ;
126> END IF;
127> higher_position := higher_position + 1 ;
128> END LOOP;
129> higher_position := disp_position ;
130> WHILE ( higher_position > 0 )
131> LOOP
132> total_var := RTRIM(total_var) || RTRIM( disp_var(higher_position) );
133> higher_position := higher_position - 1;
134> END LOOP;
135> RETURN total_var;
136> END;
137>
138>
Statement processed.
SQLWKS> select newmsr(10000) from dual
2>
NEWMSR(10000)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 75
ORA-06512: at line 1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Fix line 4 and 15 :
4> alpha_var VARCHAR2(30);
15> TYPE data_text IS TABLE OF VARCHAR2(40)
Bad idea to mix string types...
4> alpha_var VARCHAR2(30);
15> TYPE data_text IS TABLE OF VARCHAR2(40)
Bad idea to mix string types...
ASKER
Hi Pal,
It is not much of an excuse, but it is not my code..
I tried your correction but the error changes it fails for all the numbers. Pl take alook.
Th results:
SQLWKS> select newmsr(10000) from dual
2>
NEWMSR(10000)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1
SQLWKS> select newmsr(1000) from dual
2>
NEWMSR(1000)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1
SQLWKS> select newmsr(999) from dual
2>
NEWMSR(999)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1
SQLWKS> select newmsr(1) from dual
2>
NEWMSR(1)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1
The code like I compiled.
Pl see below:
CREATE OR REPLACE FUNCTION newmsr (temp_var IN VARCHAR2 )
RETURN VARCHAR2
IS input_data VARCHAR2(30);
alpha_var VARCHAR2(30);
total_var VARCHAR2(2000);
chk_number1 NUMBER(1);
chk_number2 NUMBER(1);
chk_number3 NUMBER(1);
last_two NUMBER(2);
start_position NUMBER;
array_index NUMBER;
higher_position NUMBER;
disp_position NUMBER;
use_number NUMBER;
TYPE data_text IS TABLE OF VARCHAR2(40)
INDEX BY binary_integer;
one_thru_nine data_text;
ten_thru_ninety data_text;
higher_group data_text;
disp_var data_text;
BEGIN
one_thru_nine (001) := ' ONE';
one_thru_nine (002) := ' TWO';
one_thru_nine (003) := ' THREE';
one_thru_nine (004) := ' FOUR';
one_thru_nine (005) := ' FIVE';
one_thru_nine (006) := ' SIX';
one_thru_nine (007) := ' SEVEN';
one_thru_nine (008) := ' EIGHT';
one_thru_nine (009) := ' NINE';
one_thru_nine (010) := ' TEN';
one_thru_nine (011) := ' ELEVEN';
one_thru_nine (012) := ' TWELVE';
one_thru_nine (013) := ' THIRTEEN';
one_thru_nine (014) := ' FOURTEEN';
one_thru_nine (015) := ' FIFTEEN';
one_thru_nine (016) := ' SIXTEEN';
one_thru_nine (017) := ' SEVENTEEN';
one_thru_nine (018) := ' EIGHTEEN';
one_thru_nine (019) := ' NINETEEN';
ten_thru_ninety (001) := ' TEN';
ten_thru_ninety (002) := ' TWENTY';
ten_thru_ninety (003) := ' THIRTY';
ten_thru_ninety (004) := ' FORTY';
ten_thru_ninety (005) := ' FIFTY';
ten_thru_ninety (006) := ' SIXTY';
ten_thru_ninety (007) := ' SEVENTY';
ten_thru_ninety (008) := ' EIGHTY';
ten_thru_ninety (009) := ' NINETY';
higher_group (001) := ' HUNDRED';
higher_group (002) := ' THOUSAND';
higher_group (003) := ' MILLION';
higher_group (004) := ' BILLION';
higher_group (005) := ' TRILLION';
higher_group (006) := ' QUADRILLION';
higher_group (007) := ' QUINTILLION';
higher_group (008) := ' SEXTILLION';
higher_group (009) := ' SEPTILLION';
higher_group (010) := ' OCTILLION';
input_data := temp_var;
SELECT NVL(LENGTH(temp_var),0)
INTO array_index
FROM DUAL;
higher_position := 1;
disp_position := 0;
start_position := 0;
WHILE ( array_index > start_position )
LOOP
IF ( higher_position > 1 ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (higher_position);
END IF;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number1 := SUBSTR(input_data,use_numb
ELSE
chk_number1 := 0;
END IF;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number2 := SUBSTR(input_data,use_numb
ELSE
chk_number2 := 0;
END IF;
last_two := SUBSTR(input_data,use_numb
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number3 := SUBSTR(input_data,use_numb
ELSE
chk_number3 := 0;
END IF;
IF ( higher_position > 1 AND
chk_number1 = 0 AND
chk_number2 = 0 AND
chk_number3 = 0 ) THEN
disp_position := disp_position - 1;
disp_var (disp_position) := ' ';
END IF;
IF ( chk_number1 <> 0 AND
chk_number1 <> ' ' AND
chk_number2 <> '1' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number1);
END IF;
IF ( chk_number2 <> 0 AND
chk_number2 <> ' ' ) THEN
IF ( chk_number1 <> '0' AND
chk_number1 <> ' ' AND
chk_number2 = '1' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (last_two);
ELSE
disp_position := disp_position + 1;
disp_var (disp_position) := ten_thru_ninety (chk_number2);
END IF;
END IF;
IF ( chk_number3 <> 0 AND
chk_number3 <> ' ' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (1) ;
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number3) ;
END IF;
higher_position := higher_position + 1 ;
END LOOP;
higher_position := disp_position ;
WHILE ( higher_position > 0 )
LOOP
total_var := RTRIM(total_var) || RTRIM( disp_var(higher_position) );
higher_position := higher_position - 1;
END LOOP;
RETURN total_var;
END;
Statement processed.
It is not much of an excuse, but it is not my code..
I tried your correction but the error changes it fails for all the numbers. Pl take alook.
Th results:
SQLWKS> select newmsr(10000) from dual
2>
NEWMSR(10000)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1
SQLWKS> select newmsr(1000) from dual
2>
NEWMSR(1000)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1
SQLWKS> select newmsr(999) from dual
2>
NEWMSR(999)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1
SQLWKS> select newmsr(1) from dual
2>
NEWMSR(1)
--------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "NSCTEST.NEWMSR", line 76
ORA-06512: at line 1
The code like I compiled.
Pl see below:
CREATE OR REPLACE FUNCTION newmsr (temp_var IN VARCHAR2 )
RETURN VARCHAR2
IS input_data VARCHAR2(30);
alpha_var VARCHAR2(30);
total_var VARCHAR2(2000);
chk_number1 NUMBER(1);
chk_number2 NUMBER(1);
chk_number3 NUMBER(1);
last_two NUMBER(2);
start_position NUMBER;
array_index NUMBER;
higher_position NUMBER;
disp_position NUMBER;
use_number NUMBER;
TYPE data_text IS TABLE OF VARCHAR2(40)
INDEX BY binary_integer;
one_thru_nine data_text;
ten_thru_ninety data_text;
higher_group data_text;
disp_var data_text;
BEGIN
one_thru_nine (001) := ' ONE';
one_thru_nine (002) := ' TWO';
one_thru_nine (003) := ' THREE';
one_thru_nine (004) := ' FOUR';
one_thru_nine (005) := ' FIVE';
one_thru_nine (006) := ' SIX';
one_thru_nine (007) := ' SEVEN';
one_thru_nine (008) := ' EIGHT';
one_thru_nine (009) := ' NINE';
one_thru_nine (010) := ' TEN';
one_thru_nine (011) := ' ELEVEN';
one_thru_nine (012) := ' TWELVE';
one_thru_nine (013) := ' THIRTEEN';
one_thru_nine (014) := ' FOURTEEN';
one_thru_nine (015) := ' FIFTEEN';
one_thru_nine (016) := ' SIXTEEN';
one_thru_nine (017) := ' SEVENTEEN';
one_thru_nine (018) := ' EIGHTEEN';
one_thru_nine (019) := ' NINETEEN';
ten_thru_ninety (001) := ' TEN';
ten_thru_ninety (002) := ' TWENTY';
ten_thru_ninety (003) := ' THIRTY';
ten_thru_ninety (004) := ' FORTY';
ten_thru_ninety (005) := ' FIFTY';
ten_thru_ninety (006) := ' SIXTY';
ten_thru_ninety (007) := ' SEVENTY';
ten_thru_ninety (008) := ' EIGHTY';
ten_thru_ninety (009) := ' NINETY';
higher_group (001) := ' HUNDRED';
higher_group (002) := ' THOUSAND';
higher_group (003) := ' MILLION';
higher_group (004) := ' BILLION';
higher_group (005) := ' TRILLION';
higher_group (006) := ' QUADRILLION';
higher_group (007) := ' QUINTILLION';
higher_group (008) := ' SEXTILLION';
higher_group (009) := ' SEPTILLION';
higher_group (010) := ' OCTILLION';
input_data := temp_var;
SELECT NVL(LENGTH(temp_var),0)
INTO array_index
FROM DUAL;
higher_position := 1;
disp_position := 0;
start_position := 0;
WHILE ( array_index > start_position )
LOOP
IF ( higher_position > 1 ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (higher_position);
END IF;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number1 := SUBSTR(input_data,use_numb
ELSE
chk_number1 := 0;
END IF;
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number2 := SUBSTR(input_data,use_numb
ELSE
chk_number2 := 0;
END IF;
last_two := SUBSTR(input_data,use_numb
use_number := array_index - start_position ;
start_position := start_position + 1;
IF ( use_number != ' ') THEN
chk_number3 := SUBSTR(input_data,use_numb
ELSE
chk_number3 := 0;
END IF;
IF ( higher_position > 1 AND
chk_number1 = 0 AND
chk_number2 = 0 AND
chk_number3 = 0 ) THEN
disp_position := disp_position - 1;
disp_var (disp_position) := ' ';
END IF;
IF ( chk_number1 <> 0 AND
chk_number1 <> ' ' AND
chk_number2 <> '1' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number1);
END IF;
IF ( chk_number2 <> 0 AND
chk_number2 <> ' ' ) THEN
IF ( chk_number1 <> '0' AND
chk_number1 <> ' ' AND
chk_number2 = '1' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (last_two);
ELSE
disp_position := disp_position + 1;
disp_var (disp_position) := ten_thru_ninety (chk_number2);
END IF;
END IF;
IF ( chk_number3 <> 0 AND
chk_number3 <> ' ' ) THEN
disp_position := disp_position + 1;
disp_var (disp_position) := higher_group (1) ;
disp_position := disp_position + 1;
disp_var (disp_position) := one_thru_nine (chk_number3) ;
END IF;
higher_position := higher_position + 1 ;
END LOOP;
higher_position := disp_position ;
WHILE ( higher_position > 0 )
LOOP
total_var := RTRIM(total_var) || RTRIM( disp_var(higher_position) );
higher_position := higher_position - 1;
END LOOP;
RETURN total_var;
END;
Statement processed.
Copy / paste my version. It works.
You used an old version (use_number != ' ' instead of use_number<>0).
You used an old version (use_number != ' ' instead of use_number<>0).
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You may note that there is a limitation with it though :
SQL> select TO_CHAR(TO_DATE(9999999, 'J'), 'JSP') FROM DUAL;
select TO_CHAR(TO_DATE(9999999, 'J'), 'JSP') FROM DUAL
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
SQL> select TO_CHAR(TO_DATE(5373484, 'J'), 'JSP') FROM DUAL;
TO_CHAR(TO_DATE(5373484,'J
--------------------------
FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR
SQL> select TO_CHAR(TO_DATE(9999999, 'J'), 'JSP') FROM DUAL;
select TO_CHAR(TO_DATE(9999999, 'J'), 'JSP') FROM DUAL
*
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484
SQL> select TO_CHAR(TO_DATE(5373484, 'J'), 'JSP') FROM DUAL;
TO_CHAR(TO_DATE(5373484,'J
--------------------------
FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
A post from Tom Kyte has this http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1407603857650
Spell method will not work for the non-existant days of
the non-existant year 0, that is for the numbers between 1721058 and 1721423
select to_char(to_date( '1721423' , 'J'),'JSP') from dual;
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
To spell numbers between 0 and 86399, you can use seconds of the day :
SQL> select to_char(to_date(0, 'SSSSS'),'Ssssssp') from dual;
TO_C
----
Zero
Spell method will not work for the non-existant days of
the non-existant year 0, that is for the numbers between 1721058 and 1721423
select to_char(to_date( '1721423' , 'J'),'JSP') from dual;
*
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
To spell numbers between 0 and 86399, you can use seconds of the day :
SQL> select to_char(to_date(0, 'SSSSS'),'Ssssssp') from dual;
TO_C
----
Zero
The problem is that the input parameter was changed to a VARCHAR2, however, you do not use that in your code, you use input_data. Simple solution for you (alteration to the code posted in your other question):
CREATE OR REPLACE FUNCTION nsc_num_to_words (
temp_var IN VARCHAR2 )
RETURN VARCHAR2
IS
input_data VARCHAR2(30); -- This used to be a char...