Generating Documentation out of your PL/SQL packages

One of the things, often overlooked and missed in projects is good documentation. Benefits of having such are obvious, but many times developers simply skip it, because of the lack of time or managing multiple projects at a time. That’s why I think that documenting should go along with the development and be a continuous process, not a one-off task. In this blog post I am going to demonstrate several ways of auto-generated documentation out of your PL/SQL packages, which can be easily implemented and maintained. And most importantly – make the process a lot friendlier and not time consuming.

Different methods

• JavaDoc + PL/SQL to Markdown Documenter
• SQL Developer (DB Doc)
• Dataedo
• Custom built PL/SQL parser

JavaDoc + PL/SQL to Markdown Documenter

PL/SQL to Markdown Documenter is an open source tool, created by Martin Giffy D’Souza. It reads your PL/SQL packages, finds all the places where JavaDoc is used and generates a Markdown document out of it. The tools offers a way to define your own templates, so you can produce a really custom layout that fits your needs. For more information, visit the GitHub repository.

Read more about JavaDoc and how it is used in this Wikipedia article.

The following setup is done on macOS. Windows and Linux users will have slightly different syntax, but in general the steps are the same.

Prerequisites

Steps

• Clone the plsql-md-doc from OraOpenSource’s GitHub repository. My local repositories are kept in folder Git-repos. Change that to a folder of your choise.

$ cd Git-repos/
$ git clone https://github.com/OraOpenSource/plsql-md-doc

• Install the plsql-md-doc package (you need to be inside the folder first – the previous step has created a new folder called plsql-md-doc, inside of Git-repos folder)

$ cd plsql-md-doc/
$ npm install
$ npm audit fix

• Change the config file config.json. You can find it on the root level of the plsql-md-doc folder. In this config, you can define the different folders with PL/SQL packages, where you want documentation generated from. You will also define a folder, where the generated documentation should go.

{
    "application_1": {
        "folders": {
            "output": {
                "delete": false,
                "path": "/Users/myuser/Git-repos/application_1/docs"
            },
            "source": {
                "path": "/Users/myuser/Git-repos/application_1/packages",
                "fileFilterRegexp": "\\.pk(s)$"
            },
            "template": "/Users/myuser/Git-repos/plsql-md-doc/templates/package.md"
        },
        "toc": {
            "fileName": "index.md",
            "template": "/Users/myuser/Git-repos/plsql-md-doc/templates/toc.md"
        }
    },
    "my_other_app": {
        "folders": {
            "output": {
                "delete": false,
                "path": "/Users/myuser/Git-repos/my_other_app/docs"
            },
            "source": {
                "path": "/Users/myuser/Git-repos/my_other_app/packages",
                "fileFilterRegexp": "\\.pk(b|s)$"
            },
            "template": "/Users/myuser/Git-repos/plsql-md-doc/templates/package.md"
        },
        "toc": {
            "fileName": "index.md",
            "template": "/Users/myuser/Git-repos/plsql-md-doc/templates/toc.md"
        }
    }
}

The main parts in the JSON document are:

• Add comments to your packages, following the JavaDoc specifications. You can more details and examples on the following places:
  
  https://en.wikipedia.org/wiki/Javadoc
  https://www.oracle.com/java/technologies/javase/javadoc-tool.html
  
  Here is a sample PL/SQL Package specification with JavaDoc comments added:

/**
 * PL/SQL package, documented using JavaDoc.
 * All comments regarding the package would go here.
 *
 * @author Plamen Mushkov
 * @created 14.12.2021
 */
create or replace package sample_pkg as

  /**
  * Get a record with one or all users.
  * Returns a record of type t_user_list.
  *
  * @example
  * declare
  *  l_returnvalue sample_pkg.t_user_list;
  * begin
  *   l_returnvalue := get_user_list ( in_id => 1 );    
  * end;  
  *
  * @param in_id An Id of a specific user. If null, all users will be returned.
  * @return t_user_list Record of type t_user_list.
  *
  * @author Plamen Mushkov
  */
  function get_user_list (
   in_id                in varchar2 default null ) return t_user_list;

  /**
  * Get a table with one or all OKTA users for this account. To be used with table() operator.
  *
  * @example
  * select * from table(sample_pkg.get_user_tab()) t order by 1;
  *
  * @param in_id An Id of a specific user. If null, all users will be returned.
  * @return t_user_tab
  *
  * @author Plamen Mushkov
  */ 
  function get_user_tab (
   in_id                in varchar2 default null ) return t_user_tab pipelined;

end sample_pkg;

• Execute the following command to get your Documentation created

$ cd plsql-md-doc/
$ node app application_1
$ node app my_other_app

The generated document will be created in the folder specified in config.json file – in this case Users/myuser/Git-repos/application_1/docs and Users/myuser/Git-repos/my_other_app/docs, and will have the same name as the packages, ending with .md file extension.

To change the way your generated document will look like and which tags should be added to the final Document, change the ../plsql-md-doc/templates/package.md template

SQL Developer (DB Doc)

SQL Developer provides a built-in feature to generate a Documentation of your database objects. And this includes not only PL/SQL packages, but also tables, views, triggers, etc. This feature is called DB Doc and can be found by right clicking on your Schema name in the Schema Browser and selecting Generate DB Doc… option.

DB Doc allows to generate an HTML document for your schema, as you can select the Object types, which should be included. You can use the JavaDoc syntax in your packages and DB Doc will read that too. To read more about DB Doc, check this article from Jeff Smith:

https://www.thatjeffsmith.com/archive/2012/03/javadoc-for-the-database-a-la-dbdoc-via-sql-developer/

Download SQL Developer from the Oracle web site:
https://www.oracle.com/database/technologies/appdev/sqldeveloper-landing.html

Dataedo

Dataedo is a third party tool, that has many features integrated, not only Documentation creation. Unlike the previous two, Dataedo is a PAID product. However, it has a slick interface, easy integration and works with most of the modern databases, including Oracle.

To check all of its features, check Dataedo’s website:
https://dataedo.com/product/database-documentation-tool

Other tools? Share them with me!

If you use another tool to document your code, feel free to share it in the comments.