topic Re: Pl Sql question in Operating System - HP-UX

Pl Sql question

BGiulio — Tue, 16 Jul 2002 08:35:31 GMT

Hello crew,

my need is to launch a unix script (on the machine) from the inside of an oracle plsql procedure, in other words the procedure create a file and it should be thereafter sent via ftp: how can I launch the sending from there?

thanks already!

Re: Pl Sql question

harry d brown jr — Tue, 16 Jul 2002 10:00:06 GMT

Can one execute an operating system command from PL/SQL?
There is no direct way to execute operating system commands from PL/SQL in Oracle7. However, one can write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For an Pro*C example, see chapter 8 of the Oracle Application Developers Guide.
In Oracle8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One just write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.

http://www.orafaq.com/faqplsql.htm#OSCOMMAND

If you really want this, then maybe you should write your code in perl, and have perl call sql, giving you full control of your process.

live free or die
harry

Re: Pl Sql question

Tom Geudens — Tue, 16 Jul 2002 10:13:55 GMT

Hi,
Yes, this is possible (from Oracle 8.0.x and onwards) to execute an operating system command from within Oracle. In fact I answered this question before. See the following thread :
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0xcac1a2db8513d6118ff40090279cd0f9,00.html

Hope it helps,
Tom

Re: Pl Sql question

R. Allan Hicks — Wed, 17 Jul 2002 03:39:03 GMT

One thing to remember when designing an interface to HP-UX for running scripts and other process is that the process runs as the Oracle user.

One of the folks in the group wanted a stored procedure that would accept an script name and run the shell. This procedure has to be restricted so that only trusted users can run it. A user can easily pass the script dbshut to the stored procedure and the Oracle database will come tumbling down.

To maintain a healthy system you must restrict the stored procedure that you create as the interface (granting execute permission to only trusted users) or restrict what it can do.

-Good Luck

Re: Pl Sql question

Brian Bientz — Wed, 17 Jul 2002 10:59:03 GMT

If you are running Oracle 8i or upwards, then I think you can also accomplish this using a JAVA stored procedure that does a system call.

Haven't tried it, but I think it should work.