public static RefCurStoredProcedure Create(string storedProcedureName, string packageName, string connectionString)
{
RefCurStoredProcedure storedProc = new RefCurStoredProcedure();
storedProc.Name = storedProcedureName;
storedProc.PackageName = packageName;
// Try to call database to see if we can catch the parameters
OracleSchemaInfo.GetRefCursorTypes(connectionString, storedProc);
return(storedProc);
}
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;
}
}
}
}
}
}
