 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
.NET Forum / .NET Framework / New Users / August 2007Oracle Client Error 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,  SignatureDale 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. SignatureDale Preston MCAD C# MCSE, MCDBA > Hello Dale, > [quoted text clipped - 36 lines] > ================================================== > This posting is provided "AS IS" with no warranties, and confers no rights. 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. Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.