Oracle Client Error OCI-22053 Overflow Error

G

Guest

I have a query against a database that continues to crash my app with an
Oracle CLient Error OCI-22053 Overflow Error.

The query is:

SELECT pt.adjusted_delivered_qty, pt.expected,
(CASE
WHEN pt.val2> 0
THEN pt.val1 / pt.val2
ELSE 1
END
) AS rate
FROM myTable pt

I can execute the query in Toad For Oracle and get the results as expected.
The results from Toad are listed below.

Why am I getting this error? There should be no overflow.

Thanks,
--
Dale Preston
MCAD C#
MCSE, MCDBA


Results of query in Toad are:

Val1 Val2 Rate
6 25 0.24
137 2800 0.048928571
113475 116000 0.978232759
64 61 1.049180328
1 210 0.004761905
10 10 1
40 40 1
81 82 0.987804878
1 35 0.028571429
77 420 0.183333333
0 1 0
0 5 0
0 22 0
0 22 0
0 1 0
0 1 0
0 28 0
0 28 0
2 5 0.4
80 80 1
22 200 0.11
118 110 1.072727273
122 110 1.109090909
32 190 0.168421053
2 30 0.066666667
26 320 0.08125
2 2 1
10 10 1
2770 2700 1.025925926
6 4 1.5
6 6 1
3 3 1
2 2 1
3285 3400 0.966176471
61 58 1.051724138
88 74 1.189189189
6 6 1
2 350 0.005714286
1 35 0.028571429
24 420 0.057142857
0 1 0
0 5 0
0 22 0
0 22 0
0 1 0
0 1 0
0 28 0
0 28 0
2 5 0.4
82 80 1.025
11 10 1.1
11 10 1.1
1 2 0.5
5 110 0.045454545
2 120 0.016666667
6 6 1
2 2 1
117 110 1.063636364
124 120 1.033333333
6 6 1
46 46 1
128 100 1.28
2 2 1
117 110 1.063636364
141 120 1.175
6 6 1
117 110 1.063636364
117 110 1.063636364
191 175 1.091428571
4 500 0.008
3 5 0.6
130 100 1.3
1 110 0.009090909
2 2 1
117 110 1.063636364
136 120 1.133333333
6 6 1
29 35 0.828571429
352 420 0.838095238
30 25 1.2
122 110 1.109090909
46 46 1
128 100 1.28
1 1 1
1 1 1
2 6 0.333333333
2 2 1
518 350 1.48
522 500 1.044
191 175 1.091428571
2 5 0.4
0 5 0
46 46 1
128 100 1.28
1 1 1
2 1 2
2 2 1
117 110 1.063636364
136 120 1.133333333
6 6 1
118 110 1.072727273
30 35 0.857142857
2757 2700 1.021111111
6 4 1.5
6 6 1
425 420 1.011904762
227 200 1.135
30 25 1.2
114265 116000 0.985043103
122 110 1.109090909
198 190 1.042105263
36 30 1.2
344 320 1.075
2 2 1
0 1 0
0 5 0
0 22 0
0 22 0
2 2 1
3283 2800 1.1725
10 10 1
84 74 1.135135135
6 6 1
67 61 1.098360656
0 1 0
204 210 0.971428571
520 500 1.04
189 175 1.08
567 350 1.62
885 450 1.966666667
62 60 1.033333333
62 60 1.033333333
62 60 1.033333333
62 60 1.033333333
1 2 0.5
3 7 0.428571429
5 7 0.714285714
5 7 0.714285714
5 7 0.714285714
7 7 1
12 120 0.1
46 46 1
12 100 0.12
1 1 1
118 100 1.18
121 110 1.1
118 110 1.072727273
40 40 1
2 2 1
73 70 1.042857143
390 370 1.054054054
147 10 14.7
20 20 1
0 1 0
240 220 1.090909091
8 8 1
17 18 0.944444444
30 30 1
2173 1600 1.358125
299 161 1.857142857
148 10 14.8
83 80 1.0375
83 82 1.012195122
57 56 1.017857143
1544 200 7.72
54 50 1.08
9 8 1.125
9 8 1.125
1067 1000 1.067
8 8 1
7 6 1.166666667
7 7 1
11 10 1.1
2 2 1
2 6 0.333333333
9 9 1
3 3 1
1 1 1
2 2 1
6 6 1
1 1 1
19 19 1
12 10 1.2
10 10 1
15 15 1
1 1 1
58 62 0.935483871
135 100 1.35
646 636 1.01572327
1 1 1
0 50 0
4 2 2
0 175 0
0 4 0
0 59 0
0 2 0
0 10 0
0 326 0
0 33 0
0 2 0
0 5 0
0 10 0
0 6 0
0 6 0
0 10 0
0 5 0
0 9 0
0 9 0
0 200 0
0 3 0
0 900 0
0 24 0
0 500 0
0 10 0
0 10 0
0 1 0
0 28 0
0 28 0
3280 3400 0.964705882
61 58 1.051724138
3 3 1
1 1 1
0 1 0
0 22 0
1 1 1
6 5 1.2
3 3 1
281 204 1.37745098
404 650 0.621538462
52 100 0.52
889 1100 0.808181818
1 1 1
0 100 0
0 138 0
0 10 0
0 5 0
0 24 0
0 10 0
0 170 0
0 200 0
0 10 0
0 15 0
0 3 0
1 1 1
118 110 1.072727273
1 35 0.028571429
10 10 1
22 200 0.11
68 420 0.161904762
6 25 0.24
122 110 1.109090909
32 190 0.168421053
2 30 0.066666667
26 320 0.08125
2 2 1
2773 2700 1.027037037
6 4 1.5
6 6 1
114429 116000 0.986456897
2838 2800 1.013571429
0 1 0
0 5 0
0 22 0
0 22 0
0 1 0
0 1 0
0 28 0
0 28 0
40 40 1
3287 3400 0.966764706
61 58 1.051724138
3 3 1
2 2 1
88 74 1.189189189
6 6 1
 
G

Guest

Here is my corrected query. I didn't clean it up properly the first post but
the effect is the same: overflow error from the .Net Oracle client.

Dale

SELECT pt.val1, pt.val2,
(CASE
WHEN pt.val2> 0
THEN pt.val1 / pt.val2
ELSE 1
END
) AS rate
FROM myTable pt
 
W

WenYuan Wang [MSFT]

Hello Dale,

According to your description, you meet an issue that following select
command will throw an "Oracle Client Error OCI-22053 Overflow Error"
exception by .Net Oracle client. If I misunderstoo, please correct me.
SELECT pt.val1, pt.val2,
(CASE
WHEN pt.val2> 0
THEN pt.val1 / pt.val2
ELSE 1
END
) AS rate
FROM myTable pt

It seems the issue is related to "pt.val1/pt.val2". As far as I know,
System.Data.OracleClient namespace does only support 38 digits.

I suggest you use ORACLE SQL TRUNC(number, decimal_places) function to work
around it.
http://www.techonthenet.com/oracle/functions/trunc_nbr.php
[Oracle/PLSQL: Trunc Function (with numbers)]

SELECT pt.val1, pt.val2,
(CASE
WHEN pt.val2> 0
THEN trunc(pt.val1/pt.val2,6)
ELSE 1
END
) AS rate
FROM myTable pt

Hope this helps. Please try the above method and let me know if this works
on your side. It's my pleasure to assist you.
Have a great day.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
G

Guest

Thanks Wen,

That did solve the problem. I also found another article suggesting
ROUND(val1/val2, 2) which worked as well.
 
W

WenYuan Wang [MSFT]

Welcome, Dave.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
W

WenYuan Wang [MSFT]

Welcome, Dale.
If you have any further issue, please feel free to update here again. I'm
glad to assist you.
Have a great day,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Q

Quang Vo

rounding or truncating should fixes the issue. I find this help http://www.itjungles.com/other/fixed-oci-22053-overflow-error
I have a query against a database that continues to crash my app with an
Oracle CLient Error OCI-22053 Overflow Error.

The query is:

SELECT pt.adjusted_delivered_qty, pt.expected,
(CASE
WHEN pt.val2> 0
THEN pt.val1 / pt.val2
ELSE 1
END
) AS rate
FROM myTable pt

I can execute the query in Toad For Oracle and get the results as expected.
The results from Toad are listed below.

Why am I getting this error? There should be no overflow.

Thanks,
--
Dale Preston
MCAD C#
MCSE, MCDBA


Results of query in Toad are:

Val1 Val2 Rate
6 25 0.24
137 2800 0.048928571
113475 116000 0.978232759
64 61 1.049180328
1 210 0.004761905
10 10 1
40 40 1
81 82 0.987804878
1 35 0.028571429
77 420 0.183333333
0 1 0
0 5 0
0 22 0
0 22 0
0 1 0
0 1 0
0 28 0
0 28 0
2 5 0.4
80 80 1
22 200 0.11
118 110 1.072727273
122 110 1.109090909
32 190 0.168421053
2 30 0.066666667
26 320 0.08125
2 2 1
10 10 1
2770 2700 1.025925926
6 4 1.5
6 6 1
3 3 1
2 2 1
3285 3400 0.966176471
61 58 1.051724138
88 74 1.189189189
6 6 1
2 350 0.005714286
1 35 0.028571429
24 420 0.057142857
0 1 0
0 5 0
0 22 0
0 22 0
0 1 0
0 1 0
0 28 0
0 28 0
2 5 0.4
82 80 1.025
11 10 1.1
11 10 1.1
1 2 0.5
5 110 0.045454545
2 120 0.016666667
6 6 1
2 2 1
117 110 1.063636364
124 120 1.033333333
6 6 1
46 46 1
128 100 1.28
2 2 1
117 110 1.063636364
141 120 1.175
6 6 1
117 110 1.063636364
117 110 1.063636364
191 175 1.091428571
4 500 0.008
3 5 0.6
130 100 1.3
1 110 0.009090909
2 2 1
117 110 1.063636364
136 120 1.133333333
6 6 1
29 35 0.828571429
352 420 0.838095238
30 25 1.2
122 110 1.109090909
46 46 1
128 100 1.28
1 1 1
1 1 1
2 6 0.333333333
2 2 1
518 350 1.48
522 500 1.044
191 175 1.091428571
2 5 0.4
0 5 0
46 46 1
128 100 1.28
1 1 1
2 1 2
2 2 1
117 110 1.063636364
136 120 1.133333333
6 6 1
118 110 1.072727273
30 35 0.857142857
2757 2700 1.021111111
6 4 1.5
6 6 1
425 420 1.011904762
227 200 1.135
30 25 1.2
114265 116000 0.985043103
122 110 1.109090909
198 190 1.042105263
36 30 1.2
344 320 1.075
2 2 1
0 1 0
0 5 0
0 22 0
0 22 0
2 2 1
3283 2800 1.1725
10 10 1
84 74 1.135135135
6 6 1
67 61 1.098360656
0 1 0
204 210 0.971428571
520 500 1.04
189 175 1.08
567 350 1.62
885 450 1.966666667
62 60 1.033333333
62 60 1.033333333
62 60 1.033333333
62 60 1.033333333
1 2 0.5
3 7 0.428571429
5 7 0.714285714
5 7 0.714285714
5 7 0.714285714
7 7 1
12 120 0.1
46 46 1
12 100 0.12
1 1 1
118 100 1.18
121 110 1.1
118 110 1.072727273
40 40 1
2 2 1
73 70 1.042857143
390 370 1.054054054
147 10 14.7
20 20 1
0 1 0
240 220 1.090909091
8 8 1
17 18 0.944444444
30 30 1
2173 1600 1.358125
299 161 1.857142857
148 10 14.8
83 80 1.0375
83 82 1.012195122
57 56 1.017857143
1544 200 7.72
54 50 1.08
9 8 1.125
9 8 1.125
1067 1000 1.067
8 8 1
7 6 1.166666667
7 7 1
11 10 1.1
2 2 1
2 6 0.333333333
9 9 1
3 3 1
1 1 1
2 2 1
6 6 1
1 1 1
19 19 1
12 10 1.2
10 10 1
15 15 1
1 1 1
58 62 0.935483871
135 100 1.35
646 636 1.01572327
1 1 1
0 50 0
4 2 2
0 175 0
0 4 0
0 59 0
0 2 0
0 10 0
0 326 0
0 33 0
0 2 0
0 5 0
0 10 0
0 6 0
0 6 0
0 10 0
0 5 0
0 9 0
0 9 0
0 200 0
0 3 0
0 900 0
0 24 0
0 500 0
0 10 0
0 10 0
0 1 0
0 28 0
0 28 0
3280 3400 0.964705882
61 58 1.051724138
3 3 1
1 1 1
0 1 0
0 22 0
1 1 1
6 5 1.2
3 3 1
281 204 1.37745098
404 650 0.621538462
52 100 0.52
889 1100 0.808181818
1 1 1
0 100 0
0 138 0
0 10 0
0 5 0
0 24 0
0 10 0
0 170 0
0 200 0
0 10 0
0 15 0
0 3 0
1 1 1
118 110 1.072727273
1 35 0.028571429
10 10 1
22 200 0.11
68 420 0.161904762
6 25 0.24
122 110 1.109090909
32 190 0.168421053
2 30 0.066666667
26 320 0.08125
2 2 1
2773 2700 1.027037037
6 4 1.5
6 6 1
114429 116000 0.986456897
2838 2800 1.013571429
0 1 0
0 5 0
0 22 0
0 22 0
0 1 0
0 1 0
0 28 0
0 28 0
40 40 1
3287 3400 0.966764706
61 58 1.051724138
3 3 1
2 2 1
88 74 1.189189189
6 6 1
On Wednesday, August 29, 2007 4:32 PM dale097 wrote:
Here is my corrected query. I didn't clean it up properly the first post but
the effect is the same: overflow error from the .Net Oracle client.

Dale

SELECT pt.val1, pt.val2,
(CASE
WHEN pt.val2> 0
THEN pt.val1 / pt.val2
ELSE 1
END
) AS rate
FROM myTable pt
On Thursday, August 30, 2007 7:46 AM v-wywan wrote:
Hello Dale,

According to your description, you meet an issue that following select
command will throw an "Oracle Client Error OCI-22053 Overflow Error"
exception by .Net Oracle client. If I misunderstoo, please correct me.
SELECT pt.val1, pt.val2,
(CASE
WHEN pt.val2> 0
THEN pt.val1 / pt.val2
ELSE 1
END
) AS rate
FROM myTable pt

It seems the issue is related to "pt.val1/pt.val2". As far as I know,
System.Data.OracleClient namespace does only support 38 digits.

I suggest you use ORACLE SQL TRUNC(number, decimal_places) function to work
around it.
http://www.techonthenet.com/oracle/functions/trunc_nbr.php
[Oracle/PLSQL: Trunc Function (with numbers)]

SELECT pt.val1, pt.val2,
(CASE
WHEN pt.val2> 0
THEN trunc(pt.val1/pt.val2,6)
ELSE 1
END
) AS rate
FROM myTable pt

Hope this helps. Please try the above method and let me know if this works
on your side. It's my pleasure to assist you.
Have a great day.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
On Thursday, August 30, 2007 10:18 AM dale097 wrote:
Thanks Wen,

That did solve the problem. I also found another article suggesting
ROUND(val1/val2, 2) which worked as well.
--
Dale Preston
MCAD C#
MCSE, MCDBA


"WenYuan Wang [MSFT]" wrote:
 
Q

Quang Vo

I find that to fix this issue we just have to round or truncate value where decimal is greater then what the current dataset can suppport. here's how: http://www.itjungles.com/other/fixed-oci-22053-overflow-error
I have a query against a database that continues to crash my app with an
Oracle CLient Error OCI-22053 Overflow Error.

The query is:

SELECT pt.adjusted_delivered_qty, pt.expected,
(CASE
WHEN pt.val2> 0
THEN pt.val1 / pt.val2
ELSE 1
END
) AS rate
FROM myTable pt

I can execute the query in Toad For Oracle and get the results as expected.
The results from Toad are listed below.

Why am I getting this error? There should be no overflow.

Thanks,
--
Dale Preston
MCAD C#
MCSE, MCDBA


Results of query in Toad are:

Val1 Val2 Rate
6 25 0.24
137 2800 0.048928571
113475 116000 0.978232759
64 61 1.049180328
1 210 0.004761905
10 10 1
40 40 1
81 82 0.987804878
1 35 0.028571429
77 420 0.183333333
0 1 0
0 5 0
0 22 0
0 22 0
0 1 0
0 1 0
0 28 0
0 28 0
2 5 0.4
80 80 1
22 200 0.11
118 110 1.072727273
122 110 1.109090909
32 190 0.168421053
2 30 0.066666667
26 320 0.08125
2 2 1
10 10 1
2770 2700 1.025925926
6 4 1.5
6 6 1
3 3 1
2 2 1
3285 3400 0.966176471
61 58 1.051724138
88 74 1.189189189
6 6 1
2 350 0.005714286
1 35 0.028571429
24 420 0.057142857
0 1 0
0 5 0
0 22 0
0 22 0
0 1 0
0 1 0
0 28 0
0 28 0
2 5 0.4
82 80 1.025
11 10 1.1
11 10 1.1
1 2 0.5
5 110 0.045454545
2 120 0.016666667
6 6 1
2 2 1
117 110 1.063636364
124 120 1.033333333
6 6 1
46 46 1
128 100 1.28
2 2 1
117 110 1.063636364
141 120 1.175
6 6 1
117 110 1.063636364
117 110 1.063636364
191 175 1.091428571
4 500 0.008
3 5 0.6
130 100 1.3
1 110 0.009090909
2 2 1
117 110 1.063636364
136 120 1.133333333
6 6 1
29 35 0.828571429
352 420 0.838095238
30 25 1.2
122 110 1.109090909
46 46 1
128 100 1.28
1 1 1
1 1 1
2 6 0.333333333
2 2 1
518 350 1.48
522 500 1.044
191 175 1.091428571
2 5 0.4
0 5 0
46 46 1
128 100 1.28
1 1 1
2 1 2
2 2 1
117 110 1.063636364
136 120 1.133333333
6 6 1
118 110 1.072727273
30 35 0.857142857
2757 2700 1.021111111
6 4 1.5
6 6 1
425 420 1.011904762
227 200 1.135
30 25 1.2
114265 116000 0.985043103
122 110 1.109090909
198 190 1.042105263
36 30 1.2
344 320 1.075
2 2 1
0 1 0
0 5 0
0 22 0
0 22 0
2 2 1
3283 2800 1.1725
10 10 1
84 74 1.135135135
6 6 1
67 61 1.098360656
0 1 0
204 210 0.971428571
520 500 1.04
189 175 1.08
567 350 1.62
885 450 1.966666667
62 60 1.033333333
62 60 1.033333333
62 60 1.033333333
62 60 1.033333333
1 2 0.5
3 7 0.428571429
5 7 0.714285714
5 7 0.714285714
5 7 0.714285714
7 7 1
12 120 0.1
46 46 1
12 100 0.12
1 1 1
118 100 1.18
121 110 1.1
118 110 1.072727273
40 40 1
2 2 1
73 70 1.042857143
390 370 1.054054054
147 10 14.7
20 20 1
0 1 0
240 220 1.090909091
8 8 1
17 18 0.944444444
30 30 1
2173 1600 1.358125
299 161 1.857142857
148 10 14.8
83 80 1.0375
83 82 1.012195122
57 56 1.017857143
1544 200 7.72
54 50 1.08
9 8 1.125
9 8 1.125
1067 1000 1.067
8 8 1
7 6 1.166666667
7 7 1
11 10 1.1
2 2 1
2 6 0.333333333
9 9 1
3 3 1
1 1 1
2 2 1
6 6 1
1 1 1
19 19 1
12 10 1.2
10 10 1
15 15 1
1 1 1
58 62 0.935483871
135 100 1.35
646 636 1.01572327
1 1 1
0 50 0
4 2 2
0 175 0
0 4 0
0 59 0
0 2 0
0 10 0
0 326 0
0 33 0
0 2 0
0 5 0
0 10 0
0 6 0
0 6 0
0 10 0
0 5 0
0 9 0
0 9 0
0 200 0
0 3 0
0 900 0
0 24 0
0 500 0
0 10 0
0 10 0
0 1 0
0 28 0
0 28 0
3280 3400 0.964705882
61 58 1.051724138
3 3 1
1 1 1
0 1 0
0 22 0
1 1 1
6 5 1.2
3 3 1
281 204 1.37745098
404 650 0.621538462
52 100 0.52
889 1100 0.808181818
1 1 1
0 100 0
0 138 0
0 10 0
0 5 0
0 24 0
0 10 0
0 170 0
0 200 0
0 10 0
0 15 0
0 3 0
1 1 1
118 110 1.072727273
1 35 0.028571429
10 10 1
22 200 0.11
68 420 0.161904762
6 25 0.24
122 110 1.109090909
32 190 0.168421053
2 30 0.066666667
26 320 0.08125
2 2 1
2773 2700 1.027037037
6 4 1.5
6 6 1
114429 116000 0.986456897
2838 2800 1.013571429
0 1 0
0 5 0
0 22 0
0 22 0
0 1 0
0 1 0
0 28 0
0 28 0
40 40 1
3287 3400 0.966764706
61 58 1.051724138
3 3 1
2 2 1
88 74 1.189189189
6 6 1
On Wednesday, August 29, 2007 4:32 PM dale097 wrote:
Here is my corrected query. I didn't clean it up properly the first post but
the effect is the same: overflow error from the .Net Oracle client.

Dale

SELECT pt.val1, pt.val2,
(CASE
WHEN pt.val2> 0
THEN pt.val1 / pt.val2
ELSE 1
END
) AS rate
FROM myTable pt
On Thursday, August 30, 2007 7:46 AM v-wywan wrote:
Hello Dale,

According to your description, you meet an issue that following select
command will throw an "Oracle Client Error OCI-22053 Overflow Error"
exception by .Net Oracle client. If I misunderstoo, please correct me.
SELECT pt.val1, pt.val2,
(CASE
WHEN pt.val2> 0
THEN pt.val1 / pt.val2
ELSE 1
END
) AS rate
FROM myTable pt

It seems the issue is related to "pt.val1/pt.val2". As far as I know,
System.Data.OracleClient namespace does only support 38 digits.

I suggest you use ORACLE SQL TRUNC(number, decimal_places) function to work
around it.
http://www.techonthenet.com/oracle/functions/trunc_nbr.php
[Oracle/PLSQL: Trunc Function (with numbers)]

SELECT pt.val1, pt.val2,
(CASE
WHEN pt.val2> 0
THEN trunc(pt.val1/pt.val2,6)
ELSE 1
END
) AS rate
FROM myTable pt

Hope this helps. Please try the above method and let me know if this works
on your side. It's my pleasure to assist you.
Have a great day.
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
On Thursday, August 30, 2007 10:18 AM dale097 wrote:
Thanks Wen,

That did solve the problem. I also found another article suggesting
ROUND(val1/val2, 2) which worked as well.
--
Dale Preston
MCAD C#
MCSE, MCDBA


"WenYuan Wang [MSFT]" wrote:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top