Javadoc is a tool for generating API documentation in HTML format from doc comments in source code. Wouldn’t it be nice if a similar tool existed for PLSQL? Well you’re in luck because someone already made that available via an open-source utility known as pldoc.

Automatically document your database schema with SQL Developer using the DB Doc feature. Generate HTML pages and allow your teams to click through the PLSQL source and tables without access to development databases!

SQL Developer has extended pldoc into ‘DB Doc.’ DB Doc includes the PLSQL documentation functionality and adds the ability to also document the following objects:

  • Tables
  • Views
  • Editioning Views
  • Indexes
  • Queues
  • Sequences
  • Materialized Views
  • Synonyms
  • Database Links
  • Directories
  • Editions
  • Application Express
  • Stored Java Procedures
  • XML Schemas
  • XML DB Repository

No additional work is required to take advantage of the regular database object documentation. However, if you want to harness the full power provided by pldoc, you need to start building additional documentation into your PLSQL objects via these commenting schemes seen in this sample code.

Here’s a quick preview of how that looks when you add it to your PLSQL

CREATE OR REPLACE
PACKAGE CUSTOMER_DATA
IS
/** 
* Project:         Project (<a href="http://pldoc.sourceforge.net">PLDoc</a>)<br/>
* Description:     Customer Data Management<br/>
* DB impact:       YES<br/>
* Commit inside:   NO<br/>
* Rollback inside: NO<br/>
* @headcom
*/

/**
* Record of customer data.
*
* @param id		customer ID
* @param name		customer name
* @param regno		registration number or SSN
* @param language	preferred language
*/
TYPE customer_type IS RECORD (
  id                        VARCHAR2(20),
  name                      VARCHAR2(100),
  regno                     VARCHAR2(50),
  language                  VARCHAR2(10)
);
...

When you build the DB Doc set with SQL Developer, the PLSQL pages will be built using the information inside these /* */ blocks

PLSQL Page in DB Doc generated by SQL Developer

Running DB Doc in SQL Developer

You will need to define a connection for the schema you want to document. You can then mouse-right-click on the connection in the connection tree and select ‘Generate DB Doc…’

Don't forget to right-click!

You only have a few options. Decide which types of objects you want included. Decide if you want blank pages if there are no objects for that object type. And provide a directory to write the files to. Please note there’s a bug in the current version that prevents SQL Developer from running in an acceptable amount of time if a large amount of FLASHBACK data attached to a table. This can be avoided by either

  1. Running as a low privileged user – CONNECT is all that is required to documents a schema
  2. Removing tables from the list of objects to be documented

This bug has already been fixed and addressed for the next release.

Instead of putting in a bunch of screenshots, here’s the full HTML report to play with

Get started with a very small DB Doc HTML documentation set generated by Oracle SQL Developer v3.1.

It has a bit further to go before it’s perfect. A few bugs yes, but I’d also like to see support for generating an overview page like what you see in pldoc. I was thinking maybe a dynamic page that lists the date, connection name, and version of SQL Developer used to generate the pages. Then the end users can mod it up or replace it with whatever serves their purposes. And then maybe make this available via the command-line so you can build it into your build process.

And so we continue to ‘the next version!’