ITEC NEWSLETTER

Vol. 9, No. 6
June 8, 2001

Table of Contents

1.      UsingDBMS_PIPE to run operating system commands from SQL*Plus or PL/SQL

       
1.       UsingDBMS_PIPEto run operating system commands from SQL*Plus or PL/SQL

Using the code in Oracle Note 14082.1 (see below) the following procedurecanbe used to implement the DBMS_PIPE package to run operating system commandsfromwithin SQL*Plus or PL/SQL.

1. Create a database user to own the necessary packages.  In thisexample I am using DAEMON as the username.

2. As SYS issue a GRANT EXECUTE ON DBMS_PIPE TO PUBLIC; command.

3. From Oracle Note 14082.1 extract the daemon.sql and daemon.pc files.

4. Log on to SQL*Plus as the DAEMON user that you created.  Run thedaemon.sql file to create DAEMON package.

5. From the operating system edit the daemon.pc file and change thescott/tigerusername and password to DAEMON/PASSWORD.

6. Pre-compile the daemon.pc program.

   > proc userid=daemon sqlcheck=semantics daemon.pc

7. Now make the executable.

   > make -f $ORACLE_HOME/precomp/demo/proc/demo_proc.mk buildEXE=daemon OBJS=daemon.o

8. You can now run the daemon in the background by entering

   > ./daemon &

   You should see something similar to the following:

   > ./daemon&        
   [1]     1406
   > Daemon connected.
   Daemon waiting...
   >

   At this point the daemon is waiting for something to do. If after starting the daemon, you get errors like the following, it is becauseyou forgot to do either step 2 or step 5:

   Daemon continuing.
   Daemon error: invalid command '' received.
   Daemon error while executing:
   ORA-06550: line 1, column 18:
   PLS-00201: identifier 'SYS.DBMS_PIPE' must be declared
   ORA-06550: line 1, column 7:
   PL/SQL: Statement ignored
   ORA-06550: line 1, column 81:
   PLS-00201: identifier 'SYS.DBMS_PIPE' must be declared
   ORA-06550: line 1, column 81:
   PL/SQL: Statement ignored   

9. In Oracle, create a procedure owned by the DAEMON user as follows:

   procedure OS_COMMAND (CMD in varchar2 default 'echo "Nocommand specified"')
   is
     RETURN_VALUE number;
   begin
     RETURN_VALUE :=daemon.execute_system(command=>CMD);
   end OS_COMMAND;

10. Through SQL*Plus, you can now access operating system commands, providedthat
    the daemon started successfully in step 8.

    > sqlplus

    SQL*Plus: Release 8.1.7.0.0 - Production on Tue Jun 515:11:53 2001
    (c) Copyright 2000 Oracle Corporation.  All rightsreserved.

    Enter user-name: DAEMON
    Enter password:

    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.0.0 -Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production

    SQL> execute OS_COMMAND('ls -alp');
    Will execute system command 'ls -alp'
    total 97
    drwxr-xr-x   2 oracle  oinstall   8192 Jun  5 14:47 ./
    drwxr-x--x   7 oracle  oinstall   8192 Jun  5 13:14 ../
    -rwxr-xr-x   1 oracle  oinstall  29392 Jun  5 14:47 daemon
    -rw-r--r--   1 oracle  oinstall  25114 Jun  5 14:46 daemon.c
    -rw-r--r--   1 oracle  oinstall      0 Jun  5 14:46 daemon.lis
    -rw-r--r--   1 oracle  oinstall  13632 Jun  5 14:47 daemon.o
    -rw-r--r--   1 oracle  oinstall   6743 Jun  5 14:46 daemon.pc
    -rw-r--r--   1 oracle  oinstall   5754 Jun  5 13:04 daemon.sql

    PL/SQL procedure successfully completed.

    SQL>

11. To stop the DAEMON, you should issue the following:

    SQL> execute DAEMON.STOP;

    PL/SQL procedure successfully completed.

    SQL> Daemon exiting.

    SQL>

    If the daemon is not stopped this way (i.e. you kill itatthe OS level) there
    will be dead connections within Oracle.  They shouldeventually be cleaned
    up by the PMON process, but it may take a while.

Doc ID:  Note:14082.1
Subject:  Dynamic SQL and System Commands Using DBMS_PIPE
Type:  BULLETIN
Status:  PUBLISHED
 Content Type:  TEXT/PLAIN
Creation Date:  01-JUL-1994
Last Revision Date:  31-MAY-2000
 

Abstract:            This document presents an implementation of dynamic SQL
                     and the HOST command from PL/SQL using the DBMS_PIPE
                     package.  Source code for the implementation is
                     provided.
______________________________________________________________________________

              Dynamic SQL and System Commands Using DBMS_PIPE

 

Two features which would be very nice in PL/SQL are the ability to executea
system command (like HOST in SQL*Forms), and the capability to executedynamic
SQL.  Neither of these features are available with PL/SQL 2.0.  Thelatter of
these, dynamic PL/SQL, is available (via the dbms_sql package) in PL/SQL
2.1, which is released with Oracle 7.1.  However, system commands are notyet
available with this release.  This bulletin presents an implementation ofthese
2 valuable features using the DBMS_PIPE package.  You must have both thePro*C
precompiler and a C compiler installed for this to work.

Note that these examples are also documented in the Oracle8 Application
Developers Guide, although you may wish to use external callouts in Oracle8
as these provide a clean interface between PL/SQL and C code.

Background
~~~~~~~~~~
In order to explain how this works, we need some background about theDBMS_PIPE
package.  This package is owned by SYS, and is created by thecatproc.sql
script.  In order to run it, you will need to execute privileges onit. Contact
your DBA if this is not the case.  This package enables you to sendmessages
between sessions connected to the same database.  One of the sessions willbe
your PL/SQL block, and the other will be the enclosed C program.

This bulletin includes two files, daemon.pc and daemon.sql:

daemon.pc is the source code for a C program, called a 'daemon'.  Thisis
because it needs to be running before it can receive messages.  However,itis
'sleeping' most of the time.  In this state, it is looping while waitingfor a
message to be received over the pipe.  When a message is received, itwill
'wake up' and process the message.  The term 'daemon' is common indiscussions
about the UNIX operating system, and there are many UNIX daemons.  The
'sleeping' and 'waking up' are implemented by calls to
dbms_pipe.receive_message(), which will sleep until a message is received.

daemon.sql is the source code for a PL/SQL package.  This packagehas
procedures which use dbms_pipe to send and receive message to and from the
daemon.  One thing to note is that full handshaking is used.  Thismeans that
the daemon will always send a message back to the package (except in thecase
of the 'STOP' command).  This is valuable, since it allows us to makesure
that the daemon is running.

To set this up on your system, first extract daemon.pc and daemon.sqlinto
separate files on your system.  Then connect to the database withSQL*Plus,
and load the daemon.sql file with '@daemon'.  This will create thedaemon
package.  You then need to compile daemon.pc into an executable.  Howto do
this varies according to your operating system:  On UNIX systems, you canuse
the 'proc.mk' makefile.  On VMS, you can use the 'lnproc' linkscript. Note
that you will have to use the precompile option 'sqlcheck=semantics', since
the program contains embedded PL/SQL blocks.  You will also need to usethe
'userid' precompile option, so that the precompiler will know how toconnect
to the database.  For example, 'userid = scott/tiger'.  (Don't typethe quotes,
they are only delimeters in this text.)  You can now use the daemonpackageto
execute system commands and (limited) dynamic SQL by using the functions
daemon.execute_system() and daemon.execute_sql().

Note that you can call the daemoon packaged procedures execute_system()and
execute_sql() from anonymous blocks in SQL*Plus, from an embedded PL/SQL
block in another precompiler program, or even a forms trigger.  Thispackage
can only execute non-query SQL statements, with no bind variables.  Lookat
the code for more details on these functions.

Remember that the daemon needs to be running first.  So you may want torun it
in the background, or in another window besides the SQL*Plus session fromwhich
you call it.

NOTE: The daemon package also uses the dbms_output package to displaythe
results.  To get this to work, you need to have execute privileges onthis
package as well.  It is also owned by SYS, and created bycatproc.sql. You
will also need to issue 'set serveroutput on' from the SQL> prompt inSQL*Plus
before running it.

See Appendix A of the 'ORACLE7 Server Application Developer's Guide' formore
information on the dbms_pipe and dbms_output packages.  

    Disclaimer:
    These scripts are provided for educational purposes only.They are not
    supported by Oracle Support Services.  The scripts havebeen tested and
    appear to work as intended.  However, you should alwaystest any script
    before relying on it.

 /************************************************************************
File:          daemon.sql
Written by:    Scott Urman, Language Support
Last Modified: 11/7/94

This is the source code for the daemon package.  It sends messages tothe
daemon listener via dbms_pipe.  The package has two functions and one
procedure:

execute_sql: passes the sql command given by the first argument to thedaemon
            listener for execution.  The sql command must not be a query.
            Returnsthe sqlcode after execution of the command.
execute_system: passes the system command given by the first argument tothe
             daemonlistener for execution in the operating system.  Returns
             theresult of the system command.
stop: causes the daemon to exit.  After this command, calls toexecute_sql
             andexecute_system will fail until the daemon is restarted.

This package sends the first message to the daemon over the pipe named
'daemon'.  As part of this message, the return pipe name is passed. The name
of the return pipe is the value of dbms_pipe.unique_session_name.  Thisway,
each session will be listening on its own pipe, and so one session won't
receive the messsages meant for another.

************************************************************************/

create or replace package daemon as
  /* Executes a non-query sql statement or plsql block. Arguments:
     command: the sql statement to execute
     timeout: (optional) number of seconds to wait to sendor receive a
             message
     Returns the sqlcode after execution of the statement.*/
  function execute_sql(command varchar2, timeout number default 10)
    return number;

  /* Executes a system (host) command.  Arguments:
     command: the command to execute
     timeout: (optional) number of seconds to wait to sendor receive a
             message
     Returns the value passed to the operating system bythecommand. */
  function execute_system(command varchar2, timeout number default 10)
    return number;

  /* Tells the daemon listener to exit.  Arguments:
     timeout: (optional) number of seconds to wait to sendthe message. */
  procedure stop(timeout number default 10);
end daemon;
/

create or replace package body daemon as

  function execute_system(command varchar2, timeout number default10)
    return number is

    s number;
    result varchar2(20);
    command_code number;
    pipe_name varchar2(30);
  begin

    /* Use uniqe_session_name to generate a unique name forthe return pipe.
       We include this as part of the initalmessage to the daemon, and it is
       send along the pipe named 'daemon'. */
    pipe_name := dbms_pipe.unique_session_name;

    /* Send the 'SYSTEM' command to the daemon. */
    dbms_pipe.pack_message('SYSTEM');
    dbms_pipe.pack_message(pipe_name);
    dbms_pipe.pack_message(command);
    s := dbms_pipe.send_message('daemon', timeout);
    if s <> 0 then
      raise_application_error(-20010,
        'Execute_system: Error whilesending. Status = ' || s);
    end if;

    /* Check for the handshake message.  Note that wearenow listening on
       the pipe which is unique to this session.*/
    s := dbms_pipe.receive_message(pipe_name, timeout);
    if s <> 0 then
      raise_application_error(-20011,
        'Execute_system: Error whilereceiving.  Status = ' || s);
    end if;

    /* Get the operating system result code, and display itusing
       dbms_output.put_line(). */
    dbms_pipe.unpack_message(result);
    if result <> 'done' then
      raise_application_error(-20012,
        'Execute_system: Done notreceived.');
    end if;

    dbms_pipe.unpack_message(command_code);
    dbms_output.put_line('System command executed.  result=' ||
                        command_code);
    return command_code;
  end execute_system;  

  function execute_sql(command varchar2, timeout number default 10)
    return number is

    s number;
    result varchar2(20);
    command_code number;
    pipe_name varchar2(30);
  begin  

    /* Use uniqe_session_name to generate a unique name forthe return pipe.
       We include this as part of the initalmessage to the daemon, and it is
       send along the pipe named 'daemon'. */
    pipe_name := dbms_pipe.unique_session_name;

    /* Send the 'SQL' command to the daemon. */
    dbms_pipe.pack_message('SQL');
    dbms_pipe.pack_message(pipe_name);
    dbms_pipe.pack_message(command);
    s := dbms_pipe.send_message('daemon', timeout);
    if s <> 0 then
      raise_application_error(-20020,
        'Execute_sql: Error whilesending. Status = ' || s);
    end if;

    /* Check for the handshake message.  Note that wearenow listening on
       the pipe which is unique to this session.*/
    s := dbms_pipe.receive_message(pipe_name, timeout);
    if s <> 0 then
      raise_application_error(-20021,
        'Execute_sql: Error whilereceiving. Status = ' || s);
    end if;

    /* Get the result code from the SQL statement, anddisplayit using
       dbms_output.put_line(). */
    dbms_pipe.unpack_message(result);
    if result <> 'done' then
      raise_application_error(-20022,
        'Execute_sql: Done notreceived.');
    end if;

    dbms_pipe.unpack_message(command_code);
    dbms_output.put_line('SQL command executed.  sqlcode ='|| command_code);
    return command_code;
  end execute_sql;  

  procedure stop(timeout number default 10) is
    s number;
  begin

    /* Send the 'STOP' command to the daemon. */
    dbms_pipe.pack_message('STOP');
    s := dbms_pipe.send_message('daemon', timeout);
    if s <> 0 then
      raise_application_error(-20030,
        'Stop: Error while sending. Status = ' || s);
    end if;
  end stop;

end daemon;
/  

/************************************************************************
File:          daemon.pc
Written by:    Scott Urman, Language Support
Last Modified: 11/7/94

This is the source code for the daemon listener to implement dynamic sql
and system commands from plsql.  The program accepts three
'daemon commands':

STOP:   causes the daemon to disconnect from Oracle and exit.
SYSTEM: causes the daemon to execute the next item on the pipe as a
        operating system command.
SQL:    causes the daemon to execute the next item on the pipeasan
        sql statement.  Also returnsthesqlcode resulting from the
        statement.

The daemon commands are received over the pipe named 'daemon'.  As partof the
first message sent along this pipe, the name of the pipe to use for thereturn
session is passed.

************************************************************************/
#include <stdio.h>
#include <string.h>
EXEC SQL include sqlca;

EXEC SQL begin declare section;
  char *uid = "scott/tiger";  /* User/password to connecttoOracle */
  intstatus;                /* Return value for dbms_pipe.send_message
                                and dbms_pipe.receive_message */
  varchar command[20];        /* Daemoncommand to execute */
  varchar value[2000];        /* Value(SQL statement or system command)
                                associated with previous daemon command */
  varchar return_name[30];    /* Name of the pipe on whichto send the
                                results */
EXEC SQL end declare section;

/* This is the error handler for connecting to Oracle.  If we failed onthe
   connection attempt, we need to exit the program. */
void connect_error() {

  char msg_buffer[512];
  int msg_length;
  int buffer_size = 512;

  EXEC SQL whenever sqlerror continue;
  sqlglm(msg_buffer, &buffer_size, &msg_length);
  printf("Daemon error while connecting:\n");
  printf("%.*s\n", msg_length, msg_buffer);
  printf("Daemon quitting.\n");
  exit(1);
  }  

/* This is the general error handler. Note that we don't exit the programin
   this case.  We just print the error and continue.  Thisis because any
   errors probably will not affect future operations, and we shouldkeep the
   daemon running.  This of course depends on the error, and youmay want to
   change this behavior. */
void sql_error() {

  char msg_buffer[512];
  int msg_length;
  int buffer_size = 512;

  EXEC SQL whenever sqlerror continue;
  sqlglm(msg_buffer, &buffer_size, &msg_length);
  printf("Daemon error while executing:\n");
  printf("%.*s\n", msg_length, msg_buffer);
  printf("Daemon continuing.\n");
  }

main() {

  EXEC SQL whenever sqlerror do connect_error();
  EXEC SQL connect :uid;
  printf("Daemon connected.\n");

  EXEC SQL whenever sqlerror do sql_error();
  printf("Daemon waiting...\n");
  while (1) {
    /* Wait for a message to be received, using pipe daemon.*/
    EXEC SQL EXECUTE
      begin
        :status :=dbms_pipe.receive_message('daemon');
        if :status = 0 then
         dbms_pipe.unpack_message(:command);
        end if;
      end;
    END-EXEC;

    if (status == 0) {
      /* At this point, we have successfully receivedamessage.  Now we
         need to determine which daemoncommand to execute. */
      command.arr[command.len] = '\0';
      if (!strcmp((char *)command.arr,"STOP")) {
        /* STOP command received. Simply exit the program. */
        printf("Daemonexiting.\n");
        break;
        }

      else if (!strcmp((char *)command.arr,"SYSTEM")) {
        /* SYSTEM command received. Unpack the next 2 values.  These will be
           the name of thereturn pipe, and the command to pass to the
           operatingsystem. */
        EXEC SQL EXECUTE
          begin
           dbms_pipe.unpack_message(:return_name);
           dbms_pipe.unpack_message(:value);
          end;
        END-EXEC;
        value.arr[value.len] = '\0';
        printf("Will execute systemcommand '%s'\n", value.arr);

        /* Execute the command. */
        status = system(value.arr);

        /* Send a message back toindicatethat the command has been
           executed. Also send the result of the system command.
           Use the pipepassedin from the first message for this. */

        EXEC SQL EXECUTE
          begin
           dbms_pipe.pack_message('done');
           dbms_pipe.pack_message(:status);
            :status :=dbms_pipe.send_message(:return_name);
          end;
        END-EXEC;

        if (status) {
          printf("Daemonerrorwhile responding to system command.");
          printf(" status: %d\n", status);
          }
        }

      else if (!strcmp((char *)command.arr,"SQL")) {
        /* SQL command received. Unpackthe next 2 values.  These will be
           the name of thereturn pipe, and the SQL command to execute. */
        EXEC SQL EXECUTE
          begin
           dbms_pipe.unpack_message(:return_name);
           dbms_pipe.unpack_message(:value);
          end;
        END-EXEC;
        value.arr[value.len] = '\0';
        printf("Will execute sqlcommand'%s'\n", value.arr);

        /* Execute the command. Notethat we don't want to go to the error
           handler if thereisa problem - we just pass the code back. */
        EXEC SQL whenever sqlerrorcontinue;
        EXEC SQL EXECUTE IMMEDIATE:value;
        status = sqlca.sqlcode;

        /* Reset the error handler, andsend a message back to indicate
           that the commandhas been executed.  Also send the sqlcode.
           Use the pipepassedin from the first message for this. */

        EXEC SQL whenever sqlerror dosql_error();
        EXEC SQL EXECUTE
          begin
           dbms_pipe.pack_message('done');
           dbms_pipe.pack_message(:status);
            :status :=dbms_pipe.send_message(:return_name);
          end;
        END-EXEC;

        if (status) {
          printf("Daemonerrorwhile responding to sql command.");
          printf(" status: %d\n", status);
          }
        }

      else {
        /* Invalid daemon command received.*/
        printf("Daemon error: invalidcommand '%s' received.\n", command.arr);
        }
      }
    else {
      /* We get here if an error was received whilethedaemon was waiting.
         If the status = 1, this is atimeout and is probably not a problem.
         However, the default timeoutfor the receive_message function is
         1000 days, so unless thedaemonis kept running for over 3 years
         without receiving a signal,youwon't time out. */
      printf("Daemon error while waiting forsignal.");
      printf("  status = %d\n",status);
      }
  }

  EXEC SQL commit work release;
}
_____________________________________________________________________________
Keywords:PROC;PRECOMPILER;PLSQL;DBMS_PIPE;HOST;SYSTEM;DYNAMIC;SQL;DBMS_OUTPUT
_____________________________________________________________________________

 --------------------------------------------------------------------------------
 
 Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. LegalNotices and Terms of Use. 

 Submitted by Joe Lofft, Joe.Lofft@itec.mail.suny.edu<mailto:Joe.Lofft@itec.mail.suny.edu>



PLEASE COMPLETE THE ITEC SUPPORT AND SERVICES SURVEY IF YOU HAVE NOTALREADY DONE SO (http://spider.itec.suny.edu/scacad/itecsurvey/survey2001.htm).  THANK YOU!

 

Editor:  Barbara A. Boquard

Barb.Boquard@itec.mail.suny.edu

ITEC's General Office

716/878-ITEC (4832)

Operations

716/878-5122

FAX

716/878-3485

Web Page

http://www.itec.suny.edu


ITEC Home