.comment-link {margin-left:.6em;}

Oracle Sponge -- Now Moved To Wordpress

Please use http://oraclesponge.wordpress.com

Friday, February 24, 2006

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.

7 Comments:

At 6:48 PM, Blogger Robert Vollman said...

Why all the "pipeline" stuff? Why not just regular insert statements? Or, how about just a hard-coded string?

What's the advantage to pipeline?

 
At 7:31 PM, Blogger David Aldridge said...

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?

 
At 4:03 PM, Blogger Robert Vollman said...

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?

 
At 4:23 PM, Blogger David Aldridge said...

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.

 
At 7:03 PM, Blogger LAKSHMINARAYANAN SESHADRI said...

Just Curious here to know:

1. Is the SQL*PLUS help designed in this manner?

Thanks in advance
Laks

 
At 7:34 PM, Blogger David Aldridge said...

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).

 
At 9:42 PM, Blogger LAKSHMINARAYANAN SESHADRI said...

Thanks for that info.

Regards
Laks

 

Post a Comment

Links to this post:

Create a Link

<< Home