Re: UNIX commands from within PL/SQL

You could also use the new dbms file utiltiy (UTL_FILE) in conjunction with a shell script (the pipe with pro*c is better if you know C).

Have your pl/sql generate the os command as a file with some predetermined naming convention that an external UNIX script would recognize. The UNIX script would run the command and then write the result to another file that can be read by the Oracle
file package.

Example:

Your shell queries are written to files named in the following format

    07031998120130.shqry [MMDDYYYYHHMMSS.sqhry]

The shell program ora_shell.ksh reads the .shqry file and "execs" the script and directs
the output into the file called

   07031998120130.shrlt

Then your plsql program reads the shrlt file.

NOTE: This could be a very asyncronous operation.

I still think the pro*C method would be better, but (again) if you don't know C then I think this is your solution.

BTW, your DBA will need to set parameters in the init.ora file to specify where Oracle can read or write files.

    utl_file_dir = /your_oracle_rw_directory

Hope this helps,

SCott

Piotr Kolodziej wrote:

> Bram Stieperaere wrote in message
> <35A0993F.7B3DF712_at_N_O_S_P_A_Msodexho-pass.be>...
> >Hi All,
> >
> >I need to have access to UNIX commands from within PL/SQL. I would like
> >to
> >- get a list of files in a directory and put it in another file (so
> >PL/SQL can process them one by one)
> >- move processed files to another directory
> >
> >I guess I need a PRO*C program that communicates with the PL/SQL program
> >via a pipe.
> >
> >Does anyone know how to do this?
> >
>
> For example, use dbms_pipe package.
> Very usefull to exchange information between independent Oracle
> sessions.
>
> HTH,
> Piotr Kolodziej
Received on Mon Jul 06 1998 - 12:15:13 CDT