Run an OS command from within PL/SQL

Steven Feuerstein — Fri, 27 Mar 2009 13:14:00 GMT

Developers are hard people to satisfy. Oracle gives us this incredibly robust, powerful and easy to use database programming language – and all we can do is complain about what it doesn't do for us.

Well, that's reality for you: PL/SQL is powerful and robust and easy to use, but also very narrowly focused. So if you want it to do something outside of its area of expertise, sometimes you have to jump through a few more hoops than you'd like.

A good example of this is figuring out how to run an OS command from a PL/SQL block. There is not, unfortunately, a native "execute host command" statement within PL/SQL . Instead, Oracle offers (at least) three ways to do this:

Use the DBMS_SCHEDULER package
Run a Java method to execute your command.
Run a C program to execute your command.

DBMS_SCHEDULER offers the simplest and most intuitive solution, especially from the standpoint of DBA and system administration tasks. I talk about this solution on the OTN Best Practice page:

http://apex.oracle.com/pls/otn/f?p=2853:4:1544539351000407::NO::P4_QA_ID:16282

I will, in this Toad World blog, offer brief guidance on the other two options. It is very unlikely that you will be able to rely solely on the content below to implement either approach. Instead, I suggest you use this as a starting point and then from there visit the detailed Oracle documentation and/or the full chapter coverage of both these techniques in my Oracle PL/SQL Programming.

Executing an OS command with Java

With the Java approach, you will take these steps:

Find the Java class that implements host command execution.
Build a class that invokes that host command method. Let's call it HostCommand.
Build a PL/SQL program that calls a method in HostCommand to run your command.
Acquire the privileges needed to execute host commands via Java in the database.

Let's go through each of these steps.

Find the Java class that implements host command execution.

Objects of the java.lang.Runtime class include an exec method that will execute a host command.
Build a class that invokes that host command method. Let's call it HostCommand.

Here's code to create a new Java class in the database to invoke this command for the Windows XP operating system:

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "UTLcmd"
AS import java.lang.Runtime;

public class execHostCommand
{
public static void execute (String command)
    throws java.io.IOException
{
   String osName = System.getProperty("os.name");
   if(osName.equals("Windows XP"))
       command = "cmd /c " + command;
   Runtime rt = java.lang.Runtime.getRuntime();
   rt.exec(command);
}
}
/

You can easily modify the execute method to support other operating systems based on the value of osName.
Build a PL/SQL program that calls a method in HostCommand to run your command.

CREATE OR REPLACE PACKAGE host_command IS
PROCEDURE execute (cmd IN VARCHAR2) AS LANGUAGE JAVA NAME
'execHostCommand.execute(java.lang.String)';
END;
/

Notice that I "map" the VARCHAR2 datatype to the java.lang.String class in my call to the new Java method.
Acquire the privileges needed to execute host commands via Java in the database.

You need special privileges to execute host commands from within the database through Java. Otherwise when you try to execute your command you will see an error like this (the error message will vary depending on what you are trying to do):

One way to obtain these privileges to have the JAVASYSPRIV role granted to your schema. This role contains all the privileges you need (and more).

For a more nuanced approach to granting the required privileges, you can also use the Java security API available in the Oracle database. For example, if I want to delete a file using a host command (perhaps it is not accessible through UTL_FILE), I will need to grant the following privileges to the schema in which the command is executed, such as HR:

BEGIN
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.io.FilePermission'
                             , '<>'
                             , 'execute'
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'writeFileDescriptor'
                             , ''
                              );
   DBMS_JAVA.grant_permission ('HR'
                             , 'SYS:java.lang.RuntimePermission'
                             , 'readFileDescriptor'
                             , ''
                              );
END;
/

Executing an OS command with C

To use C, you must define an external procedure and then invoke it within your PL/SQL block. It is not possible within this article to cover completely all the steps and issues involved in setting up such an external procedure. I will, instead, cover the highlights. For the full details, check out Oracle documentation at:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_extern_proc.htm#ADFNS010

and also read Chapter 27 of Oracle PL/SQL Programming, 4^th edition, in which my co-author Bill Pribyl thoroughly explains external procedures.

As with Java, you will need help your database administrator to supply the privileges needed to execute your host command in C.

Here are the steps to follow with C:

Find (or build) the C program that implements host command execution.
Save the C source to a file and generate a shared library for it.
Save the library file where Oracle can find it.
Define a library inside Oracle that is associated with the shared library on disk.
Create a PL/SQL wrapper for the C function.

Let's go through each of these steps.

Find (or build) the C program that implements host command execution.

The C system function executes an operating system command. So I build a simple C function, extprocsh(), that accepts a string and passes it to the system function for execution:

int extprocsh(char *cmd)
{
return system(cmd);
}

The function returns the result code as provided by system, a function normally found in the C runtime library (libc) on Unix, or in msvcrt.dll on Microsoft platforms.
Save the C source to a file and generate a shared library for it.

I save the source code in a file named extprocsh.c. I then use the GNU C compiler to generate a shared library. On a 64-bit Solaris machine running GCC 3.4.2 and Oracle Database 10g Release 2, the following compiler command worked to create a shared library:

gcc -m64 extprocsh.c -fPIC -G -o extprocsh.so

Similarly, on Microsoft Windows XP Pro running GCC 3.2.3 from Minimal GNU for Windows (MinGW), also with Oracle Database 10g Release 2, this works:

c:\MinGW\bin\gcc extprocsh.c -shared -o extprocsh.dll
Save the library file where Oracle can find it.

These commands generate a shared library file, extprocsh.so or extprocsh.dll. Now I need to put the library file somewhere that Oracle can find it. The default locations for Windows and Unix respectively are:

$ORACLE_HOME/bin
$ORACLE_HOME/lib

If you want to use a non-default location, you will need to edit the listener configuration file and supply path value(s) for the ENVS="EXTPROC_DLLS...".
Define a library inside Oracle that is associated with the shared library on disk.

After copying the file and/or making adjustments to the listener, you will then define a "library" inside Oracle to point to the DLL. For example:

CREATE OR REPLACE LIBRARY extprocshell_lib
   AS '/u01/app/oracle/local/lib/extprocsh.so';   -- Unix

CREATE OR REPLACE LIBRARY extprocshell_lib
   AS 'c:\oracle\local\lib\extprocsh.dll';      -- Microsoft

Note: performing this step requires Oracle’s CREATE LIBRARY privilege.
Create a PL/SQL wrapper for the C function.

Now I can create a PL/SQL call specification which uses the newly created library:

CREATE OR REPLACE FUNCTION exec_host_command (cmd IN VARCHAR2)
   RETURN PLS_INTEGER
AS
   LANGUAGE C
   LIBRARY extprocshell_lib
   NAME "extprocsh"
   PARAMETERS (cmd STRING, RETURN INT);
Then, assuming that your DBA has set up the system environment to support external procedures, the exec_host_command function can now be called anywhere you can invoke a PL/SQL function.

Note that these operating system commands will execute with the same privileges as the Oracle Net listener that spawns the extproc process.

Steven Feuerstein's Blog

Run an OS command from within PL/SQL