RE: Calling O/S Scripts From PL/SQL

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Scott Canaan Sent: Monday, August 14, 2006 10:02 AM
To: oracle-l_at_freelists.org
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_at_rit.edu)

(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.

--
http://www.freelists.org/webpage/oracle-l