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.

Comments for How to run an executable from an Oracle Trigger

Average Rating starstarstarstarstar

Click here to add your own comments

Aug 24, 2016
External Procedures
by: John

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 exact 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
    EXECUTE :a := 1;
    CALL call_my_java_proc(:a) INTO :b;
    PRINT b

For more details on using external procedures see Chapter 18 "Developing Applications with Multiple Programming Languages" of the Oracle Database Development Guide. Also Steven Feuerstein and Bill Pribyl have a whole chapter on external procedures in their book: "Oracle PL/SQL Programming".

Having described the use of external procedures as one way of "calling out" from the database, it may be easier to "call in" to the database from an external program written in C/C++ with Pro*C/C++, Java with JDBC or SQLJ, COBOL with Pro*COBOL, Visual Basic with Oracle Provider for OLE DB, .NET with Oracle Data Provider (ODP) for .NET.

You can also store procedures written in PL/SQL, Java or .NET in the database.

For more help with Pl/SQL see our Oracle PL/SQL tutorials.

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.

Aug 20, 2015
Useful tips

Great article. Thanks for sharing such useful and interesting tips.

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.