Data Dictionary Report

Overview

Understanding the data on which you are required to report is crucial. In an ideal situation, a full, complete, and up-to-date data dictionary will be supplied to you, along with access to the DBA and systems analysts who possess an in-depth knowledge of the structures and their purposes. The real world is rarely ideal.

This chapter presents a complex report example. You will create a data dictionary report for the Crystal Reports sample database that has been moved into Oracle. Because the structure of Oracle data dictionary views is the same no matter what user objects have been created, these instructions can be applied to any Oracle database you choose and will work equally well. Your report will look like the report in the screen shots to follow except that the names of the database objects in the report will differ, and certain object types may or may not exist in your database.

If you complete the steps in this chapter, you will have a good understanding of the Oracle data dictionary views; you will also have a report that you can use to document the objects in any Oracle schema.

Oracle Data Dictionary Views

As mentioned in Chapter 2, the Oracle data dictionary views contain information about the objects that exist in the database. In this chapter, we will look at several of those views in more detail as you use them in our data dictionary report. The ALL_ views will be used in the report because they display all of the objects for which the logged in user has privileges. The DBA_ views are available only to the DBA or a user with the SELECT_CATALOG_ROLE. The USER_ views display only the objects in the logged in user’s schema. Your assumption will be that your report users want information on all objects that are available to them.

Report Strategy

The overall structure of the report must be decided before work can begin. This is always a cyclic process and usually requires several iterations to determine the best design that balances easy manipulation in Crystal and query complexity in Oracle. For other than very simple reports, it is recommended that you work out your data requirements using a query tool before starting work in Crystal. Starting a complex report in Crystal without working through the data needs first can lead to over- reliance on Crystal formulas and subreports and can add report complexity. Once you have determined what the query should be, if it can be easily duplicated in Crystal, you can re-create it there. If it cannot be duplicated in Crystal, you will already have created the SQL that can be pasted into a SQL Command or used in a view or stored procedure.

A data dictionary report is particularly difficult to design because you are required to report on many different object types, each having its own characteristics, and many having a hierarchical relationship to other objects. An extensive use of master/ detail linked subreports would be the most intuitive approach, but subreports can be inefficient. Using Oracle queries with embedded cursors to display the child data would be efficient but impossible to use in Crystal. You need a query that will return all of the required fields in a form that is easy to format in Crystal.

Before making any design decisions, you must determine the report requirements. Because the report requirement is to document the XTREME sample schema, some database objects types will not be included; for example, there are no triggers or security policies in the XTREME schema. Only a few simple object types exist in the XTREME schema so, although your report will cover them, it would not necessarily be sufficient for more complex object types. The basic objects, such as tables, views, indexes, stored procedures, and table constraints, will be included along with their related column definitions. Most objects of interest to report writers will be included.

There are many ways to organize a report such as this; what follows is the strategy used for this example. For easy use in Crystal, the overall goal will be to get a list of all objects along with their associated grouping values. Then you will join to that list the related fields for each different object type. This will denormalize the data so that each object has one master row of data, but that row will contain all fields of interest for each object type. Therefore, for any given object, the grouping fields will be populated and the fields that belong to that object’s type will be populated, but many of the fields that apply only to other object types will be null. You will format the Crystal Report so that fields belonging to different object types are grouped in sections and suppress unnecessary sections. In addition, some object types have lower-level detail such as columns or arguments. That will be the lowest level required and hence the level at which the query will be returned. All higher-level grouping will be done in Crystal.

To start, which objects in the dictionary should be displayed? The object types that will be included in this report are shown in the Group 4 column of Table 10-1, which includes tables or views (including materialized views), constraints, indexes, synonyms, types, sequences, functions, procedures, and database links. You might notice that procedure is listed three times: once for standalone procedures, once for procedures attached to object types (which are often called methods), and once for procedures that belong to packages.

Table 10-1: Dictionary Grouping

 

Group 4 is the grouping by object type, Group 5 is the grouping by the name of the database object, and the detail column contains the columns, attributes, or arguments associated with the object. Above Group 4, Group 3 exists so that related objects can be displayed together in the report. For example, a given table will have its constraints and indexes grouped with its basic table information, procedures belonging to object types will be grouped with the type they belong to, and procedures defined in packages will be grouped with the package they belong to. Group 2 is a grouping at the level of the object type of the parent, so that all tables will be grouped together, all synonyms will be grouped together, and so on. The highest level group will be the schema or object owner.

Creating the Query

The dictionary query will be developed in several parts over the following sections. Note that the chapter text contains code for Oracle 9i. The logic for 8i is identical, and the corresponding code for the end result is provided with the Chapter 10 files.

Obtaining All Objects

As mentioned previously, the first task is to create the core list of objects with their grouping values. Unfortunately, there is not a single Oracle data dictionary view containing all of the objects that you need, so you will have to build the list from several views. Start with ALL_OBJECTS, which contains package names but does not contain the package procedures, and then join ALL_OBJECTS to ALL_PROCEDURES to include the packaged procedures.

  Note

The ALL_PROCEDURES view does not exist in 8i, so the packaged procedure list is obtained from ALL_ARGUMENTS instead.

SELECT xo.owner, xo.object_type parent_type, xo.object_name parent_name, NVL2(xp.procedure_name, 'PROCEDURE', DECODE(xo.object_type,'TYPE','ATTRIBUTE',xo.object_type)) object_type, NVL(xp.procedure_name, xo.object_name) object_name, xo.status, DECODE(NVL2(xp.procedure_name,'PROCEDURE',xo.object_type), 'TYPE','ATTRIBUTE','FUNCTION','ARGUMENT', 'PROCEDURE','ARGUMENT', 'CONSTRAINT','CONSCOL', 'COLUMN') detail_type FROM all_objects xo LEFT JOIN all_procedures xp ON (xo.owner=xp.owner AND xo.object_name=xp.object_name) WHERE xo.object_type IN ('TABLE', 'VIEW', 'SYNONYM', 'TYPE', 'SEQUENCE', 'FUNCTION', 'PROCEDURE', 'PACKAGE') AND xo.generated='N'

Note that you include only the object types from ALL_OBJECTS that you are interested in, and only those objects that are not system generated. Indexes will be obtained from the ALL_INDEXES view where the parent object is available. Materialized views are listed twice in ALL_OBJECTS as both materialized views and as tables, so you should include them with the regular tables. There is no filtering done by schema; the report will have a schema parameter so the filter will be added to the query at runtime. The detail_type field has been added to facilitate joining to the detail level data later.

Now you will add unions to your query to add the other object types. Note that after this step, the query can no longer be replicated by Crystal Reports’ internal query generation capabilities and must be used in a SQL Command, view, or stored procedure.

SELECT xo.owner, xo.object_type parent_type, xo.object_name parent_name, NVL2(xp.procedure_name, 'PROCEDURE', DECODE(xo.object_type,'TYPE','ATTRIBUTE',xo.object_type)) object_type, NVL(xp.procedure_name, xo.object_name) object_name, xo.status, DECODE(NVL2(xp.procedure_name,'PROCEDURE',xo.object_type), 'TYPE','ATTRIBUTE','FUNCTION','ARGUMENT', 'PROCEDURE','ARGUMENT', 'CONSTRAINT','CONSCOL', 'COLUMN') detail_type FROM all_objects xo LEFT JOIN all_procedures xp ON (xo.owner=xp.owner AND xo.object_name=xp.object_name) WHERE xo.object_type IN ('TABLE', 'VIEW', 'SYNONYM', 'TYPE', 'SEQUENCE', 'FUNCTION', 'PROCEDURE', 'PACKAGE') AND xo.generated='N' UNION ALL SELECT xc.owner, xo.object_type, xc.table_name, 'CONSTRAINT', xc.constraint_name, xc.status, 'CONSCOL' FROM all_constraints xc LEFT JOIN all_objects xo ON (xc.owner=xo.owner AND xc.table_name=xo.object_name) WHERE xc.generated='USER NAME' UNION ALL SELECT owner, table_type, table_name, 'INDEX', index_name, status, 'INDEXCOL' FROM all_indexes WHERE generated='N' UNION ALL SELECT owner, 'DB_LINK', CAST(DB_LINK AS VARCHAR2(30)), 'DB_LINK', CAST(DB_LINK AS VARCHAR2(30)), NULL, 'NONE' FROM all_db_links

  Note

ALL_CONSTRAINTS must be joined to ALL_OBJECTS to obtain the object that the constraint belongs to.

Object Level Fields

Now you have your complete list of objects and their grouping information. Next, you will turn this list into an inline view and add master level details for each object type. The result will be a very wide record with many columns, where certain columns apply only to certain object types. For tables, views, and materialized views, information will be added from ALL_ALL_TABLES and ALL_TAB_COMMENTS. For materialized views, information will also be added from ALL_MVIEWS. For external tables, information will be added from ALL_EXTERNAL_LOCATIONS and ALL_EXTERNAL_TABLES. For types, information will be added from ALL_TYPES and ALL_COLL_TYPES. For sequences, synonyms, indexes, procedures, constraints, and database links, information will be added from the corresponding ALL_ views. As fields are added, they will be prefixed with a character to distinguish which object type they belong to. This will be useful when developing the report so that related fields can be grouped together in separate sections.

Note that in the following code listing, the inline view subquery is not shown in its entirety but represented by “INLINE VIEW.” The entire query is available in Chapter 10Data_Dict_Master.sql.

SELECT o.owner schema, o.parent_type, o.parent_name o.object_type, o.object_name, o.status, DECODE(a.temporary,'Y','Temporary',NULL) t_temporary, SUBSTR(c.comments,1,1000) c_comments, a.num_rows t_rowcount, a.last_analyzed t_last_analyzed, a.partitioned t_partitioned, a.table_type t_table_of_type, m.updatable m_updatable, m.rewrite_enabled m_rewrite_enabled, m.rewrite_capability m_rewrite_capability, m.refresh_mode m_refresh_mode, m.refresh_method m_refresh_method, m.fast_refreshable m_fast_refreshable, m.last_refresh_type m_last_refresh_type, m.last_refresh_date m_last_refresh_date, m.staleness m_staleness, n.location x_location, n.directory_name x_directory_name, x.type_name x_type_name, x.default_directory_name x_default_directory_name, x.reject_limit x_reject_limit, x.access_type x_access_type, x.access_parameters x_access_parameters, t.typecode y_typecode, l.coll_type l_coll_type, l.upper_bound l_upper_bound, l.elem_type_owner l_elem_type_owner, l.elem_type_name l_elem_type_name, s.table_owner s_table_owner, s.table_name s_table_name, s.db_link s_db_link, q.min_value q_min_value, q.max_value q_max_value, q.increment_by q_increment_by, q.cycle_flag q_cycle_flag, q.order_flag q_order_flag, q.cache_size q_cache_size, q.last_number q_last_number, p.aggregate p_aggregate, p.parallel p_parallel, p.authid p_authid, i.index_type i_index_type, i.uniqueness i_uniqueness, i.compression i_compression, r.constraint_type r_constraint_type, r.search_condition r_search_condition, r.r_owner r_r_owner, r.r_constraint_name r_r_constraint_name, r.delete_rule r_delete_rule, r.validated r_validated, b.username b_username, b.host b_host, b.created b_created, d.column_id, d.column_name, d.data_type, d.data_length, d.data_precision, d.data_scale, d.nullable, d.detail_type, userenv('SESSIONID') audsid FROM (INLINE VIEW) o LEFT JOIN all_all_tables a ON (o.owner=a.owner AND o.object_name=a.table_name) LEFT JOIN all_tab_comments c ON (o.owner=c.owner AND o.object_name=c.table_name) LEFT JOIN all_mviews m ON (o.owner=m.owner AND o.object_name=m.mview_name) LEFT JOIN all_external_locations n ON (o.owner=n.owner AND o.object_name=n.table_name) LEFT JOIN all_external_tables x ON (o.owner=x.owner AND o.object_name=x.table_name) LEFT JOIN all_types t ON (o.owner=t.owner AND o.object_name=t.type_name) LEFT JOIN all_coll_types l ON (o.owner=l.owner AND o.object_name=l.type_name) LEFT JOIN all_synonyms s ON (o.owner=s.owner AND o.object_name=s.synonym_name) LEFT JOIN all_sequences q ON (o.owner=q.sequence_owner AND o.object_name=q.sequence_name) LEFT JOIN all_procedures p ON (o.owner=p.owner AND o.parent_object_name=p.object_name AND o.object_name=NVL(p.procedure_name,p.object_name)) LEFT JOIN all_indexes i ON (o.owner=i.owner AND o.object_name=i.index_name) LEFT JOIN all_constraints r ON (o.owner=r.owner AND o.object_name=r.constraint_name) LEFT JOIN all_db_links b ON (o.owner=b.owner AND o.object_name=b.db_link);

Not all fields from every data dictionary view are included. You can add any fields that you desire to the query.

Detail Level

For the detail data, the display fields needed for columns (tables, views, indexes, constraints) are similar to the fields needed for attributes (types) and arguments (procedures), so you will create a subquery that unions all of the different detail types together. One significant difference is in the nullable field, which is filled with the nullable flag for table and view columns, IN/OUT information for arguments, and the attribute type owner for attributes and is left null for indexes and constraints. You will need to modify the report field headings appropriately for each detail type.

SELECT owner, table_name parent_name, table_name object_name, 'COLUMN' detail_type, column_id, SUBSTR(column_name,1,32) column_name, data_type, data_length, data_precision, data_scale, nullable FROM all_tab_columns UNION ALL SELECT owner, NVL(package_name, object_name), object_name, 'ARGUMENT', data_level*100+position, DECODE(position,0,object_name,SUBSTR(argument_name,1,32)), data_type, data_length, data_precision, data_scale, in_out FROM all_arguments UNION ALL SELECT owner, type_name, type_name, 'ATTRIBUTE', attr_no, SUBSTR(attr_name,1,32), attr_type_name, length, precision, scale, attr_type_owner FROM all_type_attrs UNION ALL SELECT index_owner, table_name, index_name, 'INDEXCOL', column_position, SUBSTR(column_name,1,32), NULL,NULL,NULL,NULL, descend FROM all_ind_columns UNION ALL SELECT owner, table_name, constraint_name, 'CONSCOL', position, SUBSTR(column_name,1,32), NULL, NULL, NULL, NULL, NULL FROM all_cons_columns

The argument number is computed from the data_level and position. The zero data_level in the ALL_ARGUMENTS view indicates the actual arguments, so you could have simply filtered out any rows with the data_level not equal to zero and used the position for the column identifier. However, the argument information for the lower levels contains the REF Cursor and the PL/SQL record composition. These define the fields in the REF Cursor for stored procedures and, therefore, the computation will allow you not only to display all of the data, but also to distinguish between the true arguments and the component parts. This computation assumes that there are fewer than 100 arguments.

Final Query

The next step is to join the detail subquery to the main query. There is one complication to overcome concerning this join, however. It is possible and even common for an index to have the same name as the constraint it is implementing. In this situation, this means, for example, the primary key constraint on the employee table has the same owner (XTREME), parent name (EMPLOYEE), and object name (EMPLOYEE_PK) as the associated index. Your solution to this problem will be to add the detail_type column to both the master query and the detail query.

In the following code listing the object inline view is abbreviated as “OBJECT INLINE VIEW,” and the detail inline view is abbreviated as “DETAIL INLINE VIEW.” Part of the object level select list, along with the object level joins, are also abbreviated. The entire query is available as Chapter 10Data_Dictionary.sql.

SELECT o.owner schema, o.parent_type, o.parent_name, o.object_type, o.object_name, o.status, …Other Object Level Columns… d.column_id, d.column_name, d.data_type, d.data_length, d.data_precision, d.data_scale, d.nullable, d.detail_type, userenv('SESSIONID') audsid FROM (OBJECT INLINE VIEW) o …Left Joins for Object Level… LEFT JOIN (DETAIL INLINE VIEW) d ON (o.owner=d.owner AND o.parent_name=d.parent_name AND o.object_name=d.object_name AND o.detail_type=d.detail_type);

For ease of use, the query will be created as a view. The entire view creation statement can be found in Chapter 10Data Dictionary view.sql.

For Oracle 8i, the creation statement is contained in Chapter 10Data Dictionary view 8i.sql. The 8i version uses the older join syntax and lacks references to external tables, as they were not available until 9i. Oracle 8i does not contain an ALL_PROCEDURES data dictionary view, so the three fields from ALL_PROCEDURES are omitted. In addition, the packaged procedure names are drawn from ALL_ARGUMENTS instead of ALL_PROCEDURES.

Creating the Report

Creating the report involves several steps that will be outlined in the following sections. The significant actions are covered, but extensive formatting, field placement, and so on should be referenced from the report itself. Everything discussed here using 9i is directly applicable to 8i, except for the lack of the external table fields and the procedure fields.

Preliminary Setup

The Oracle data dictionary is contained in system views that have been given public synonyms. In order to see the data dictionary views from Crystal Reports, the database options need to be modified to show synonyms, as shown in Figure 10-1. Start Crystal Reports, then choose File | Options, and then the Database tab. Make sure Synonyms is checked (the System Tables option need not be checked) and close the dialog box. The database options can also be modified from the Database Expert by right-clicking a database and choosing Options.

Figure 10-1: Database options

Checking the Show Synonyms option is required if you want to report directly from the system views. In this case, you will be reporting from a view defined in the XTREME schema so this step can be omitted if desired.

Create the Report

Open Crystal Reports and create a new report using the Standard Report Creation Wizard. Choose the XTREME view DATA_DICTIONARY as the only data source. Choose the detail fields shown in Figure 10-2 as the fields to display.

Figure 10-2: Fields to display

Group on SCHEMA, PARENT_TYPE, PARENT_NAME, OBJECT_TYPE, and OBJECT_NAME, as shown in Figure 10-3, then choose Finish.

Figure 10-3: Group by fields

On the summaries page remove all summaries, as shown in Figure 10-4.

Figure 10-4: Summaries

On the Record Selection page, choose SCHEMA as a filter field and set it equal to XTREME, as shown in Figure 10-5. You will add a parameter for picking a schema later. For now, it is best to create this selection formula to avoid returning all data dictionary objects, which would take quite some time.

Figure 10-5: Record selection

Choose Finish, and the report will be displayed.

Page Setup

Set the page margins as shown here:

Add the Parameter

Add a parameter field called Schema and modify the record selection formula as shown:

{DATA_DICTIONARY.SCHEMA} = {?Schema}

When previewing the report set Schema equal to XTREME.

Create Formulas

Create the following formulas using the code shown:

Modify the Group Options

Modify the group options for Group 3. Check Repeat Group Header On Each Page. For Group 4, change the sort order to descending. For Group 5, choose Keep Group Together.

Populate and Format the Sections

The SCHEMA group name field will be used in the page header so it will appear at the top of each page. The PARENT_TYPE group name field will also be put in the page header so that it will include the schema and the main object type. For the object types that do not have parents, such as database links, functions, procedures, sequences, and synonyms, the group information for the PARENT_NAME and OBJECT_TYPE groups will be suppressed. The OBJECT_TYPE group can be suppressed because it is identical to the PARENT_TYPE. The sections in the OBJECT_NAME group will be used to display all fields for the various object types, excluding the column level details. Fields for various object types will be grouped together in sections that can be suppressed when the current record is for a different object type. You will be putting what the report considers detail level fields into group headers to effectively suppress the duplication that was created by joining the object master level information to the detail inline view.

Split the page header into two sections: PHa and PHb. Split the header section for Group 5 until you have sections GH5a through GH5u.

Move the detail field headers from the page header into GH5u and then format the group sections as follows:

The report is now complete. The 9i version is included with the download files as Chapter 10Data Dictionary.rpt. The 8i version is Chapter 10Data Dictionary 8i.rpt. Figure 10-6 shows the SUPPLIER_RPTS.SUPPLIER_REPORT as it is displayed in the report.

Figure 10-6: SUPPLIER_RPTS.SUPPLIER_REPORT

You now have a report that you can use to document any schema in your database. You can add to the query any other data dictionary information that you require and modify the report accordingly keeping to the general structures outlined in this chapter. You should find the report performance to be quite speedy even though a large amount of data is returned.

The next chapter will discuss creating and using a Crystal repository in Oracle. In addition, security will be added to the repository to make its use feasible in a multiuser environment.

Related

Категории