OS commands in Oracle using Kernel32.dll

First of all: watch out! You should be very carefull implementing this. Use grants appropriately.

There are several ways to implement OS command in Oracle. I’m using this “trick” for several years now and it works like a charm. Even on older versions of Oracle on Windows.

1. Create a library to the kernel32.dll

create library kernel32 
as '%SystemRoot%\SYSTEM32\KERNEL32.DLL';

2. Create a package with the following function:

FUNCTION RUN
  ( cmd IN VARCHAR2
  , show IN BINARY_INTEGER DEFAULT 0
  ) RETURN BINARY_INTEGER
IS 
  External Library Kernel32
  Name "WinExec"
  Language C
;

3. Add some API you need in the package. e.g. to delete a set of files:

PROCEDURE DEL
  ( P_FILES IN VARCHAR2
  )
IS
  l_dum BINARY_INTEGER;
BEGIN
  l_dum:= RUN('cmd.exe /c del /Q '||P_FILES ,0);
END;

or move

PROCEDURE MOVE
  ( P_from IN VARCHAR2
  , P_to IN VARCHAR2
  )
IS
  l_dum BINARY_INTEGER;
BEGIN
  l_dum:= RUN('cmd.exe /c move "'||p_from||'" "'||p_to||'"',0);
END;

You can imagine the possibilities are endless. We have used it for mailing, ftp and zipping.

4. Alter the listener.ora to include extproc

LISTENER = ( ADRESS_LIST = (.... Your database ....)
                           (PROTOCOL=IPC)(KEY=EXTPROC0))
           )
SID_LIST_LISTENER=(SID_LIST =
                  ( SID_DESC=(SID_NAME=PLSExtProc)
                             (ORACLE_HOME=...)
                             (PROGRAM=extproc)
                             (ENVS="EXTPROC_DLLS="ANY")
                  )
                  ( .... your database....) )

5. Alter the tnsnames.ora on the database server to include:

 EXTPROC_CONNECTION_DATA=
  (DESCRIPTION=(ADRESS=(PROTOCOL=IPC)
                       (KEY=EXTPROC0)
               )
  (CONNECT_DATA=(SID=PLSExtproc)
                (PRESENTATION=RO)))
Advertisements

3 thoughts on “OS commands in Oracle using Kernel32.dll

  1. We did some test with that. You can redirect stderr and stdout to a file.
    e.g. cmd /c dir 1>stdout.txt 2>stderr.txt
    With that, the only “problem” you’re faced with is where to look for those files and read them with e.g. utl_file
    But that “problem” isn’t that hard to solve.

    If you want stderr redirected to stdout in just one file do:
    cmd /c dirx >edwin.txt 2>&1

  2. Pingback: Kernel32.dll with Oracle 12c | emoracle

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s