Using DBMS_PIPEs to debug PL/SQL

These scripts serve a dual purpose:

• show how to use Perl to create a DBMS_PIPE reading daemon,
• create a simple method for viewing output from PL/SQL in real-time; no waiting for the PL/SQL to finish

These scripts are very basic, but they do work. They are not production code. If you wish to use this technique in any production systems, you should make them more robust.

Notes on Perl:

You must have DBI and DBD for Oracle installed; this program is Oracle specific.

Make sure that you have the environment variables setup just as if you were going to run sqlplus.

The top line in 'debug_piped' will need to be edited for your site, as I doubt your perl executable is in the same location as mine.

You will also need to comment out the 'use OraEnv' package and hardcode your username and password. The areas are marked with '# CHANGE'.

The debug_piped program will require a '-database' argument, and an optional '-filename' argument. If filename is not supplied on the command line, the output will be to stdout.

These programs are NOT production ready; use at your own risk!

DEMO:

   sqlplus scott/tiger ( or whoever you are )
   @pipe_vars
   @create_pipe
   @send_pipe

From another terminal window: ( SHELL )

   debug_piped -database mydb  # start the perl daemon
   cat /tmp/piped.log

From the SQLPLUS window

   @send_pipe

From the SQLPLUS Window

   @end_pipe

Files:

   debug_pipe.sql    - run this one first ( creates package )
   create_pipe.sql   - run this to create pipe and insert
                       some messages into it
   end_pipe.sql      - this will terminate piped or read_pipe.sql
   debug_piped       - this is the Perl daemon to read the pipe
   read_pipe.sql     - PL/SQL to read the pipe
   remove_pipe.sql   - PL/SQL to remove the pipe
   send_pipe.sql     - PL/SQL to interactively send a message to pipe

Perl/DBMS_PIPEs