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.

About these ads

7 thoughts on “Creating Built-in Help For PL/SQL Packages

  1. The advantage is that you access the help by querying the help function of the package, which just about any client will do without the need to enable output for DBMS_OUTPUT for example.

    I don’t follow what you mean by using insert statements or a hard-coded string?

  2. I mean, why not just create an actual table and fill it with this?

    And if you’re just trying to emulate “a table without a table”, do you know need all that Pipe Row stuff? Can’t you just say help_text := whatever? Do we need to pipe rows, and presumably every time this function is called.

    I can see you’re doing something neat, I guess I just don’t see why. What problem are we trying to solve, and why do we need to pipe to solve it?

  3. There’s a few reasons why I like this.

    Firstly it is very flexible — you can do something like accept an argument to the help function to define what procedure or function within the package you want help on. The help function can then query for overloaded versions and their arguments, and provide help on each of the arguments based on a common definition for that package.

    I also like that it is relatively self-contained, apart from the object and table types (which could actually be used by other packages as well). Everything comes as part of the package.

    This method is also sympathetic to some other stuff I’m doing — for example a pipelined “report” function that will spit out a custom report on thestatus of materialized views, their statistics, when they were refreshed etc. based on parameters that describe how detailed a report is required.

  4. Hmmm, not that I know of — but this is the same method used by DBMS_XPLAN package, wherein the DISPLAY function is pipelined and returns a report that varies in format based on the parameter passed (or not).

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s