Solved

calling shell script from oracle stored procedure or trigger

Posted on 2002-04-04
8
1,732 Views
Last Modified: 2008-02-26
Hi Guys,

I have one question i want to know how can i call a shell script abc.sh on solaris2.6 with oracle8.1.5.
Using oracle trigger how can i execute abc.sh ?

The bottomline is we have to call a java-servlet on that trigger, so we are thinking of calling shell script which in turn will java-servlet. so if someone can tell me anyone or both of the options like either executing java-servlet from trigger or executing shell script from trigger.
Please its very urgent.

Thanks.
0
Comment
Question by:arpit080399
8 Comments
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
You can use external procedures or dbms_pipe to execute the shell script.

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.

Note that these examples are also documented in the Oracle8 Application
Developers Guide, although you may wish to use external callouts in Oracle 8
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 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.


    Disclaimer:
    These scripts are provided for educational purposes only. They are not
    supported by Oracle Support Services.  The scripts have been tested and
    appear to work as intended.  However, you should always test 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 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;
}

0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
I've used the DBMS_PIPE / daemon approach successfully for years to do operating system commands from PL\SQL, since they aren't natively supported in PL\SQL.  You should also be able to use external procedures or Java procedures with Oracle8.1.5 or higher.  I haven't tried external procedures, but have tested Java a bit and gotten that to work as well to do at least some operating system commands.
0
 

Expert Comment

by:FarazMSyed
Comment Utility
hi ... here's the complete process of Calling Operating System Commands from PL/SQL using External Procedure. Hope it will help u .....


The ability to call operating system commands from PL/SQL is a feature that is easily implemented in Oracle 8i using External Procedures.

STEP 1:
=======
Get Connect to Sun Machine by Oracle user and write an External Procedure Code on Sun Server in vi editor:

$ vi /Oracle_Home/lib/shell.c

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

void sh (char *);
void sh (char *cmd)
{
     int num;
     num = system(cmd);
}

STEP 2:
=======
After successful creation of shell.c file on the given path, issue the following commands to compile that code.

     $ gcc –c shell.c

this command will compile shell.c and generate an OBJ file named shell.o. After this issue the following command to create a link directory:

     $ ld -r -o shell.so shell.o

this command will generate another shell.so (Shared OBJ) file.
STEP 3:
=======
     Now we can use the demo_rdbms.mk makefile to build shared libraries for use in external procedures. Using this method insulates you from any Operating System specific dependencies. Issue the following command:

      $ make –f demo_rdbms.mk extproc_no_context \
        SHARED_LIBNAME=shell.so OBJS=shell.o

STEP 4:
=======
     Now make some changes in listener.ora file by doing the following:
     You can find the listener.ora on path :
          .../Oracle_Home/network/admin/listener.ora
     
     Change this file by opening it into vi editor and add the text :

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = sunserver2)(PORT = 1521))
      )
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /export/disk0/Oracle)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = aquad2)
      (ORACLE_HOME = /export/disk0/Oracle)
      (SID_NAME = aquad2)
      (PROGRAM=.../Oracle_Home/bin/extproc)
      (ENVS='PATH=/bin:/usr/bin:/usr/ccs/bin:/usr/ucb')
    )
  )
STARTUP_WAIT_TIME_LISTENER_PROC = 0
CONNECT_TIMEOUT_LISTENER_PROC = 10
TRACE_LEVEL_LISTENER_PROC = OFF

STEP 5:
=======
Now Connect from Oracle (SQL Plus) by system user and create a library by running the following command:

SQL> CREATE LIBRARY shell_lib is 'Oracle_Home/lib/shell.so';
/
Note: The directory in quotes is the current location of the library that was created in the STEP 1.

STEP 6:
=======
     Create a Procedure which calls that library by the following statements:

SQL> Create or replace procedure shell (cmd IN char)
     as external
          name "sh"
          library shell_lib
          language C
          parameters (cmd string);
/

now for executing the procedure.

SQL> exec shell (‘ls’);

It will returns the list of files and directories on sun terminal. And u can as well use ...

regards
Faraz
0
 

Author Comment

by:arpit080399
Comment Utility
hey guys,

i dont want PRO*C code but pure oracle code in procedure or triggers.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Pure Oracle code in procedures or triggers cannot execute operating system commands by itself.  You need to use one of the methods listed in the earlier comments:
1. DBMS_PIPE / daemon
2. external procedures
3. JAVA

Then you call one of these from PL\SQL.
0
 
LVL 7

Expert Comment

by:waynezhu
Comment Utility
In addtion to the above helpful comments, you might find the following link (and the like) are also helpful
"execute shell script from stored procedure"
"http://asktom.oracle.com/pls/ask/f?p=4950:8:661576::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:16212348050,%7Bshell%7D"
0
 
LVL 5

Accepted Solution

by:
sora earned 100 total points
Comment Utility
I am assuming that you want to be able to run some JAVA SERVLET code when a triggering event takes place and you are not really particular of which JVM the java code runs under.

Then this might work for you...........that is if you decided that you positively do not want to use External routines as described in very detail above in a previous post.

You can use Java Stored Procedures. Java and PL/SQL can be called from each other using this method.

One way is to convert your servlet into a java stored procedure and then call it from a trigger


See this URL for more details:

http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/java.817/a81358/toc.htm

It tells you how to create Java stored procedures and how to call them from pl/sql. It also gives you a list of Java classes available as part of Oracle JServer. Yes-note that JServer needs to running as part of your Oracle Server in order to do this. I think the URL above describes what needs to be setup as well.

sora
0
 

Author Comment

by:arpit080399
Comment Utility
thanks it helped.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now