internal static ITranslaterType GetTranslater(ITyped dataType)
{
string dataTypeFull = String.Empty;
if (OracleTypeTranslaters.Any(t => t.IsValid(dataType)))
{
dataTypeFull = OracleTypeTranslaters.First(t => t.IsValid(dataType)).DataTypeFull;
}
else
{
dataTypeFull = dataType.OrclType.BuildDataTypeFullName(dataType);
switch (OrclUtil.NormalizeDataType(dataType)) // dynamically create custom type translaters for complex types
{
case Orcl.ASSOCIATITVE_ARRAY:
OracleTypeTranslaters.Add(new TranslaterAssociativeArray(dataTypeFull, TypeTargetForOracleAssociativeArray, dataType));
break;
case Orcl.REF_CURSOR:
if (dataType.SubType == null)
{
OracleTypeTranslaters.Add(new TranslaterRefCursorUntyped(dataTypeFull, TypeTargetForOracleRefCursor, dataType));
}
else
{
OracleTypeTranslaters.Add(new TranslaterRefCursorTyped(dataTypeFull, TypeTargetForOracleRefCursor, dataType));
}
break;
case Orcl.OBJECT:
OracleTypeTranslaters.Add(new TranslaterObjectType(dataTypeFull));
break;
case Orcl.RECORD:
OracleTypeTranslaters.Add(new TranslaterRecordType(dataTypeFull));
break;
case Orcl.ROWTYPE:
OracleTypeTranslaters.Add(new TranslaterRowtype(dataTypeFull));
break;
case Orcl.NESTED_TABLE:
OracleTypeTranslaters.Add(new TranslaterNestedTable(dataTypeFull, TypeTargetForOracleAssociativeArray, dataType));
break;
case Orcl.VARRAY:
OracleTypeTranslaters.Add(new TranslaterVarray(dataTypeFull, TypeTargetForOracleAssociativeArray, dataType));
break;
default:
OracleTypeTranslaters.Add(new TranslaterNoneType(dataTypeFull));
break;
}
}
return(OracleTypeTranslaters.First(t => t.DataTypeFull.Equals(dataTypeFull)));
}
internal static void Normalize(ITyped type)
{
type.PreNormalizedValues = String.Format("DataType: {0}, DataPrecison: {1}, DataScale: {2}, CharLength: {3}",
type.DataType, type.DataPrecision.HasValue ? type.DataPrecision.ToString() : "null", type.DataScale.HasValue ? type.DataScale.ToString() : "null", type.CharLength.HasValue ? type.CharLength.ToString() : "null");
var dataTypeNormalized = NormalizeDataType(type);
type.OrclType = OrclUtil.GetType(dataTypeNormalized);
type.OrclType.NormalizePrecisionScale(type, out int?precision, out int?scale);
type.DataPrecision = precision;
type.DataScale = scale;
type.CharLength = type.OrclType.NormalizeCharLength(type);
}
private bool HasArgumentOfRefCursorOfRowtype()
{
foreach (IArgument arg in Arguments)
{
if (Arguments.IndexOf(arg) == Arguments.Count - 1)
{
return(false); // reached end of arg list since ref cursor uses "2 args"
}
// check type of argument and its subsequent argument
if (arg.DataType == Orcl.REF_CURSOR && OrclUtil.NormalizeDataType(arg.NextArgument) == Orcl.ROWTYPE)
{
return(true);
}
}
return(false);
}
private static string NormalizeDataTypeFromTypeCode(string dataType, ITyped type)
{
if (!(type is Argument) && !OrclUtil.IsExistsType(dataType))
{
switch (type.Typecode) // Typecode only used for table, view, object attributes (not argument)
{
case Orcl.TYPECODE_NESTED_TABLE:
dataType = Orcl.NESTED_TABLE;
break;
case Orcl.TYPECODE_OBJECT:
dataType = Orcl.OBJECT;
break;
case Orcl.TYPECODE_ANYDATA:
dataType = Orcl.ANYDATA;
break;
case Orcl.TYPECODE_ANYDATASET:
dataType = Orcl.ANYDATASET;
break;
case Orcl.TYPECODE_ANYTYPE:
dataType = Orcl.ANYTYPE;
break;
//case Orcl.TYPECODE_LCR_DDL_RECORD:
//case Orcl.TYPECODE_LCR_PROCEDURE_RECORD:
//case Orcl.TYPECODE_LCR_ROW_RECORD:
case Orcl.TYPECODE_XMLTYPE:
default:
dataType = Orcl.XMLTYPE;
break;
//default:
// break;
}
}
return(dataType);
}
/// <summary>
/// Load all attributes for both tables, views or object types into memory
/// </summary>
/// <param name="connection"></param>
private void LoadNonPackagedEntityAttributes <T_EntityAttribute>(OracleConnection connection, ref List <IEntityAttribute> attributes)
where T_EntityAttribute : class, IEntityAttribute, new()
{
string sql = typeof(T_EntityAttribute).Equals(typeof(ObjectTypeAttribute))
? " SELECT a.type_name, "
+ " a.attr_name, "
+ " CAST(a.attr_no as NUMBER(9,0)) attr_no, "
//+ " a.attr_type_name , "
// attribute type can be NULL when type is actually XMLTYPE; need better way to handle this
+ $" (CASE WHEN a.attr_type_name IS NULL THEN '{Orcl.XMLTYPE}' ELSE a.attr_type_name END) attr_type_name, "
+ " a.attr_type_owner , "
+ " a.attr_type_mod , "
+ " CAST(a.length as NUMBER(9,0)) length, "
+ " CAST(a.precision as NUMBER(9,0)) precision, "
+ " CAST(a.scale as NUMBER(9,0)) scale, "
+ " t.typecode "
+ " FROM sys.all_type_attrs a, sys.all_types t "
+ " WHERE a.inherited = 'NO' "
+ " AND UPPER(a.owner) = :owner "
+ " AND UPPER(a.type_name) LIKE :objectNamePrefix || '%'"
//+ " AND a.attr_type_name = t.type_name(+) "
// attribute type can be NULL when type is actually XMLTYPE; need better way to handle this
+ $" AND (CASE WHEN a.attr_type_name IS NULL THEN '{Orcl.XMLTYPE}' ELSE a.attr_type_name END) = t.type_name(+) "
+ " AND a.owner = t.owner(+) "
+ " ORDER BY a.type_name, a.attr_no "
: " SELECT c.table_name "
+ ", c.column_name "
+ ", CAST(c.column_id as NUMBER(9,0)) column_id "
+ ", c.data_type "
+ ", c.data_type_owner "
+ ", c.data_type_mod "
+ ", CAST(c.data_length as NUMBER(9,0)) data_length "
+ ", CAST(c.data_precision as NUMBER(9,0)) data_precision "
+ ", CAST(c.data_scale as NUMBER(9,0)) data_scale "
+ ", c.nullable "
+ ", CAST(c.char_length as NUMBER(9,0)) char_length "
+ ", t.typecode "
+ " FROM all_tab_columns c, all_types t "
+ " WHERE UPPER(c.owner) = :owner "
+ " AND UPPER(c.table_name) LIKE :objectNamePrefix || '%'"
+ " AND c.data_type = t.type_name(+) "
+ " AND c.owner = t.owner(+) "
+ " ORDER BY c.table_name, c.column_id ";
string attribType = typeof(T_EntityAttribute).Name.ToLower();
DisplayMessage("Reading "
+ (typeof(T_EntityAttribute).Equals(typeof(ObjectTypeAttribute)) ? "" : "table or view ")
+ attribType + "s...");
attributes = connection.Query <T_EntityAttribute>(sql,
new { owner = Schema, objectNamePrefix = Filter.ToUpper() }).ToList <IEntityAttribute>();
foreach (IEntityAttribute attrib in attributes)
{
OrclUtil.Normalize(attrib);
}
DisplayMessage(attributes.Count.ToString() + " "
+ (typeof(T_EntityAttribute).Equals(typeof(ObjectTypeAttribute)) ? "" : "table or view ")
+ attribType + "s read.");
}
/// <summary>
/// Load all proc arguments for given schema and filter
/// </summary>
/// <param name="connection"></param>
private void LoadArguments <T_Argument>(OracleConnection connection)
where T_Argument : class, IArgument, new()
{
DisplayMessage("Reading package arguments...");
bool isFiltering = !String.IsNullOrWhiteSpace(Filter);
string sql = " SELECT CAST(a.position as NUMBER(9,0)) position, a.overload, "
+ " CAST(a.data_level as NUMBER(9,0)) data_level, a.argument_name, "
+ " CAST(a.sequence as NUMBER(9,0)) sequence, a.data_type, a.in_out, CAST(a.data_length as NUMBER(9,0)) data_length, "
+ " CAST(a.data_precision as NUMBER(9,0)) data_precision, CAST(a.char_length as NUMBER(9,0)) char_length, "
+ " CAST(a.data_scale as NUMBER(9,0)) data_scale, "
+ " a.type_owner, a.type_name, a.type_subname, a.pls_type, "
+ " a.object_name, a.package_name, a.defaulted, a.owner, a.type_link, "
+ " o.owner owner_object "
+ " FROM sys.all_arguments a, sys.all_objects o "
+ " WHERE a.owner = :owner "
// This join logic is necessary but it can cause the query to never return. We cannot require that an Oracle instance
// be configured or tuned in order for the system views to perform. Instead, we need to enforce this condition
// in C# (see below **).
// + " AND a.owner = o.owner " !!
+ " AND a.package_name = o.object_name "
+ " AND UPPER(o.object_type) = :objectType " // required to restrict to package spec only
+ (isFiltering ? " AND UPPER(a.package_name) LIKE :packageNamePrefix || '%' " : String.Empty)
+ " ORDER BY a.package_name, a.object_name, a.overload, a.defaulted, a.sequence "; // moves all defaulted (defaulted="Y") past required (defaulted="N")
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("owner", Schema);
dynamicParameters.Add("objectType", SytemTableObjectType.PACKAGE.ToString());
if (isFiltering)
{
dynamicParameters.Add("packageNamePrefix", Filter.ToUpper());
}
List <IArgument> args = connection.Query <T_Argument>(sql, dynamicParameters)
.Where(a => a.Owner == a.OwnerObject) // ** prevents inclusion of identically named package argument from another schema
.ToList <IArgument>();
if (IsExcludeObjectsNamesWithSpecificChars)
{
args = args.FindAll(a => a.PackageName.IndexOfAny(ObjectNameCharsToExclude) == -1);
}
DisplayMessage(args.Count.ToString() + " arguments read.");
// set next argument
if (args.Count > 0)
{
IArgument nextArg = null;
for (int i = args.Count - 1; i >= 0; i--)
{
if (i == args.Count - 1)
{
nextArg = args[i];
continue;
}
if ((args[i].PackageName ?? "") == (nextArg.PackageName ?? "") &&
args[i].ProcedureName == nextArg.ProcedureName)
{
args[i].NextArgument = nextArg;
}
nextArg = args[i];
}
}
foreach (IArgument arg in args)
{
OrclUtil.Normalize(arg);
}
ArgumentsPackaged = args;
}
