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;
}
}
}
}
}
}
