Xref: alice comp.databases.oracle.misc:23718
Path: alice!news-feed.fnsi.net!newsfeed.wli.net!su-news-hub1.bbnplanet.com!su-news-feed1.bbnplanet.com!news.gtei.net!inet16.us.oracle.com!not-for-mail
From: tkyte@us.oracle.com (Thomas Kyte)
Newsgroups: comp.databases.oracle.misc
Subject: Re: Running an executable from a trigger
Date: Sat, 12 Dec 1998 16:11:56 GMT
Organization: Oracle Service Industries
Lines: 136
Message-ID: <36739490.1752560@192.86.155.100>
References: <1Cfc2.33$vu2.2103@typhoon.nycap.rr.com> <3672be67.175111515@nntp.sprintisp.com>
Reply-To: tkyte@us.oracle.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Newsreader: Forte Agent 1.5/32.451

A copy of this was sent to david.swanson@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@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@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.