How to run an executable from an Oracle Trigger

by Sean


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.


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

  4. 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

For more details on using external procedures see Chapter 14 of the Oracle Database Advanced Application Developer's Guide. Also Steven Feuerstein and Bill Pribyl have a whole chapter on external procedures in their book: "Oracle PL/SQL Programming".

For more help with Pl/SQL see our .

If you're looking for more formal training on Oracle, why not take a training course with one of our partners? See our
Oracle training page for more details.

Comments for How to run an executable from an Oracle Trigger

Average Rating starstarstarstarstar

Click here to add your own comments

Aug 23, 2010
An alternative to external procedures
by: John

As an alternative to calling an external procedure form pl/sql, if you're running Oracle 11g, is to create (run) a job using dbms_scheduler which allows you to specify jobs that are executables.

The problem might be though that this job would be run asynchronously to the trigger, so if you need the job to finish before the trigger finishes then this obvioulsy wouldn't be suitable.

See the PL/SQL Packages and Types Reference for more details on dbms_scheduler.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.