JAVADOC for the Oracle Database a la DBDOC

Tell Others About This Story:

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

* Project:         Test Project (<a href="">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)
/** Table of customer records. */
TYPE customer_table IS TABLE OF customer_type INDEX BY BINARY_INTEGER;
* Gets customer by ID.
* @param p_id       customer ID
* @param r          record of customer data
* @throws no_data_found if no such customer exists
PROCEDURE get_customer (
  p_id              VARCHAR2,
  customer_rec      OUT customer_type);
* Searches customer by criteria.
* @param p_criteria record with assigned search criteria
* @param r_records  table of found customers <b>(may be empty!)</b>
PROCEDURE get_by_criteria (
  p_criteria        customer_type,
  r_records         OUT customer_table);
* Creates a customer record.
* @param customer_rec record of customer data
PROCEDURE create_customer (
  customer_rec      customer_type);
* Changes customer data.
* @param customer_rec record of updated customer data
PROCEDURE update_customer (
  customer_rec      customer_type);

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!’

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Similar Posts by Content Area: , , ,