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.

Partition Change Tracking Fast Refresh Workaround?

I wrote a while ago that I didn't think that Partition Change Tracking (PCT) fast refresh was worth using, because eventually something will cause the refresh to fail and then, for various silly reasons, you can only get PCT FR back by completely rebuilding your MV in one shot. This is not a practical endeavour in many cases, hence PCT is not worth so much.

However, it has now occured to me that if one kept a backup copy of the data in the PCT-based MV, even if it is only as a regular table, then in the event of disaster such a table could be brought up to dat with respect to the master tables(s) and with the correct MV definition placed over it temporarily the complete rebuild of the PCT-based MV could be rewritten to effectively be a full table scan of this backup. that ought to be more achievable I suppose.

It still seems like a lot of trouble though.