Executar comandos Shell atravéz da PL/SQL

[LoadingXp]

Por favor alguém sabe como eu faço para executar comandos no DOS através da PL/SQL??
Preciso mover um arquivo depois de rodar com o UTL_FILE e importa-lo para base de dados.

Obrigado.

[rsnaumann]

Cara.
Eu já usei uma classe java dentro do banco pra executar comandos shell, não tenho o código, mas sei que funciona.

Da uma pesquisada que você acha. Se eu encontrar eu posto aqui.

Valeu.

[dr_gori]

Sim, é possível executar com JAVA.
Mas também tem essa forma abaixo. Crie a package chamada DAEMON e é só alegria

Para executar os comandos, basta informar o comando desejado:

Selecionar tudo

var_numerica := daemon.execute_system ( 'ls -l > teste.txt' , 100 );

Selecionar tudo

Document ID:          105688.158

Title:                Dynamic SQL and System Commands Using DBMS_PIPE
Creation Date:        15 June 1993
Last Revision Date:   7 November 1994
Distribution Code:    External
Product Version:      1.5, 2.0

Platform:             Generic
Information Type:     Solution
Impact:               Medium
Abstract:             This document presents an implemention of dynamic SQL
                      and the HOST command from PL/SQL using the DBMS_PIPE

                      package.  Source code for the implementation is
                      provided.
Keywords:             PROC;PRECOMPILER;PLSQL;DBMS_PIPE;HOST;SYSTEM;DYNAMIC;SQL;
                      DBMS_OUTPUT


______________________________________________________________________________

               Dynamic SQL and System Commands Using DBMS_PIPE


Two features which would be very nice in PL/SQL are the ability to execute a

system command (like HOST in SQL*Forms), and the capability to execute dynamic
SQL.  Neither of these features are available with PL/SQL 2.0.  The latter 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 not yet
available with this release.  This bulletin presents an implementation of these
2 valuable features using the DBMS_PIPE package.  You must have both the Pro*C

precompiler and a C compiler installed for this to work.

In order to explain how this works, we need some background about the DBMS_PIPE
package.  This package is owned by SYS, and is created by the catproc.sql

script.  In order to run it, you will need execute privileges on it.  Contact
your DBA if this is not the case.  This package enables you to send messages
between sessions connected to the same database.  One of the sessions will be

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'.  This is

because it needs to be running before it can receive messages.  However, it is
'sleeping' most of the time.  In this state, it is looping while waiting for a
message to be received over the pipe.  When a message is received, it will

'wake up' and process the message.  The term 'daemon' is common in discussions
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 package has
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.  This means that
the daemon will always send a message back to the package (except in the case
of the 'STOP' command).  This is valuable, since it allows us to make sure

that the daemon is running.

To set this up on your system, first extract daemon.pc and daemon.sql into
separate files on your system.  Then connect to the database with SQL*Plus,
and load the daemon.sql file with '@daemon'.  This will create the daemon

package.  You then need to compile daemon.pc into an executable.  How to do
this varies according to your operating system:  On UNIX systems, you can use
the 'proc.mk' makefile.  On VMS, you can use the 'lnproc' link script.  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 use the
'userid' precompile option, so that the precompiler will know how to connect

to the database.  For example, 'userid = scott/tiger'.  (Don't type the quotes,
they are only delimeters in this text.)  You can now use the daemon package to
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.  This package

can only execute non-query SQL statements, with no bind variables.  Look at
the code for more details on these functions.

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

you call it.

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

See Appendix A of the 'ORACLE7 Server Application Developer's Guide' for more

information on the dbms_pipe and dbms_output packages.
  /************************************************************************
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;
/
   /************************************************************************
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 pipe as an
        sql statement.  Also returns the sqlcode resulting from the
        statement.
 The daemon commands are received over the pipe named 'daemon'.  As part of the

first message sent along this pipe, the name of the pipe to use for the return
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 connect to Oracle */
  int status;                 /* Return value for dbms_pipe.send_message

                                 and dbms_pipe.receive_message */
  varchar command[20];        /* Daemon command 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 which to send the
                                 results */
EXEC SQL end declare section;

/* This is the error handler for connecting to Oracle.  If we failed on the

   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 program in

   this case.  We just print the error and continue.  This is because any
   errors probably will not affect future operations, and we should keep the
   daemon running.  This of course depends on the error, and you may 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 received a message.  Now we
         need to determine which daemon command to execute. */
      command.arr[command.len] = '\0';

      if (!strcmp((char *)command.arr, "STOP")) {
        /* STOP command received.  Simply exit the program. */
        printf("Daemon exiting.\n");
        break;
        }

      else if (!strcmp((char *)command.arr, "SYSTEM")) {

        /* SYSTEM command received.  Unpack the next 2 values.  These will be
           the name of the return pipe, and the command to pass to the
           operating system.  */
        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 system command '%s'\n", 
value.arr);

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

        /* Send a message back to indicate that the command has been
           executed.  Also send the result of the system command.

           Use the pipe passed in 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("Daemon error while responding to system command.");
          printf("  status: %d\n", status);

          }
        }

      else if (!strcmp((char *)command.arr, "SQL")) {
        /* SQL command received.  Unpack the next 2 values.  These will be
           the name of the return 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 sql command '%s'\n", value.arr);

        /* Execute the command.  Note that we don't want to go to the error
           handler if there is a problem - we just pass the code back. */

        EXEC SQL whenever sqlerror continue;
        EXEC SQL EXECUTE IMMEDIATE :value;
        status = sqlca.sqlcode;
         /* Reset the error handler, and send a message back to indicate
           that the command has been executed.  Also send the sqlcode.

           Use the pipe passed in from the first message for this. */

        EXEC SQL whenever sqlerror do sql_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("Daemon error while responding to sql command.");

          printf("  status: %d\n", status);
          }
        }

      else {
        /* Invalid daemon command received. */
        printf("Daemon error: invalid command '%s' received.\n", 
command.arr);
        }
      }
    else {
      /* We get here if an error was received while the daemon was waiting.
         If the status = 1, this is a timeout and is probably not a problem.
         However, the default timeout for the receive_message function is

         1000 days, so unless the daemon is kept running for over 3 years
         without receiving a signal, you won't time out. */
      printf("Daemon error while waiting for signal.");
      printf("  status = %d\n", status);

      }
  }

  EXEC SQL commit work release;
}
_____________________________________________________________________________
                                           Oracle Worldwide Technical Support