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)))