Executing host command from PL/SQL

Jens Bloedorn

unread,

Aug 11, 1997, 3:00:00 AM8/11/97

to

I have to shedule some PL/SQL Procedures by dbms_job.
Within these procedures i have to copy and remove Files assigned to
Datasets.

Is it possible to execute host (unix) commands by PL/SQL.
e.g. cp or rm

Thanks

Jens

Chris Halioris

unread,

Aug 12, 1997, 3:00:00 AM8/12/97

to Jens Bloedorn

Yes, you need to use DBMS_PIPE. Pipe a message from your PL/SQL and
have another session always running that is listening for a message in
the pipe. It can be a shell script running a SQL*Plus script that
includes PL/SQL code. When the message is retrieved it spools some info
to a file and then ends. The shell script regains control and performs
some OS commands based on the info spooled to the file.

Chris Halioris

Thomas Kyte

unread,

Aug 12, 1997, 3:00:00 AM8/12/97

to

Heres how it would work....

Here is a PL/SQL subroutine you can install in your schema:

create or replace procedure host( cmd in varchar2 )
as
status number;
begin
dbms_pipe.pack_message( cmd );
status := dbms_pipe.send_message( 'HOST_PIPE' );
if ( status <> 0 ) then raise_application_error( -20001, 'Pipe error' );
end if;
end;
/

Here is a C-Shell script you can run in the background

-------------------- bof ----------------------------
#!/bin/csh -f

sqlplus tkyte/tkyte <<"EOF" | grep '^#' | sed 's/^.//' > tmp.csh

set serveroutput on

declare
status number;
command varchar2(255);
begin
status := dbms_pipe.receive_message( 'HOST_PIPE' );
if ( status <> 0 ) then
dbms_output.put_line( '#exit' );
else
dbms_pipe.unpack_message( command );
dbms_output.put_line( '##!/bin/csh -f' );
dbms_output.put_line( '#' || command );
dbms_output.put_line( '#exec host.csh' );
end if;
end;
/
spool off
"EOF"

chmod +x tmp.csh
exec tmp.csh
----------------------- EOF ---------------------------------

If you run this in the background (The script), you'll be able to have it
execute any host command you want. Run this in one window for example and in
anther window go into sql*plus and try:

SQL> exec host( 'ls -l' );
SQL> exec host( 'uptime' );
SQL> exec host( 'echo Hello World' );
SQL> exec host( 'exit' );

You'll see the output of ls -l, uptime, and echo happen on the other window
where the shell script is running (shows you a way to debug pl/sql routines, use
"host( echo some string )" and you'll get real time feedback from your pl/sql
procedure).....

Thomas Kyte
tk...@us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

Curtis Mack

unread,

Aug 13, 1997, 3:00:00 AM8/13/97

to

Jens Bloedorn wrote:
>
> I have to shedule some PL/SQL Procedures by dbms_job.
> Within these procedures i have to copy and remove Files assigned to
> Datasets.
>
> Is it possible to execute host (unix) commands by PL/SQL.
> e.g. cp or rm
>
> Thanks
>
> Jens

try the 'HOST' command followed buy your unix command.
--
Curtis E. Mack
Washington State Dept. of Social and Health Services
Research and Data Analysis
mac...@dshs.wa.gov

Jens Bloedorn

unread,

Aug 14, 1997, 3:00:00 AM8/14/97

to

Hi folks,
thanks a lot for your answers.

I have found a solution in the Oracle documentation
as you said.
It's a Pro*C program running on the server and listening for the
message in the pipe.
It works great.

Jens