After-Insert-Trigger must call external application
Dieter Ratsch
is it possible, to start an external application on the DB-Server after
inserting a row???
Example:
I insert a row in a table. After inserting the row I would like to start
PHP.EXE or on our NT-Web-/DB-Server.
I've read and tried out several hours to build triggers and
PL/SQL-Procedures but I'm not able to get access to the OS to start
external programs. The HOST-command is only available in SQL*PLUS - not
in PL/SQL - or am i wrong?
Any hints for a bloody beginner????
best regards
Dieter (mailto:k...@epost.de)
Sybrand Bakker
I would consider starting up php on after-insert an extremely bad idea
and a sign of an application hold together by ropes and sellotape.
Brrr... Are you really sure you *have* to use php and you can't do it
in ordinary pl/sql. I would strongly advise you to reconsider.
That said: yes it can be done. Oracle comes with an external procedure
facility (which you can look up in the doco) and for which the
listener has to be configured. Just be aware, obviously, those
external procedures run *on the server*, *NOT* on the client.
Hth
Sybrand Bakker, Senior Oracle DBA
To reply remove -verwijderdit from my e-mail address
Dieter Ratsch
> I would consider starting up php on after-insert an extremely bad idea
> and a sign of an application hold together by ropes and sellotape.
> Brrr... Are you really sure you *have* to use php and you can't do it
> in ordinary pl/sql. I would strongly advise you to reconsider.
>
> That said: yes it can be done. Oracle comes with an external procedure
> facility (which you can look up in the doco) and for which the
> listener has to be configured. Just be aware, obviously, those
> external procedures run *on the server*, *NOT* on the client.
> Hth
> Sybrand Bakker, Senior Oracle DBA
way, but i don't know a solution for my problem with PL/SQL or other
Oracle-Tools:
PHP reads out the BLOB's in the inserted (and some other) records, which
contains uploaded i.e. Word-, Excel- and Powerpoint-Docs, text-fields
and other informations and generates in a very complex way
1 PDF-Document for a catalog. This is already done and the results are
nearly perfect!
I will use PL/SQL as soon MS-Office can generate XML-output in a usable
quality and not in MS-XML(!) and hope, this feature is available soon!!!
Sure - the external procedure must run on the server, that's what I'm
looking for!
Regards (excuse my bad english - I'm still learning...)
Dieter Ratsch
Brian Peasland
table experiences a high volume of inserts, this trigger will fire many,
many times. And spawning the external process will only add to the
trigger execution time. It might be better to write a job which runs
every so often (1 hour?) and does what you need to the data. Just a
thought....
Aside from external routines, you could also use Java Stored Procedures.
HTH,
Brian
Adam Hapworth
that is an article at asktom.oracle.com in which you setup a java
stored procedure to run external commands. I am currently using it
for a few fun things and it works very well. All of the source is
there all You need to make sure of is that you have the JVM installed
on your oracle database.
Adam
Dieter Ratsch <dieter.n-o-s...@epost.de> wrote in message news:<appiub$77l$03$1...@news.t-online.com>...
Dieter Ratsch
> http://asktom.oracle.com/pls/ask/f?p=4950:8:1529329::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:952229840241,%7Bjava%7D%20and%20%7Bunix%7D
> that is an article at asktom.oracle.com in which you setup a java
> stored procedure to run external commands. I am currently using it
> for a few fun things and it works very well. All of the source is
> there all You need to make sure of is that you have the JVM installed
> on your oracle database.
Thanks Adam,
that seems to be an easy way and JVM is installed.
My application will not fire the trigger more than once per
10 minutes (i think so), so IMHO the STP looks interesting.
On the other hand a scheduled job (thanks Brian Reasland))
is more OS-independent but must start up an application
very often to see, if there is something to do.
regards
Dieter
Michael Kuznetsov
One of the way to execute external program code after database trigger
fired is to use dbms_alert package. I like this way because database
well separated from external application. And external application
may be of any type on any languge.
To do this you should:
1. Add to database trigger procedure dbms_alert.signal
2. external application execute dbms_alert.register
3. external procedure call dbms_alers.wait in infinitive loop and wait
for signal from database.
Regards,
Michael
Brainbench MVP for Oracle Programming
http://www.brainbench.com
Dieter Ratsch <dieter.n-o-s...@epost.de> wrote in message news:<app6pj$4fg$00$1...@news.t-online.com>...