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:
- Editioning Views
- Materialized Views
- Database Links
- 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: Test 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) ); /** 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); END;
When you build the DB Doc set with SQL Developer, the PLSQL pages will be built using the information inside these /* */ blocks
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…’
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
- Running as a low privileged user – CONNECT is all that is required to documents a schema
- 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!’