/************************************************************************
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 to the
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 the daemon
             listener for execution.  The sql command must not be a query.
             Returns the sqlcode after execution of the command.
execute_system: passes the system command given by the first argument to the
             daemon listener for execution in the operating system.  Returns
             the result of the system command.
stop: causes the daemon to exit.  After this command, calls to execute_sql
             and execute_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.  This way,
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 send or 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 send or receive a
              message
     Returns the value passed to the operating system by the command. */
  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 send the message. */
  procedure stop(timeout number default 10);
end daemon;
/

create or replace package body daemon as
   function execute_system(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 for the return pipe.
       We include this as part of the inital message 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 while sending.  Status = ' || s);
    end if;

    /* Check for the handshake message.  Note that we are now 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 while receiving.  Status = ' || s);
    end if;

    /* Get the operating system result code, and display it using
       dbms_output.put_line(). */
    dbms_pipe.unpack_message(result);
    if result <> 'done' then
      raise_application_error(-20012,
        'Execute_system: Done not received.');
    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 for the return pipe.
       We include this as part of the inital message 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 while sending.  Status = ' || s);
    end if;

    /* Check for the handshake message.  Note that we are now 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 while receiving.  Status = ' || s);
    end if;

    /* Get the result code from the SQL statement, and display it using
       dbms_output.put_line(). */
    dbms_pipe.unpack_message(result);
    if result <> 'done' then
      raise_application_error(-20022,
        'Execute_sql: Done not received.');
    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;
/