Oracle Client Error OCI-22053 Overflow Error

Discussion:

(too old to reply)

Dale

2007-08-29 15:02:03 UTC

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

Dale

2007-08-29 20:32:02 UTC

Permalink

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

WenYuan Wang [MSFT]

2007-08-30 11:46:08 UTC

Permalink

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.

Dale

2007-08-30 14:18:07 UTC

Permalink

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

Post by 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.

WenYuan Wang [MSFT]

2007-08-31 04:28:43 UTC

Permalink

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.

WenYuan Wang [MSFT]

2007-08-31 04:30:02 UTC

Permalink

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.

Quang Vo

2010-12-01 05:24:04 UTC

Permalink

rounding or truncating should fixes the issue. I find this help http://www.itjungles.com/other/fixed-oci-22053-overflow-error

Post by Dale
I have a query against a database that continues to crash my app with an
Oracle CLient Error OCI-22053 Overflow Error.
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
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

Post by Dale
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

Post by Dale
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

Post by 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.

Post by 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.
Submitted via EggHeadCafe
Sterling Database for Silverlight and Windows Phone 7
http://www.eggheadcafe.com/tutorials/aspnet/f4731b1a-dadb-4b4f-b9a8-a5d9ef7a76dd/sterling-database-for-silverlight-and-windows-phone-7.aspx

Quang Vo

2010-12-01 05:32:08 UTC

Permalink

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

Post by Dale
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

Post by Quang Vo
rounding or truncating should fixes the issue. I find this help http://www.itjungles.com/other/fixed-oci-22053-overflow-error
Submitted via EggHeadCafe
Silverlight 4 Coin-Flip Decision Maker based on Mersenne Twister
http://www.eggheadcafe.com/tutorials/aspnet/fabf1bbb-9568-4ff6-859b-7679bc02f13f/silverlight-4-coinflip-decision-maker-based-on-mersenne-twister.aspx