Solved

Calling shell script from Oracle

Posted on 2006-07-05
17
6,914 Views
Last Modified: 2008-01-09
We need to call a UNIX shell script from an oracle 9i stored procedure synchronously i.e. the control should come back to the procedure once the script completes. Would you know of a method to do this?
0
Comment
Question by:suhinrasheed
  • 5
  • 5
  • 2
  • +3
17 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17041276
It is possible only by using Oracle external procedures written in either C or Java.
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10795/adfns_ex.htm
0
 
LVL 6

Expert Comment

by:DLyall
ID: 17041396
0
 
LVL 6

Expert Comment

by:DLyall
ID: 17041401
sorry, above actually shows example in async mode.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 17041524
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17042970
>> We need to call a UNIX shell script from an oracle 9i stored procedure synchronously

for 9i , look at this:

http://web.njit.edu/info/limpid/DOC/appdev.920/a96590/adg11rtn.htm#1656
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17043939
You have to use Java for this, since Oracle PL\SQL does not support "host" calls directly.  Here are the two files that we thought should work for this in our Oracle10 database on Linux, first the *.java file (that we named "oscommands.java") then the *.sql file (that we named: "oscommands.sql").  Just run these as SYS (or as a DBA-privileged user in your system).  Following that are the two files we actually use now, "host.java" and "host.sql".  

Note that the "host.java" file does have two values that are specific to our system that I have replaced with: [IP-ADDRESS] and [ORACLE_SID].  You will have to replace those variables with values that are valid in your system.  Also, the grants in the "host.sql" file have [Oracle username].  You will have to replace that with the owner of this procedure in your system.

--the "oscommands.java" file:
CREATE OR REPLACE and COMPILE
JAVA SOURCE NAMED "OSCommands"
AS
import java.lang.* ;
import java.io.* ;

public class OSCommands
{
  public static void RunCMD(String[] args)
   {
    try
   {

     Process m ;
     String S = "" ;

     m = Runtime.getRuntime().exec(args) ;

     BufferedReader in =
           new BufferedReader(new InputStreamReader(
                               m.getInputStream()));

       while((S=in.readLine()) != null)
        {
         System.out.println(S);
        }
   }
    catch(Exception ex)
    {
     ex.printStackTrace () ;
    }
  }

    public static String copyFile(String sourceFile, String copyFile) {
       
        InputStream source;  // Stream for reading from the source file.
        OutputStream copy;   // Stream for writing the copy.
        int byteCount;  // The number of bytes copied from the source file.
       
       
      /* Create the input stream.  If an error occurs, end the program. */
        try {
            source = new FileInputStream(sourceFile);
        }
        catch (FileNotFoundException e) {
            return "Error: Can't find file \"" + sourceFile + "\".";
        }
       
      /* Create the output stream.  If an error occurs, end the program. */
       
        try {
            copy = new FileOutputStream(copyFile);
        }
        catch (IOException e) {
            return "Error: Can't open output file \"" + copyFile + "\".";
        }
       
      /* Copy one byte at a time from the input stream to the out put stream,
         ending when the read() method returns -1 (which is the signal that
         the end of the stream has been reached.  If any error occurs, print
         an error message.  Also print a message if the file has bee copied
         successfully.  */
       
        byteCount = 0;
       
        try {
            while (true) {
                int data = source.read();
                if (data < 0)
                    break;
                copy.write(data);
                byteCount++;
            }
            source.close();
            copy.close();
            return "Success: " + byteCount + " bytes copied.";
        }
        catch (Exception e) {
            return "Error: Error occured while copying.  "
            + byteCount + " bytes copied. " + e.toString();
        }
       
    }
   
     public static String delFile(String fileName) {
       
      boolean success = (new File(fileName)).delete();
          if (!success) {
        // Deletion failed
      return "Error: Can't find or open file \"" + fileName + "\" to be deleted.";
          }
       
       
        return "Success: " + fileName + " has been deleted.";
       
    }
   
    public static String renameFile(String oldName, String newName) {
       
     // File (or directory) with old name
    File file = new File(oldName);
   
    // File (or directory) with new name
    File file2 = new File(newName);
   
    // Rename file (or directory)
    boolean success = file.renameTo(file2);
    if (!success) {
        // File was not successfully renamed
      return "Error: File or directory could not renamed.";
    }
   
    // If no error return a successfull message
    return "Success: " + oldName + " was successfully renamed to " + newName;

    }

    public static String touchFile(String fileName) {
       
    File file = new File(fileName);
   
    // Get the last modified time
    long modifiedTime = file.lastModified();
    // 0L is returned if the file does not exist
   
    // Set the last modified time
    long newModifiedTime = System.currentTimeMillis();
    boolean success = file.setLastModified(newModifiedTime);
    if (!success) {
        // operation failed.
      return "Error: Touch of file was unsuccessfull.";
    }

   
    // If no error return a successfull message
    return "Success: " + fileName + " was successfully touched";

    }


}
/
-- end of "oscommands.java" file

-- the "oscommands.sql file:
create or replace PACKAGE OSCommands AS

  --Package spec for java calls to OSCommands class

  --This procedure runs an OS command
  --i.e. exec OSCommands.runCMD('/bin/bash, -c ls');
  PROCEDURE runCMD(OScmd IN VARCHAR2);

  --This function copies a file
  --i.e. select OSCommands.copyfile('/opt/ora_10g1/network/admin/tnsnames.ora', '/tmp/test1.txt') from dual;
  FUNCTION copyfile(sourceFile VARCHAR2, copyFile VARCHAR2) RETURN VARCHAR2;

  --This function deletes a file
  --i.e. select OSCommands.delfile('/tmp/test1.txt') from dual;
  FUNCTION delfile(fileName VARCHAR2) RETURN VARCHAR2;

  --This function renames a file or directory
  --i.e. select OSCommands.renamefile('/tmp/tnsnames.ora', '/tmp/tnsnames.ora.bak') from dual;
  FUNCTION renamefile(oldFile VARCHAR2, newFile VARCHAR2) RETURN VARCHAR2;

  --This function touches(updates timestamp) of a file
  --i.e. select OSCommands.touchfile('/tmp/tnsnames.ora.bak') from dual;
  FUNCTION touchfile(fileName VARCHAR2) RETURN VARCHAR2;

END OSCommands;
/
--** PACKAGE BODY Name: OSCOMMANDS
create or replace PACKAGE BODY Oscommands AS

  --Package body for java calls to OSCommands class
  --For full code refer to OSCommands java class
  --

  --This procedure runs an OS command
  --i.e. exec OSCommands.runCMD('/bin/bash ls -l');
  PROCEDURE runCMD(OScmd IN VARCHAR2) as
--  This didn't work for Oracle10 on Linux as of 21-Sep-2005, so call another "wrapper" procedure
--   that does work for Oracle10 on Linux.
--    AS LANGUAGE JAVA
--  NAME 'OSCommands.RunCMD(java.lang.String[])';
    begin
      -- Note: this will only work for Linux utilities that are in the /bin directory:
      host_command('/bin/'||OScmd);
    end;

  --This function copies a file
  --i.e. select OSCommands.copyfile('/opt/ora_10g1/network/admin/tnsnames.ora', '/tmp/test1.txt') from dual;
  FUNCTION copyfile(sourceFile VARCHAR2, copyFile VARCHAR2) RETURN VARCHAR2
    AS LANGUAGE JAVA
  NAME 'OSCommands.copyFile(java.lang.String, java.lang.String) return java.lang.String';

  --This function deletes a file
  --i.e. select OSCommands.delfile('/tmp/test1.txt') from dual;
  FUNCTION delfile(fileName VARCHAR2) RETURN VARCHAR2
    AS LANGUAGE JAVA
  NAME 'OSCommands.delFile(java.lang.String) return java.lang.String';

  --This function renames a file or directory
  --i.e. select OSCommands.renamefile('/tmp/tnsnames.ora', '/tmp/tnsnames.ora.bak') from dual;
  FUNCTION renamefile(oldFile VARCHAR2, newFile VARCHAR2) RETURN VARCHAR2
    AS LANGUAGE JAVA
  NAME 'OSCommands.renameFile(java.lang.String, java.lang.String) return java.lang.String';

  --This function touches(updates timestamp) of a file
  --i.e. select OSCommands.touchfile('/tmp/tnsnames.ora.bak') from dual;
  FUNCTION touchfile(fileName VARCHAR2) RETURN VARCHAR2
    AS LANGUAGE JAVA
  NAME 'OSCommands.touchFile(java.lang.String) return java.lang.String';

END Oscommands;
/
create public synonym oscommands for oscommands;
grant execute on oscommands to public;
-- end of "oscommands.sql" file

-- "host.java" file:
CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  File:                 host.java                                               ~
~  Author:               Aaron M. Brummitt                                       ~
~  Date Created:         20051129-2254                                           ~
~  Date Modified:        20051201-1548 AMB                                       ~
~                                                                                ~
~  Description: This class contains methods to execute operating system commands ~
~                                                             ~
~  Known Issues/Limitations: Only commands which are in $PATH will be able to be ~
~      executed, unless the full path to the binary is included                 ~
~                                                             ~
~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
*/
public class Host
{
      /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      ~ executeCommand() requires one parameter and returns a string                   ~
      ~                                                                                ~
      ~ Params:                                                                        ~
      ~       String p_sCommand is the command to be executed by the Operating System       ~
      ~                                                             ~
      ~ Return:                                                                        ~
      ~      String      a status                                                          ~
      ~                                                                                ~
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      */
      public static String executeCommand(String p_sCommand) throws IOException
      {
            //local variable declations
            Process p;
            boolean bPrintError = true;
            String sError= "";
            String sLogError = "";
            int iRetStat = -1;
      
            String[] myCMD = {"/bin/sh", "-c", ""};
            myCMD[2] = p_sCommand;
            
            try
            {
                  //run the command
                  p = Runtime.getRuntime().exec(myCMD);
            }//end try
            catch(IOException e)
            {      
                  //catch any Interuption errors and report
                System.out.println("IO Exception on Runtime.getRuntime().exec(): " +  e.getMessage());
                    logCmd(p_sCommand, -1, e.getMessage());
                        return "\nIO Exception Occured in Host.executeCommand\n";                                                

                  
            }//end catch IOException
            
            try
            {
                  //set up the error stream reader
                  BufferedReader stdError = new BufferedReader(new InputStreamReader(p.getErrorStream()));
                        
                  //read the error stream, record them if any
                  while ((sError = stdError.readLine()) != null)
                  {
                        if(bPrintError)
                        {
                              System.out.println("Errors reported");
                              bPrintError = false;
                        }//end if iStatus
            
                        sLogError = sLogError + sError + " ";
                  
                  }//end while read error loop
            }//end try
            catch(IOException e)
            {
                  //catch any Interuption errors and report
                  System.out.println("IO Exception in error reading: " +  e.getMessage());
                return "\nIO Exception Occured in Host.executeCommand\n";
            }//end catch(IOException)                  
            
            try
            {
                  //wait for the proccess to end
                  iRetStat = p.waitFor();
                  
            }//end try
            catch(InterruptedException e)
            {
                  //catch any Interuption errors and report
                  System.out.println("Interrupted Exception on waitFor: " +  e.getMessage());
                  return "\nException Occured in Host.executeCommand\n";
            }//end catch

            //if there was an error, log it
            if(sLogError != "")
            {
                  logCmd(p_sCommand, iRetStat, sLogError);
                  return "\n Error encountered and logged, please check log\n";      
                        
            }//end if sLogError
            
            //everythings good
            return "\nCommand  " + p_sCommand + " successfully completed\n";
            
      }//end executeCommand() Method

      /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      ~ logCmd() requires three parameters and returns void                           ~
      ~                                                                                ~
      ~ Params:                                                                        ~
      ~       String p_sCmd is the command to be executed by the Operating System      ~
      ~      int p_RetStat is the status returned from the Operating System             ~
      ~      String p_sError is the error message returned form the Operating System       ~
      ~                                                                                ~
      ~ Return:                                                                        ~
      ~            VOID                                                                     ~
      ~                                                                                ~
      ~ Credit(s):                                                       ~
      ~      Oracle Metalink website (http://metalink.oracle.com),                    ~
      ~                                                             ~
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      */

      public static void logCmd(String p_sCmd, int p_iRetStat, String p_sError)
      {
            System.out.println("Logging error: \n" + "Command: " + p_sCmd + "\np_iRetStat: " + p_iRetStat + "\np_sError: " + p_sError);
            
            try
            {
                  //create insert string
                  final String sInsert = "INSERT INTO app_error_log(form_name, err_code, err_desc, user_name, timestamp) VALUES (" +
                              "'Host.executeCommand', " + p_iRetStat + ",'" + p_sError.replaceAll("'", "''") + "', user, sysdate)";
                  //System.out.println(sInsert);
                  //connect to DB
                  //syntax examples for Oracle:
                  //Statement stmt = DriverManager.getConnection ("jdbc:oracle:thin:dbUser/dbPassword@[IP_ADDRESS]:1521:[ORACLE_SID]");
                  //Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@" + tnsName,dbUser,dbPassword);
                  Statement stmt = DriverManager.getConnection("jdbc:default:connection:").createStatement();
                  //execute the insert string
                  stmt.executeUpdate(sInsert);
                  //close the connection
                  stmt.close();
            }//end try
            catch (SQLException e)
            {
                  //catch any sql errors
                  System.out.println("SQLException on INSERT: " + e.getMessage());
            }//end catch
      
      }//end logCmd() Method      
      
}//end class host
-- end of "host.java" file

-- "host.sql" file:
CREATE OR REPLACE PROCEDURE Host_Command (p_command IN VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/
-- end of "host.sql" file

Note that you will have to run a number of grants like this for the owner of the "host_command" procedure:
EXEC Dbms_Java.Grant_Permission('[Oracle username]', 'java.io.FilePermission', '<>', 'read ,write, execute, delete');
EXEC Dbms_Java.Grant_Permission('[Oracle username]', 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
EXEC Dbms_Java.Grant_Permission('[Oracle username]', 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
EXEC Dbms_Java.Grant_Permission('[Oracle username]', 'SYS:java.io.FilePermission','/bin/sh', 'execute' );
EXEC Dbms_Java.Grant_Permission('[Oracle username]', 'SYS:java.io.FilePermission','/bin/ls', 'execute' );
EXEC Dbms_Java.Grant_Permission('[Oracle username]', 'SYS:java.io.FilePermission','/bin/cp', 'execute' );
EXEC Dbms_Java.Grant_Permission('[Oracle username]', 'SYS:java.io.FilePermission','/bin/mv', 'execute' );
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17043956
A simple OS batch file might do it..

let me know which os you are on and I can help out...
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17044094
To actonwang:

No, a PL\SQL procedure cannot directly call a batch file on Windows, or a shell script on UNIX or Linux.  Maybe the business problem can be solved though by a batch file or shell script being run from an O/S scheduler, instead of from PL\SQL.
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 19

Expert Comment

by:actonwang
ID: 17044106
to markgeer:

       I am trying to answer the original question and try to execute the script file NOT pl/sql.
       look at the original question...


acton
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 500 total points
ID: 17044672
The question title: "Calling shell script from Oracle" could include calling a shell script from a *.SQL file being run in SQL*Plus, and SQL*Plus can support "host" commands, but the text of the question specifically says:  "We need to call a UNIX shell script from an oracle 9i stored procedure".  An Oracle stored procedure is PL\SQL, and PL\SQL cannot call "host" commands (like running a shell scripts) directly.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17044929
markgeer:

       I am messed up with other question. Ignore me on this :)

Acton
0
 

Author Comment

by:suhinrasheed
ID: 17048026
Hi Folks.Please make sure that if you can suggest a method with an example which doesnt involve Java or ProC,IT WOULD BE BEST.dbms_pipe package...can we use this and achieve the requirement.give me some inputs with an eg if possible
0
 
LVL 19

Expert Comment

by:actonwang
ID: 17048228
An external procedure, also sometimes referred to as an external routine, is a procedure stored in a dynamic link library (DLL), or libunit in the case of a Java class method. You register the procedure with the base language, and then call it to perform special-purpose processing.

if you want to use external procedure from oracle, it is unavoidable to use java or ProC/C because that it is what it is.

as a lot of links and examples above, this is not very difficult to do.

0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17050223
In Oracle7 and Oracle8, we used a combination of DBMS_PIPE and a utility that Oracle used to include with the database software, named "daemon".  That can work in Oracle9 too if you can find the source code for that "daemon" utility and compile it for your O/S.  The source code used to be in the Oracle documentation for Supplied PL\SQL Packages, under DBMS_PIPE.  Then you have to start that "daemon" process on your server (and keep it running all the time).  It establishes a connection to Oracle (using the SCOTT/TIGER login unless you change that in the source code before you compile it) and waits for messages to be sent to it via DBMS_PIPE.  When it receives a message (which must be a text string that is a valid command in your server O/S) it executes that text string, and returns "success" to PL\SQL.  Note that "success" gets returned regardless of whether the actual O/S command succeeded or failed.
0
 

Author Comment

by:suhinrasheed
ID: 17056114
markgeer

Can you just give me an example of this dbms_pipe method.Show me how you execute a small shellscript using this method.Shell script should be called from a Procedure.If you could show me this.full marks to you
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17056243
DBMS_PIPE allows asynchronous messaging between two or more sessions in the same instance. The data sent using dbms_pipe can be read only by another Oracle session. An OS program or a shell script can connect to Oracle database and receive data.

http://www.oracle-base.com/articles/misc/dbms_pipe.php
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 17058250
If you have access to Oracle8 documentation, the use of DBMS_PIPE plus the "daemon" executable was documented there in the Supplied PL\SQL Packages section.  I'm not sure if it is in the Oracle9 documentation or not.  An example from me is not enough.  You also need the "daemon" executable compiled for your O/S and running on your server.  I've never had that executable for UNIX - I just had it for Windows.

Do you have that executable?

Are you aware that the DBMS_PIPE-based way to execute "host" commands from PL\SQL was what Oracle supported for Oracle7 and 8.0, but starting with Oracle8.1, Oracle strongly promotes the use of Java instead.  I'm no big fan of Java, but we did switch to a Java-based way to do "host" commands from PL\SQL when we upgraded to Oracle9 some years ago.  (The DBMS_PIPE/daemon approach did also work for us with Oracle9 on WIndows.)

Is there a reason you do not want to use the Java-based method that Oracle encourages for Oracle9?
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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

709 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

13 Experts available now in Live!

Get 1:1 Help Now