RE: Calling O/S Scripts From PL/SQL

From: "Reidy, Ron" <Ron.Reidy@xxxxxxxxxxxxxxxxxx>

To: <srcdco@xxxxxxx>, <oracle-l@xxxxxxxxxxxxx>

Date: Mon, 14 Aug 2006 11:08:17 -0600

Scott,
Why are the triggers invalid?
Calling SQL*Plus from a extproc seems like overkill. Why not put these
things into dbms_scheduler and run them from there? In any case, you
could call SQL*Plus from C using the 'system()' function. However, this
is extremely insecure and could introduce really bad things into your
environment. Sounds like redesign time to me.
rr
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Scott Canaan
Sent: Monday, August 14, 2006 10:02 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Calling O/S Scripts From PL/SQL
We have an issue where we need to drop and recreate tables, indexes,
triggers, stored procedures, etc. The programmer involved has contacted
me because they have to do this via a stored procedure. The reason is
that the tool they are using (Synopsis) isn't easily able to call O/S
scripts. The problem they are having is in creating the triggers. The
triggers get created (via an "execute immediate" statement), but are
invalid and the stored procedure returns an error (ora-24344 success
with compilation error).
My suggestion was to keep the original SQL*Plus scripts and call them
from the stored procedure. The problem is, I can't figure out how to do
that. I've found documentation on how to call C and JAVA external
programs, but not SQL*Plus scripts. I'm sure it can be done, even if
there's a way to call an O/S program (sqlplus) from the stored
procedure, but I can't figure it out and time is running out.
Here are the specifics:
Oracle 10.2.0.2
Sun Solaris 10
Thank you,
Scott Canaan '88 (Scott.Canaan@xxxxxxx)
(585) 475-7886
"Life is like a sewer, what you get out of it depends on what you put
into it." - Tom Lehrer.
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is
intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.