This worked example demonstrates how to generate database documentation for a schema in an Oracle database.

The example has six steps:

1. Setting up the example schema
2. Setting up the documentation project
3. Viewing the schema objects
4. Selecting the schemas and objects to document
5. Generating the documentation
6. Viewing the documentation

1. Setting up the example schema

The worked example uses the WIDGETDEV schema. To create the schema on your Oracle server:

1. Copy this SQL creation script for the schema:

   ---- WIDGET DEV ----
   DROP USER WidgetDev CASCADE;
   CREATE USER WidgetDev IDENTIFIED BY password;
   GRANT UNLIMITED TABLESPACE TO WidgetDev;
   GRANT CONNECT TO WidgetDev;
   GRANT CREATE SESSION TO WidgetDev;
   GRANT ALTER ANY TABLE TO WidgetDev;
   GRANT CREATE ANY INDEX TO WidgetDev;
   GRANT CREATE ANY PROCEDURE TO WidgetDev;
   GRANT CREATE ANY VIEW TO WidgetDev;
   /
   CREATE TABLE WidgetDev.WidgetPrices (
   	RecordID INTEGER NOT NULL ,
   	WidgetID INTEGER NULL ,
   	Price INTEGER  NULL ,
   	DateValidFrom DATE NULL ,
   	DateValidTo DATE NULL ,
   	Active CHAR(1) NULL 
   );
   
   ALTER TABLE WidgetDev.WidgetPrices MODIFY DateValidFrom DATE DEFAULT sysdate ;
   ALTER TABLE WidgetDev.WidgetPrices MODIFY Active CHAR DEFAULT ('N');
   ALTER TABLE WidgetDev.WidgetPrices ADD CONSTRAINT PK_WidgetPrices PRIMARY KEY (RecordID);
   
   
   CREATE INDEX WidgetDev.IX_WidgetPrices_1 ON WidgetDev.WidgetPrices(WidgetID);
   CREATE INDEX WidgetDev.IX_WidgetPrices_2 ON WidgetDev.WidgetPrices(DateValidFrom);
   CREATE INDEX WidgetDev.IX_WidgetPrices_3 ON WidgetDev.WidgetPrices(DateValidTo);
   
   GRANT SELECT  ON WidgetDev.WidgetPrices TO public;
   
   CREATE TABLE WidgetDev.Widgets (
   	RecordID INTEGER NOT NULL ,
   	Description VARCHAR2 (50) NULL ,
   	SKU VARCHAR2 (20) NULL 
   );
   
   CREATE TABLE WidgetDev.WidgetReferences (
   	WidgetID INTEGER NOT NULL,
   	Reference VARCHAR2(25) NULL 
   );
   
   ALTER TABLE WidgetDev.WidgetReferences ADD CONSTRAINT PK_WidgetReferences PRIMARY KEY (WidgetID);
   ALTER TABLE WidgetDev.Widgets ADD CONSTRAINT PK_Widgets PRIMARY KEY (RecordID);
   
   GRANT SELECT ON WidgetDev.Widgets TO PUBLIC;
   
   ALTER TABLE WidgetDev.WidgetPrices ADD CONSTRAINT FK_WidgetPrices_Widgets FOREIGN KEY (WidgetID) REFERENCES WidgetDev.Widgets(RecordID);
   
   CREATE OR REPLACE VIEW WidgetDev.CurrentPrices AS
   SELECT 
   WidgetID, 
   Price, 
   Description
   FROM WidgetDev.Widgets INNER JOIN
       WidgetDev.WidgetPrices ON Widgets.RecordID = WidgetPrices.WidgetID 
       WHERE WidgetPrices.Active = 'Y';
   
   GRANT SELECT ON WidgetDev.CurrentPrices TO PUBLIC;
   
   CREATE OR REPLACE PROCEDURE WidgetDev.prcActivatePrices 
   IS
   BEGIN
     UPDATE WidgetDev.WidgetPrices SET Active='N' WHERE sysdate<DateValidTo OR sysdate>DateValidFrom; 
     UPDATE WidgetDev.WidgetPrices SET Active='Y' WHERE sysdate>=DateValidFrom OR sysdate<=DateValidFrom;
   END;
   /
   CREATE OR REPLACE PACKAGE WidgetDev.WidgetPackage AS
   PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2, widgetSKU VARCHAR2);
   PROCEDURE remWidget(widgetID INTEGER);
   END WidgetPackage;
   /
   CREATE OR REPLACE PACKAGE BODY WidgetDev.WidgetPackage AS
   PROCEDURE addWidget(widgetID INTEGER, widgetDesc VARCHAR2, widgetSKU VARCHAR2) IS
     BEGIN
        INSERT INTO WidgetDev.Widgets(RecordID, Description, SKU) VALUES(widgetID, widgetDesc, widgetSKU);
   END addWidget;
   PROCEDURE remWidget(widgetID INTEGER) IS
   BEGIN
     DELETE FROM WidgetDev.Widgets
      WHERE WidgetDev.Widgets.RECORDID = remWidget.widgetID;
   END remWidget;
   END WidgetPackage;
   /

2. Paste the script into your SQL editor and run it on any database you choose.

The schema is created.

2. Setting up the documentation project

1. If you haven't started Schema Doc for Oracle, select it from your Start menu. If it's already running, click  New Project to open the Project Configuration dialog box.
2. On the Connection tab, enter connection details for the database on which you set up the WIDGETDEV schema.
3. In the User name box, type WIDGETDEV. In the Password box, type password.
   In this example, we'll set up the schema on the DEVELOPMENT database:
   
4. Under Schemas, click Refresh list, and select the schema you want to document from the list. 
   In this example, we'll document the WIDGETDEV schema:
   
5. Click OK.

3. Viewing the schema objects

To view the objects in the WIDGETDEV schema, click to expand the schema.

Click an object to see a preview of the documentation for that object.

For example, if you click the WIDGETPRICES table, the preview pane displays the table's properties, column details, and its SQL creation script:

4. Selecting the schemas and objects to document

To include objects in the documentation, select the objects using the check boxes.

In this example, we'll select all the tables and the PRCACTIVATEPRICES stored procedure.

1. Select the Tables check box. All the tables in the database are selected.
2. Under Procedures, select the check box for the PRCACTIVATEPRICES procedure:
   

5. Generating the documentation

Now that we've selected the objects we want to document, we can generate the documentation:

1. Click  Generate Documentation.
   
2. On the Generate Documentation dialog box, in the Save in box, specify the location where you want Schema Doc for Oracle to create the documentation.
3. Select an Output style. For this example, we'll generate an HTML web page.
4. Select the Open documentation on completion check box.
5. Click Generate Documentation to start generating the documentation.
6. Click OK to close the message box.
7. Click Save Project on the Schema Doc for Oracle toolbar to save the project. The project configuration, object-selection, and generation settings are stored so they are available next time you open the project.

6. Viewing the documentation

Schema Doc for Oracle opens the database documentation in your default web browser: