Oracle Documentation Generator - The manual

Is there anything more boring than to document your own code?
It would have to be to document other developers code.

Beeing faced with the prospect of having to document an entire Oracle database (schemas, tables, data files...the lot), 
I simply had to come up with something.

I'm a big fan of generating documentation out of the comments in the code, e.g. Java doc.

At that point I got (what I thought was) a bright idea.
I'll simply create an application that generates HTML formated documentation out of an Oracle database.
The idea it self was probably nice, but I didn't realize the amount of work it would take.

Anyhow here it is the one and only free-of-charge-no-questions-asked Oracle Documentation Generator.

Click here for an example of the documentation generated by the tool.

This is a client based tool, i.e. it need not to run on the server where the database resides.
You simply download the tool to any client machine with a Java Runtime Environment (JRE) version 1.4 or higher.
The tool will connect to the server with the database using the host/ip-address, port number for the listener (default 1521)
and the service id (SID) for the database.
There is of course nothing that prevents you from installing the tool on the same machine as the database.

The tool has been developed and tested mostly on 10g servers but also to a certain extent on 9i.

Platform indepedency
Since the tool is designed in Java all platforms that have support for JRE can run the tool.
The type of server and platform the Oracle server is running on is of no matter as long as there 
is a listener running on the server that the tool can connect to.
The documentation can be generated to any local or network disk that the client can access.

Figure. Overview

How the information is fetched
When connected the tool will query a bunch of dba_* views (this is why you must connect as system or equivalent user that has the 
"SELECT ANY TABLE" privilege) to find out what users/tables/views/tablespaces/etc. exist on the database.

No altering of data
Under no circumstances will the tool ever write anything to any table in the database, it will only query information.

General Features

• Generates HTML output
• Possible to configure projects that connect to different hosts and/or instances
• Configurable output directories for each project
• Possible to autogenerate documentation for all objects in a schema with just a few clicks with the mouse
• Single sign on. Login once as system user and produce output for all the schemas (except sysdba)
• Currently the following object types are supported:
• Advanced queues
• Database triggers
• Functions
• Jobs
• Packages
• Procedures
• Scheduler jobs (10g)
• Tables
• Tablespaces
• Type declarations
• Users/schemas
• Views

In the preferences menu you can create and delete projects.
A project consists of a single database instance and a path where to produce 
the output documentation.

To create a new project choose the _new project_ option in the drop down box.
In order for the application to be able to connect to an database instance
some information is required:

Host - The hostname/IP-address of the server
SID - The service id of the instance
Port - The listener port on the server (default 1521)
User - The user name for a system user or equivalent
Password - The password for the above user
Output path - The path for where the documentation is to be generated

Both the user name and password can be omitted.
These can be specified when logging in to the instance.

As soon as the apply button has been pressed the project has been created.

In order to be able to connect to an instance a project must be created.
Once the project is in place you can connect to the project/instance by choosing the project from the settings/projects menu.

You now first choose for which schema/user you want to generate documentation for.
This is done by choosing the user/schema from the users menu.
The "lock" symbol denotes that the user is locked and/or expired.

Once the user has been selected you will be presented with some information regarding that user.
You can now choose to generate documentation for all objects in one go or individually.

From the menu you can choose to view and generate documentation for each object the schema owns, e.g.
tables, packages, views...etc

In order to simplify the generation of documentation you have the option to list all available 
object for the currently selected user/schema.
This is done by clicking on the third button from left in the toolbar. 

From the list of object you can choose for which you can generate documentation.

Once the object (table, view, etc) has been selected from the menu you can press the second button from the left in the toolbar to generate 
documentation for that specific object.

Here follows some screenshots from the various types of objects that are supported by the tool

The package section is a bit different since PL/SQL packages contain various procedures/functions 
that you may want to document.

This is where the real smart part of this tool becomes obvious.
By writing java-doc-style comments in your PL/SQL code you can not only comment the code
but this tool is capable of extracting those comments and include the in the generated documentation.

The following is an example of how to write comments:
Note the @param, @return and @see tags.


create or replace package dmonix.DM_PROC is

--Export of table data was successful
EXPORT_SUCCESSFUL CONSTANT NUMBER := 1;

--Export of table data failed
EXPORT_FAILED CONSTANT NUMBER := -2;

--Import of table data was successful
IMPORT_SUCCESSFUL CONSTANT NUMBER := 1;

--Import of table data failed
IMPORT_FAILED CONSTANT NUMBER := -1;

--Exports customer data to a binary file
--@param p_fileName The name of the dump file
--@param p_logging If the procedure should log progress to alert.log
--@return The result of the operation
--@see EXPORT_SUCCESSFUL
--@see EXPORT_FAILED
function BackupCustomerData(p_fileName varchar2 DEFAULT to_char(sysdate, 'YYYYMMDDHH24MISS')||'.dmp', 
                            p_logging boolean DEFAULT false)
                            return number;                           

--Coalesce all indexes
--@param p_logging Logging on/off
procedure CoalesceIndexes(p_logging boolean DEFAULT false);

-- Updates data in the reptest table
--@param p_NAME The customer name
--@param p_ADDRESS The address
--@param p_COUNTRY The country
--@param p_EXPIREDATE The date when the customer expires
procedure CreateCustomer(p_NAME customer.name%type,
                         p_ADDRESS customer.address%type,
                         p_COUNTRY customer.country%type,
                         p_EXPIREDATE customer.expiredate%type);

--Makes an order of an product for a customer
--@param p_CUSTOMER_ID The customer id
--@param p_PRODUCT_ID The product id
--@param p_DELIVER_DATE The delivery date
procedure MakeOrder(p_CUSTOMER_ID orders.customer_id%type,
                    p_PRODUCT_ID orders.product_id%type,
                    p_DELIVER_DATE orders.delivery_date%type);                          
                         

--Imports spatial data from a file.
--@param p_fileName The name of the dump file
--@param p_logging If the procedure should log progress to alert.log
--@return The result of the operation
--@see IMPORT_SUCCESSFUL
--@see IMPORT_FAILED
function RecoverCustomerData(p_fileName varchar2, 
                             p_logging boolean DEFAULT false)
                             return number;                           

--Finds all orders that have missed their delivery date
procedure ReportMissedDeliveries;

--Writes data from a logentry to the alert.log file  
--@p_logEntry The log entry to write
procedure WriteLogEntries(p_logEntry ku$_LogEntry);

--Writes data to the alert.log file
--@param p_logString The string to write
--@param p_logging Logging on/off
procedure WriteToAlertLog(p_logString varchar2,
                          p_logging boolean DEFAULT true);
                         
end DM_PROC;

As with most of my projects I have a load of features that would want to implement.
Unfortunately there never seems to be enough of time.

• Improved manual?
• Support for sequences
• Support for materialized views
• Support for stored Java procedures

Version 1.0

First draft

Version 1.1

Added support for database triggers
Corrected bug that displayed clusters for tables with more than one columns as different clusters

Version 1.2

Added support for tablespaces and their associated files

Version 1.3

Added support for functions and procedures
Added support for jobs
Now possible to generate documentation for all/selected tablespaces in one go
All "heavy" functionality that takes more than a few seconds to perform is now in separate threads to prevent that the main thread (frame) gets frozen
When creating the object menus a sub menu is created for each 30:th object to prevent the menus from growing larger than the screen size

1. Will the tool work for older Oracle versions?

Answers

1. Will the tool work for older Oracle versions?

   Honestly I don't know since I haven't tried it on anything else than 9i and 10g.
   Some of the dba_ views that the tool relies on might not exist on older versions of the database 
   or they might have different names. 
   The result would be that the tool might be unable to fetch the necessary information.
   This will however not disrupt the functionality of your database.
   It will merely generate an error in the tool.
   [Back to the FAQ]