Creating Built-in Help For PL/SQL Packages

This is a trivial application of basic techniques, but it seemed to me to be an idea worthwhile of sharing.

Anyway, I sometimes have a need to code up some PL/SQL to support ETL operations, and such procedures are often executed from the command line in SQL*Plus by operators. Currently I'm working on a generic package to support manual refresh of some troublesome materialized views.

This is not necessarily the sort of thing that the operators will run every day, and hence it seems worthwhile to put the documentation for the procedures literally at their fingertips by providing a built-in help function. I've done this by adding to the package a definition of a pipelined function called HELP, which simply spools out descriptions of procedures and parameters, and here's how it works.


Start with an object type definition such as:

create or replace TYPE HELP_TEXT_TYPE
AS OBJECT ( help_text varchar2(80) );
/

Then add a table type definition:

create or replace TYPE HELP_TEXT_TYPE_TABLE
AS TABLE OF HELP_TEXT_TYPE;
/

In your package you'll need a pipelined function specification:

Create Package help_demo
as
   function help                     
   return help_text_type_table
   pipelined;
End;
/

Then the package body contains the function definition, such as:

Create or replace Package Body help_demo
as
   function help
   return   help_text_type_table
   pipelined
   is
   begin
      Pipe Row(help_text_type('IF THIS IS DIFFICULT TO READ THEN USE THE COMMAND ...'));
      Pipe Row(help_text_type('set heading off pagesize 1000'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('HELP for package HELP_DEMO'));
      Pipe Row(help_text_type('=========================='));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('Procedures'));
      Pipe Row(help_text_type('----------'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type(' None'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('Functions'));
      Pipe Row(help_text_type('---------'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('help:'));
      Pipe Row(help_text_type(' You''re reading it now. This is a demonstration of how'));
      Pipe Row(help_text_type(' to provide a built-in help functionality for a PL/SQL package'));
      Pipe Row(help_text_type(' that is accessible through SQL*Plus to user of the package'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('Parameters'));
      Pipe Row(help_text_type('----------'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type(' There are none of those either, but if there were then they''d be'));
      Pipe Row(help_text_type(' listed and described here, like the following examples'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type(' autocommit; (Y/N) Default N'));
      Pipe Row(help_text_type('   Whether or not to commit each process as it completes'));
      Pipe Row(help_text_type('   A commit will be issued at the end anyway'));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type('That Is All'));
      Pipe Row(help_text_type('==========='));
      Pipe Row(help_text_type(''));
      Pipe Row(help_text_type(' Get back to work'));
   End;
End;
/

When you issue the query;

Select * from table(help_demo.help)
/

... you get output such as ...

IF THIS IS DIFFICULT TO READ THEN USE THE COMMAND ...
set heading off pagesize 1000

HELP for package HELP_DEMO
==========================

Procedures
----------

 None

Functions
---------

help:
 You're reading it now. This is a demonstration of how
 to provide a built-in help functionality for a PL/SQL package
 that is accessible through SQL*Plus to user of the package

Parameters
----------

 There are none of those either, but if there were then they'd be
 listed and described here, like the following examples

 autocommit; (Y/N) Default N
   Whether or not to commit each process as it completes
   A commit will be issued at the end anyway

That Is All
===========

 Get back to work

33 rows selected.

You might choose to wrap up the help functioanlity in a view, such as:

create view help_demo
as
select * from table(help_demo.help)
/

... to make is easier to access, or even use DBMS_OUTPUT to send the help text to the screen. I like this though as it's accessible directly through SQL, the Universal Language.