Re: Execute shell script unix from stored procedure

nanduri_at_hotmail.com wrote:

> There is a way to do it and it is not all that simple and not sure if this
> solves your requirement. Its skeleton logic goes as follows
> 1. Write a PRO*C program to be run as a daemon server process which has
> calls to oracle supplied DBMS_PIPE package (which can send and receive
> messages between two Oracle sessions. the daemon process has to be in
> msg read mode to serve the incoming requests)
> 2. Call the same DBMS_PIPE package from a stored procedure with appropriate
> parameters such as the pathname of the script and if the script requires
> any parameters etc.,
>
> Hope this helps.
> Nanduri
>
> > Hi.
> > How can I execute a shell script unix from a stored procedure?
> >

There's another way - at least in UNIX:

First a quote from the Oracle docs ('Oracle7 Server Application Developer's Guide'):

"PL/SQL File I/O

The release 7.3 Oracle Server adds file input/output capabilities to PL/SQL. This is done through the supplied package UTL_FILE.

The file I/O capabilities are similar to those of the standard operating system stream file I/O (OPEN, GET, PUT, CLOSE), with some limitations. For example, you call the FOPEN function to return a file handle, which you then use in
subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When you are done performing I/O on the file, you call FCLOSE to complete any output and to free any resources associated with the file."

I have some times used a neat little feature in UNIX called named pipes - unlike named pipes in OS/2 and NT, these are created as 'special files' in the filesystem

which means that 1) you can see them in directory listings and 2) they are permanent. The command to make them is usually called mkfifo. When a program - eg a shell script or a PL/SQL procedure - opens a named pipe and writes to it, the process 'hangs' until another process opens and reads. This is very useful

• I once used it to make it possible to execute remote procedures via ftp:

On the serving side I had a shell script reading from a pipe - depending on what it read, it would do different things. On the client side I would transfer a file containing eg. a short text that the 'server script' would interpret. It's so
simple that one almost weeps...

/jan Received on Thu Sep 17 1998 - 02:41:17 CDT