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
$ 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:

output
paththe folder where the generated documents should go
source
paththe folder, containing your PL/SQL files
fileFilterRegexpregular expression, specifying the file types that should be included in documentation generation
templatethe file, containing the Markdown template, based on which your Documentation will be generated
/**
 * 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s