Running System Commands from PL/SQL Using Pipes

Since PL/SQL doesn't recognize the host ("!") command, which is available in SQL*Plus scripts, you couldn't use that to run system commands, such as having a trigger on a table execute a host mail command to e-mail you about a particular update made to a record in that table.  However, Oracle has a technical support bulletin, Dynamic SQL and System Commands Using DBMS_PIPE (here for local copy), which has a Pro-C program (daemon.pc, which I've modified to terminate if the pipe connection is lost) that you can run as a listener to execute system commands passed through a PL/SQL package (daemon.sql), which uses database pipes for message passing.  The daemon pipe listener doesn't take up any CPU time while it is waiting for a message; however, make sure you shut down the daemon listener before you shut down your database (using "execute youruserid.daemon.stop;" in sqlplus); otherwise, it starts chewing up CPU cycles.  This also provides a good example of how to use database pipes for other communications tasks that you might need to develop.  As an example of using this package, to send a unix mail message with the current count of spriden records and the system date and time, you would use the following (which has a 2 second timeout, in case the daemon listener isn't running).  (Thanks to James Mann and Brad Christensen for pointing this out to me.)  For e-mailing from Oracle 9.2, you could also see E-Mailing From Oracle.

declare
     res number;
     cmd varchar2(80);
begin
     select 'mail -s "' || count(*) || ' spriden records at `date`" youraddr@yoursite.edu </dev/null'
          into cmd from spriden;
     res := youruserid.daemon.execute_system(cmd,2);
exception
     when others
     then
          null;
end;
/

Here are a few tips on how to get it set up and running:

1. Change *uid in daemon.pc from scott/tiger to your user ID and password.
2. Compile daemon.pc using Banner's sctproc.mk file:
        make -f $BANNER_HOME/general/c/sctproc.mk CHECKOPT="sqlcheck=semantics
             userid=youruserid/yourpassword" daemon
   Note that if you upgrade Oracle (such as 7.x to 8.x), you will have to stop this daemon listener (using the unix "kill" command), recompile daemon.pc, and restart it; otherwise, it will probably stop working.
3. Move the resulting $BANNER_HOME/general/exe/daemon to another directory if you don't want it left in the general directory.
4. Make sure your user ID has create procedure privileges. From a dba or system account in sqlplus:
        grant create procedure to youruserid;
5. From your user ID in sqlplus, create the daemon package:
        @daemon.sql
6. Make sure others can now use the package:
        grant execute on daemon to public;
7. Start the daemon listener from the unix command prompt (change ">/dev/null" to something like ">/home/mydirectory/daemon.lst" to have it write to a debug file):
        nohup daemon >/dev/null 2>&1 &

Now, you can run PL/SQL procedures containing daemon.execute_system.

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.