public static OracleDataTypeInfoList GetDataTypeInfo(string connectionString, string sqlStatement) { OracleDataTypeInfoList dataTypeList = null; if (!String.IsNullOrEmpty(connectionString) && !String.IsNullOrEmpty(sqlStatement)) { try { using (OracleConnection conn = new OracleConnection(connectionString)) { conn.Open(); OracleCommand cmd = new OracleCommand(sqlStatement, conn); using (OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo)) { // Read the SchemaTable for the datareader DataTable dt = reader.GetSchemaTable(); // Create the list dataTypeList = new OracleDataTypeInfoList(); for (int i = 0; i < reader.FieldCount; i++) { OracleDataTypeInfo dti = new OracleDataTypeInfo(); int ordinal; // ProviderType ordinal = dt.Columns["ProviderType"].Ordinal; if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value) { dti.ProviderType = null; } else { dti.ProviderType = ((OracleDbType)dt.Rows[i].ItemArray[ordinal]); } // Datatype dti.DataType = ConvertProviderType(dti.ProviderType); // ColumnName ordinal = dt.Columns["ColumnName"].Ordinal; if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value) { dti.ColumnName = String.Empty; } else { dti.ColumnName = (string)dt.Rows[i].ItemArray[ordinal]; } // ColumnSize ordinal = dt.Columns["ColumnSize"].Ordinal; if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value) { dti.ColumnSize = null; } else { dti.ColumnSize = (int)dt.Rows[i].ItemArray[ordinal]; } // IsByteSemantic ordinal = dt.Columns["IsByteSemantic"].Ordinal; if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value) { dti.IsByteSematic = null; } else { dti.IsByteSematic = (bool)dt.Rows[i].ItemArray[ordinal]; } // NumericPrecision ordinal = dt.Columns["NumericPrecision"].Ordinal; if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value) { dti.Precision = null; } else { dti.Precision = (short)dt.Rows[i].ItemArray[ordinal]; } // NumericScale ordinal = dt.Columns["NumericScale"].Ordinal; if (dt.Rows[i].ItemArray[ordinal] == DBNull.Value) { dti.Scale = null; } else { dti.Scale = (short)dt.Rows[i].ItemArray[ordinal]; } // Add to list dataTypeList.Add(dti); } } conn.Close(); } } catch (OracleException e) { if (e.Number == 1008) { throw new Exception(string.Format("OracleError when trying to find datatypes.\n" + "If SQL looks ok then check all parenthesis and see if you can\n" + "remove any to get the same result.\n\n" + "This error has occured with a UNION that was working in Toad but failed here:\n" + "\t( select ... ) union ( select ... ) order by ...\n\n" + "It worked when the parenthesises was removed:\n" + "\tselect ... union select ... order by ...\n" + "\nOriginal Error:\n" + "\t{0}", e.Message), e); } else { throw; } } catch (Exception) { throw; } } return(dataTypeList); }
private void FindDataTypes(OracleQuery query, string oracleConnectionString, bool isSpecialWhereParameters) { if (!String.IsNullOrEmpty(oracleConnectionString)) { // If special where parameters then there could be parameters on the format :XXXX.YYYY // which Oracle doesn't like. So we fix these before doing the call to get // the datatypeinfo. string sql = TrimComments(query.SQL); if (isSpecialWhereParameters) { sql = Regex.Replace(sql, @":(([A-Za-z]\w*)\.?){1,2}(?!\.)", ":param"); } OracleDataTypeInfoList odti = OracleSchemaInfo.GetDataTypeInfo(oracleConnectionString, sql); if (odti != null) { for (int i = 0; i < odti.Count; i++) { OracleQueryParameter qp = query.GetPropertyByTypeAndSequence(OracleQueryParameterDirection.Out, i + 1); if (qp != null) { qp.DbDatatype = odti[i].DataType; if (qp.DbDatatype == "NUMBER") { qp.Precision = odti[i].Precision; qp.Scale = odti[i].Scale; if (qp.Scale.HasValue) { if (qp.Precision.HasValue) { // Scale cannot have a higher value than the precision. if (qp.Scale > qp.Precision) { qp.Scale = qp.Precision; } // And scale (decimals) should maximum contain 9 numbers as highest if (qp.Scale > 9) { qp.Scale = 9; } } else { qp.Scale = null; } } } else { qp.Precision = null; qp.Scale = null; } if (qp.DbDatatype == "VARCHAR2" || qp.DbDatatype == "CHAR") { qp.Length = odti[i].ColumnSize; } else { qp.Length = null; } } } } } }