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