Re: Running an executable from a trigger

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 12 Dec 1998 16:11:56 GMT
Message-ID: <36739490.1752560@192.86.155.100>

A copy of this was sent to david.swanson_at_sprintranet.com (Power User) (if that email address didn't require changing) On Sat, 12 Dec 1998 00:56:22 GMT, you wrote:

>The only way I've ever seen this done is VIA the dbms_pipe package...
>
>You would write your PL/SQL procedure to insert a value into a
>specific pipe in the oracle database and then have a program probably
>running in the background on your NT machine that monitors the Oracle
>pipe and when it gets a message in the pipe, acts on it... One of the
>actions could be to execute an external program...
>
>Oracle itself does not seem to have this sort of functionality built
>into the database...
>

In 8 it does.

In fact, since they are on NT and NT has the requisite .DLL for running a program, you might be able to do this very easily (without writing C). You can try this (and if this doesn't work 100% for you, I would suggest writing a small C wrapper program that runs your program in any way shape or form you want and calling that from pl/sql).

This little stub just sets up pl/sql to be able to call the windows WinExec function. WinExec will start another program. You'll have to look up the windows sdk docs to find more about WinExec() itself.

---

drop library demolib;

CREATE LIBRARY demolib as 'c:\winnt\system32\kernel32.dll' /
show errors

CREATE OR REPLACE PACKAGE Demo IS
  function run(lpCmdLine IN varchar2,

               nCmdShow IN binary_integer) return binary_integer; end;
/
show errors

CREATE OR REPLACE PACKAGE BODY Demo IS

function run(lpCmdLine IN varchar2,

             nCmdShow IN binary_integer) return binary_integer   AS EXTERNAL
  NAME "WinExec"
  LIBRARY demolib
  parameters ( lpCmdLine STRING,

                nCmdShow    long );

end;
/
show errors

---

After you install that (or before) you need to make sure the EXTPROC facility is configured on your server. I'm including my listener.ora and tnsnames.ora which demonstrate the correct settings for EXTPROC services. You'll need to restart the listener after updating your listener.ora file.

• tnsnames.ora entry --------------------------- extproc_connection_data= (description= (address= (protocol=IPC) (key=extproc) ) (connect_data= (sid=extproc) ) )
• tnsnames.ora entry ---------------------------
• sample listener.ora -------------------------- LISTENER = (ADDRESS_LIST= (ADDRESS= (PROTOCOL=IPC) (key=extproc) ) (ADDRESS= (PROTOCOL=TCP) (Host=localhost) (Port= 1521) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME= c:\orant) (SID_NAME = ORC1) ) (SID_DESC = (SID_NAME = extproc) (ORACLE_HOME = c:\orant) (PROGRAM = extproc) ) ) STARTUP_WAIT_TIME_LISTENER = 0 CONNECT_TIMEOUT_LISTENER = 10 TRACE_LEVEL_LISTENER = OFF
• sample listener.ora --------------------------

>
>On Fri, 11 Dec 1998 20:52:13 GMT, "Dave's News"
><stimpson_at_visionarysoftwareSPAM.com> wrote:
>
>>We would like to run a Windows Executable (VC++ app) from a trigger within
>>Oracle. Does anyone have an idea on how we could do this?
>>
>>We have Oracle 8.0.4 for NT.
>>
>>Thanks in advance.
>>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Dec 12 1998 - 10:11:56 CST