RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


An Integrated-Documentation Tool for Oracle Databases : Page 3

If you have ever had to analyze the data in a large Oracle database with no documentation, this integrated-documentation tool is for you. Check out this PL/SQL-generated HTML database dictionary.

How to Set the Tool Up
The main entry point for this tool is the GenerateWebPages.cmd file located in the main directory. GenerateWebPages.cmd is basically a batch script that contains the basic set-up parameters. It connects to the Oracle database with these parameters before firing off other files to generate the HTML dictionary. Make a new copy of this file for each schema that you wish to have a dictionary. The parameters that you need to set in this file are the schema name, the user name, the password, the TNSName, and the SID (as required by the Oracle DB for connection). Make sure that the user name has sufficient security privileges to access system tables. An example setting for these variables is show in Figure 4.

Figure 4. Setup Parameters: Here is an example of connection parameters that need to be set in GenerateWebPages.cmd file.

After these parameters are set, all you have to do is double-click the script (or call it from a windows command window), and the script will automatically generate the dictionary and open it in your default browser. The HTML files created by the script are placed in a directory with the same name as the schema in the main directory of the tool.

How the Tool Works
If you are curious about what the PL/SQL is doing, take a look inside the SQL script files inside the SQL directory of the main tool directory. Basically, a batch file causes the SQL files to run one by one and spools their outputs in HTML files. The SQL files use the schema name to retrieve the information about the schema from the system tables. These system tables include ALL_TABLES (for getting the table names), ALL_TAB_COLUMNS (for getting the column names for each table), ALL_TAB_COMMENTS (for getting the comments for tables), and ALL_COL_COMMENTS (for getting the comments for the columns). Each table in the schema is also hit to create the column-specific stats.

Upcoming Feature for Version 2.0
When we saw the great productivity boost a simple tool like this could deliver, we thought to ourselves wouldn't it be great to include more analytic information to further assist data-mining projects. Certain stats and analytics are very common in data-mining projects, and it could be very beneficial to have them included in an enhanced version of the tool.

The main challenge for the next version of the tool has been deciding which stats provide the best insight into a database and how to present that data. We have been working with other colleagues to come up with a good solution and hope to deliver a solution in the near future.

Ali Afsari-Nejad is a Computer Engineering Co-op student from the University of Waterloo in Waterloo, Ontario. He has nearly two years of professional work experience. He is currently employed with the Advanced Concepts and Technology Data Mining team of Pitney Bowes.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date