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