I have played with another technique...
The PL/SQL trigger writes something into an Oracle Pipe (using the DBMS_PIPE
package). I then created a PL/SQL procedure that knows how to read the same
pipe. From a perl script I can call this procedure and check the contents
of the pipe. If there is anything there, you can parse the stream and then
process as necessary.
It does work an in concept will do what you want. However I have not really
stress tested such a system to see whether it will work in a heavily loaded
environment.
Neil McLennan
-----Original Message-----
From: Anna Fong [mailto:[EMAIL PROTECTED]]
Sent: March 28, 2001 4:31 PM
To: Steve Sapovits; [EMAIL PROTECTED]
Subject: RE: triggering a script after insert/update
Thanks so much for your reply Steve!
The first link (faqplsql.htm) you sent along seems to be the most promising
when I looked at the example
(http://www.orafaqcom/scripts/c_src/extproc.txt). A couple of the other
links seem outdated (288.htm, 267.htm) and much more clunky to implement.
Another link (3864.htm) just reiterates what is in extproc.txt but in less
specific detail. The info in the remaining link (2302.htm) is not too
useful and is already covered by extproc.txt.
The complete text from extproc.txt is below:
/*--------------------------------------------------------------------
* extproc.c
*
* Call operating system commands from PL/SQL using the External
* Procedure Interface.
*
* Frank Naude - Dec 2000
*--------------------------------------------------------------------
* Setup instructions:
*
* 1. Compile this program: cc -G extproc.c -o extproc.so (on Unix)
* 2. Run $ORACLE_HOME/bin/extproc to ensure it is executable
* 3. Define this TNSNAMES.ORA entry (Use the correct domain):
* EXTPROC_CONNECTION_DATA.WORLD = (DESCRIPTION =
* (ADDRESS=(PROTOCOL=IPC)(KEY=extproc))
* (CONNECT_DATA=(SID=extproc)))
* 4. Define this LISTENER.ORA entry:
* EXTERNAL_PROCEDURE_LISTENER =
* (ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=extproc)))
* SID_LIST_EXTERNAL_PROCEDURE_LISTENER =
* (SID_LIST=(SID_DESC=(SID_NAME=extproc)
* (ORACLE_HOME=/app/oracle/product.8.1.7)(PROGRAM=extproc)))
* 5. Start the new listener: lsnrctl start EXTERNAL_PROCEDURE_LISTENER
* 6. SQL> create library shell_lib as '/app/oracle/local/extproc.so';
* /
* 7. SQL> create or replace function sysrun (syscomm in varchar2)
* return binary_integer
* as language C -- Use "as external" for older Oracle releases
* name "sysrun"
* library shell_lib
* parameters(syscomm string);
* /
* 8. Execute an OS command from PL/SQL:
* PL/SQL> declare
* rc number;
* begin
* rc := sysrun('/bin/ls -l');
* dbms_output.put_line('Return Code='||rc);
* end;
* /
*
*--------------------------------------------------------------------
* Notes:
*
* 1. When running shell-scripts, very few environment variables will be
* defined (as with cron jobs). Remember to set everything
* explicitly. Ie. $PATH, etc.
* 2. Rewrite this program using C Piping if you need to capture command
* output. Look at the popen (pipe open) function.
* 3. In addition to this, you can also try to make the external
* procedure example as provided by Oracle:
* $ cd $ORACLE_HOME/plsql/demo
* $ make -f demo_plsql.mk extproc.so
*
*--------------------------------------------------------------------
*/
int sysrun(char *command)
{
return system(command);
}
----------------------------------
Thanks again,
Anna
At 06:38 PM 03/27/2001 -0500, you wrote:
>
>Maybe I prove myself dumb ...
>
>It appears to be possible, although perhaps not built in or
>easy. Here's are some threads from www.orafaq.org that explain
>how to do it:
>
>http://www.orafaq.com/faqplsql.htm (a little over halfway down)
>
>http://www.orafaq.com/msgboard/sql/messages/288.htm
>
>http://www.orafaq.com/msgboard/server/messages/2302.htm
>
>http://www.orafaq.com/msgboard/plsql/messages/267.htm
>
>http://www.orafaq.com/msgboard/sql/messages/3864.htm
>
>> -----Original Message-----
>> From: Steve Sapovits
>> Sent: Tuesday, March 27, 2001 6:21 PM
>> To: 'Anna Fong'; [EMAIL PROTECTED]
>> Subject: RE: triggering a script after insert/update
>>
>>
>>
>> Oracle triggers are tied to PL/SQL code -- not external scripts.
>> The PL/SQL a trigger invokes is also limited a bit: no commits or
>> rollbacks and some limits on the data types it uses for variables.
>>
>> Someone can prove me dumb here but I don't think this is possible.
>>
>> Where's the triggering SQL being executed from?
>>
>> > -----Original Message-----
>> > From: Anna Fong [mailto:[EMAIL PROTECTED]]
>> > Sent: Tuesday, March 27, 2001 6:26 PM
>> > To: [EMAIL PROTECTED]
>> > Subject: OT: triggering a script after insert/update
>> >
>> >
>> > Hi,
>> >
>> > I'm looking for an example of how to trigger a script (Perl, shell,
>> > whatever...) after an insert/update into a table. I've
>> seen database
>> > triggers used to replicate data to other databases, but how
>> > do I trigger an
>> > external script?
>> >
>> > Currently, I have a bunch of Perl scripts that create reports
>> > in HTML from
>> > the database. Each time an insert/update is done, the
>> > scripts need to be
>> > executed in order to update the report. Right now, we're
>> > just manually
>> > running the scripts. I'd like to automate it so that the
>> > execution of the
>> > scripts is triggered from the insert/update in the
>> database. Is this
>> > possible?
>> >
>> > Database: Oracle 8.0.3
>> > Platform: HP-UX 10.20
>> >
>> >
>> > Thanks in advance for you reply,
>> > Anna
>> >
--------------------------------------------------------
Anna Q. Fong, Webmaster
California Data Exchange Center
