Re: Start a program from wthin Oracle

Moritz,

Interesting how minds end up needing the same thing about the same time. Last week, I posted a message about needing to call a program (e.g. do command line stuff) from a PL/SQL procedure.

PLEASE let us know what you end up using and how it works.

I successfully used the external procedure but found some problems along the way. I documented these problems in the comp.database.oracle message thread. Here is basically what I found:

FYI, it doesn't work (as shipped/written in Oracle 9i) for Oracle 9i. Simple enough to fix the multiple problems, but strange Oracle would ship it without testing it (I could see release 1 but release 2?).

For those interested in using it. You will run into some problems with the extern.sql file, as written.

1. It requires server manager (SVRMGRL), which Oracle has been going out of the way to tell us no longer exists in 9i. Here Oracle is using svrmgrl in Oracle 9i.
2. The "/" to end various commands (e.g. create library, create or replace procedure) don't work in the present positions. Moving them immediately after the command fixes the problem.
3. You'll get the error message ORA-28595, unless you:
4. Update the DLL location.
5. Place the DLL file in $ORACLE_HOME/bin for Windows or $ORACLE_HOME/lib for Unix systems ii) Update the sql script to point to $ORACLE_HOME/bin for Windows or $ORACLE_HOME/lib for Unix
6. Update the environment variable EXTPROC_DLL. The value for this environment variable is a list of colon separated fully qualified DLL names. There is addition stuff about security (ONLY and ANY) and it is RTFM time.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"Moritz Klein" <mklein_at_students.uni-mainz.de> wrote in message news:auljlm$4jj$1_at_bambi.zdv.Uni-Mainz.DE...
> Hello NG,
> first of all the general information.
> I am using Windows 2000 Server and Oracle 9.2.
> I am wondering if it is possible in PL/SQL to start programs (like
> batch-scripts, perl-scripts an so on). I am designing an usermanagement
with
> Oracle and DHCP/DNS. Our administrators use a webinterface to enter the
> user-information into the Oracle-DB. First we scheduled our scripts to run
> every hour but this brought some problems and i want the scripts to be run
> after every insert, update, or delete. If anyone does have a better
solution
> i would be very thankful if told here.
>
> Thanks in advance
> Moritz Klein
>
>
Received on Sun Dec 29 2002 - 16:51:04 CST