Oracle Client Error OCI-22053 Overflow Error

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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.
 
Thanks Wen,

That did solve the problem. I also found another article suggesting
ROUND(val1/val2, 2) which worked as well.
 
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.
 
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.
 
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
Click to expand...
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.
Click to expand...
Click to expand...
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:
Click to expand...
Click to expand...
Click to expand...
 
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
Click to expand...
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.
Click to expand...
Click to expand...
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:
Click to expand...
Click to expand...
//www.itjungles.com/other/fixed-oci-22053-overflow-error[/URL]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top