How to run an executable from an Oracle Trigger

From: Sean - Hi, is it possible to run an executable file from an Oracle trigger? I haven't been able to find any documentation on how to do this - please help! Thanks in advance.


It's not possible to run a stand alone executable file directly from a trigger. However, it is possible to invoke "external procedures" from Oracle pl/sql and therefore from a trigger.

These external procedures can be written in C/C++ or Java and the overall process for accessing these procedures is dependent on the language but in general terms you load the external procedure into the Oracle database and then publish it so that it can be called from SQL and/or PL/SQL.

An external C/C++ procedure in this context is a shared library (e.g. ".DLL" on MS Windows or ".so" on Solaris) as opposed to an executable.

The steps that have to be taken to be able to call the external C/C++ procedure from your PL/SQL are as follows:

1. Build the shared library file (may contain more than one external procedure) for your operating system
2. Ensure Oracle knows where to find the library by issuing the CREATE LIBRARY command in SQL*Plus and specifying the full path to the library file (e.g. CREATE LIBRARY ext_lib AS 'C:\WINDOWS\system32\extprocs.dll'). You need to do this once for each library you intend to use, as opposed to once for each external procedure you wish to call.

1. Publish the procedure by creating a pl/sql wrapper to call the external procedure. This comprises the pl/sql function or procedure header for the body with the rest of the body replaced by the external procedure interface definition. For example:CREATE OR REPLACE PROCEDURE extproc1 (p1 PLS_INTEGER) IS LANGUAGE C LIBRARY ext_lib NAME "my_ext_proc" PARAMETERS (p1 int)
2. Call your Oracle pl/sql wrapper procedure from your trigger or other pl/sql routines or from SQL*Plus using the SQL CALL statement syntax - e.g. CALL my_ext_proc(400);

For an external Java procedure you have to

• load the Java class
• publish the Java procedure by creating an Oracle pl/sql wrapper e.g. CREATE OR REPLACE FUNCTION call_my_java_proc (N NUMBER) RETURN NUMBER AS LANGUAGE JAVA NAME 'myRoutines.myjavaproc(int) return int';
• Call it using the SQL CALL syntax 
  VARIABLE a NUMBER
  VARIABLE b NUMBER
  EXECUTE :a := 1;
  CALL call_my_java_proc(:a) INTO :b;
  PRINT b