F6 Team

前陣子同事遇到連Oracle DB 出現【Oracle OCI-22053】的問題

去google一下,原來oracle在做數值運算時

例如1/3 會 產生 0.33333333....

回傳到DataTable時會產生 overflow 的 error

舉下列例子來說明

OracleOCI-22053.aspx.cs

using System; using System.Collections.Generic; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; //加入dll Oracle.DataAccess 10.2.0.100 //我是安裝Oracle Client 10g using Oracle.DataAccess.Client; using System.Data; public partial class OracleOCI_22053 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string strConn = "Data Source=dupont_E;User ID=dupont;password=dupont"; using (OracleConnection objConnection = new OracleConnection()) { objConnection.ConnectionString = strConn; try { objConnection.Open(); OracleCommand objCommand = new OracleCommand(); objCommand.Connection = objConnection; //這行會出現【Oracle OCI-22053】或【數學運算導致溢位】問題 objCommand.CommandText = "SELECT 1/3 FROM DUAL"; //解決方式,利用TO_CHAR 或 ROUND 來解決 //TO_CHAR,轉為文字格式 objCommand.CommandText = "SELECT TO_CHAR(1/3) FROM DUAL"; //ROUND,四拾五入到位數少一點的數值,才不會overlfow objCommand.CommandText = "SELECT ROUND(1/3,2) FROM DUAL"; OracleDataAdapter objAdapter = new OracleDataAdapter(objCommand); objAdapter.SelectCommand = objCommand; DataTable objDataTable = new DataTable(); objAdapter.Fill(objDataTable); objConnection.Close(); } catch (Exception ex) { Response.Write(ex.ToString()); } finally { objConnection.Close(); } } } }

參考網址：
http://blog.csdn.net/wangguol/archive/2005/09/29/492358.aspx