Skip to Main Content
  • Questions
  • How do I execute an executable from PLSQL

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Shabbir .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: February 22, 2007 - 7:56 pm UTC

Version:

Viewed 10K+ times! This question is

You Asked

How do I execute an executable
from a PL/SQL code (Without using the
SQL*Plus "HOST" command)?



and we said...



In Oracle7 -- you cannot execute another program from PLSQL. You can 'talk' to an already running program (which in turn can execute another process). See </code> http://asktom.oracle.com/~tkyte/plex/index.html <code>for an idea on how this works -- we can use dbms_pipe to send messages to another session and have it do something for us.

In Oracle8 -- this feature is called an external procedure and is quite easy to implement. You would code your stored procedure in C, the C can use a system() command for example to run another process.

In Oracle8i, release 8.1 -- a java stored procedure can run external processes as well without having to code C.


In unix, you can do something like this as well (make sure you understand the security implications here -- run host.csh as a very Non-priveleged user!)

This is a quick and dirty daemon -- written in csh (the cool shell)..

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 (use this shell script make sure it is named host.csh)

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


Rating

  (28 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

alex, March 20, 2001 - 3:01 am UTC


A reader, March 25, 2001 - 6:51 am UTC


Shunan Xiang, May 11, 2001 - 12:40 am UTC


Hossam Eldin Emam, May 24, 2001 - 4:31 am UTC

thank than I need

Bloody marvelous

Andrew Fyfe, June 07, 2001 - 3:44 pm UTC

Solved my FTP problem, and has potential for hundreds of other uses!

you daaa maan!!!

Prakash, June 20, 2001 - 10:51 am UTC

This is cool and awesome and ....
Now I view my debug messages realtime!!!

MaxU, June 22, 2001 - 6:13 pm UTC


Why didn't Oracle document this years ago?

Colin Davies, August 23, 2001 - 12:49 pm UTC

Tom delivers again! This is SO useful! Up until now, I have used a Perl daemon listening to the pipe, but not all systems have Perl installed. Now I just have totranslate it into the Korn and Bourne shells...

A reader, September 17, 2001 - 11:46 am UTC


tom

ray, September 21, 2001 - 1:19 am UTC

I want to use a similar kind of process .

I have to select from dbms_jobs make this an executable. And from another session I want to check peridically if there are any broken jobs.

How can I do it , kindly give the answeri n steps , I had been looking for this functionality for a long time now, but ......

Sue, September 25, 2001 - 4:29 pm UTC

Great example of exactly what I'm trying to do. Very helpful.

Richard Lee, November 20, 2001 - 4:39 pm UTC

Could you please supply detail information on how to run under Windows NT + Oracle8i(8.1.5), like how to execute ".exe" from PL/SQL? Or where I can find related information?

Thanks
Richard


Totally off the point but worth a read

A reader, November 28, 2001 - 6:25 pm UTC

The famous csh bashing document...
</code> http://www.perl.com/language/versus/csh.whynot <code>

Excellent Tip

kaustubh, April 30, 2002 - 11:43 am UTC

The code in example works verbatim. Excellent tip that can be used for lot of useful things...

Excellent

Tom, June 03, 2002 - 1:56 pm UTC

Tom:

It's an excellent alternative for doing things. However, I received an error when I tried to compile the stored procedure to test out the process:

Processing ...
(1):PLS-00201: identifier 'SYS.DBMS_PIPE' must be declared
(2):PL/SQL: Statement ignored
(3):PLS-00201: identifier 'SYS.DBMS_PIPE' must be declared
(4):PL/SQL: Statement ignored

Procedure HOST compiled with errors; status INVALID

What do I have to do to fix the errors. Please help.

Thanks,
Tom

Tom Kyte
June 03, 2002 - 3:51 pm UTC

Great tip!

Lm, June 03, 2002 - 6:15 pm UTC

Another follow-up:

If I want to write all output messages to a log file in the Unix directory where the host.csh script is running, how would I modify the host.csh script? Please help since I am really new to Unix.

Thanks a lot.
Lm

Tom Kyte
June 03, 2002 - 6:27 pm UTC

well, "message" isn't known to the sh script -- it is only available in the PLSQL. So, I would use UTL_FILE to log that message to a known directory -- i wouldn't mess with the sh script at all.

Great Tip

Senthil, June 04, 2002 - 3:55 am UTC

Very Good tip can be used in many calls to call OS command

external procedures

seshadri, January 15, 2003 - 4:56 am UTC

hi
Tom
thanks for th wonderful support u have been extending, I have a similar qstn, trying to run a .VBS files from a Procedure and not able to do can you please guide me on this ?
Oracle 8i on Win-nt is the Environment


Tom Kyte
January 15, 2003 - 8:31 am UTC

i don't even know what a 'vbs' file is (beyond a carrier of viruses)...

but if it is an "executable", you can use

o java to run it.
o c to run it

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:952229840241 <code>



Really helpful -- why isn't this documented Larry??

Jonathan, January 15, 2003 - 10:26 am UTC

VBS - virus bearing script

need explaination

steve, January 27, 2003 - 9:35 pm UTC

Hi Tom,

I don't understand the following part
"| grep '^#' | sed 's/^.//' > tmp.csh "
and
dbms_output.put_line( '##!/bin/csh -f' );
dbms_output.put_line( '#' || command );
dbms_output.put_line( '#exec host.csh' );

Could you please explain for me?
How do I port this to kshell ?
can I just replace dbms_output.put_line() in the following answer to get the same result?

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:2003580269332,%7Bksh%7D <code>

Thanks!

Steve


Tom Kyte
January 28, 2003 - 7:13 am UTC

the grep keeps lines that begin with #
the sed gets rid of the first character of a line (a # sign in this case)

so the dbms_output puts out:

##!/bin/csh -f
#command
#exec host.csh

which the grep grabs and sed filters out the first character -- thats all..


as for porting to ksh -- c/csh/ksh/ and you are done.



try to run the scripts

Steve, January 28, 2003 - 4:19 pm UTC

Hi Tom,

I try to run the script you give to us.
1) host.ksh:  I just changed csh to ksh.
------- 
#!/bin/ksh

sqlplus srs/srs <<"EOF" | grep '^#' | sed 's/^.//' > tmp.ksh

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/ksh' );
                dbms_output.put_line( '#' || command );
                dbms_output.put_line( '#exec host.ksh' );
        end if;
end;
/
spool off
"EOF"

chmod +x tmp.ksh
exec tmp.ksh

--------

2) creat host procedure.
3) I run host.ksh& and SQL>exec host('ls -l');
   Nothing shows up in the window where I run host.ksh&
   and I got tmp.ksh generated but with attributes 
   -rw-rw-rw- (no x?, even though I modify 'chmod 777   tmp.ksh' in host.ksh )
4) the content of tmp.ksh:
   ls -l
   exec host.ksh 
5) if I mannually run following command 
   exec tmp.ksh 
   I got files listed and xwindow closed!


Do I miss something ? 


Thanks

Steve  

Tom Kyte
January 28, 2003 - 7:55 pm UTC

put it back to csh i guess. there is no logical reason on the planet to make it a ksh script


besides, you didn't ask me to convert that to ksh - you asked about the other.

why not just leave it as is (or understand exactly what it does and code in in ksh by hand yourself)

Pass SQL*Plus Variable Back

Jerry, February 08, 2003 - 7:26 am UTC

Is there a way to pass the value of a plus variable back to the shell?

I know in this case that dbms_application_info is more appropriate, but just as an example:

declare
l_rec t%rowtype;
begin
for i in 1 .. 200
loop
select * into l_rec from t;
if mod(i,10) = 0 then
host('echo 10 records '); -- echo value of i
end if;
end loop;
end;


Tom Kyte
February 08, 2003 - 9:31 am UTC

plsql is running on the server -- under Oracle. that echo would go to the great bit bucket in the sky. there is no "terminal" for it to be echoed to.

If you want to have "real time" output from PLSQL -- you would

o use pipes, have a process you run on your terminal read from a pipe and print out what it reads. your plsql would write to the pipe

o use utl_file, have plsql write to a file on the server file filesystem and do a tail -f on it

o use jdeveloper and run plsql in a debugger

o use dbms_application_info and the v$session_longops view

o write rows into a "message table" using an autononmous transaction

to name the first couple that come to mind.

SQL * PLUS HOST

Reddy, June 14, 2003 - 9:35 am UTC

Hi Tom
after a long time, I am asking this question.

You have an example how to call HOST command in UNIX environments. Is it possible to run in NT environments? if so how?

Thanks in advance

Reddy

A reader, June 14, 2003 - 2:42 pm UTC

Your window closed after running the script because you have

exec tmp.ksh

Anytime you run anything with 'exec' from the command-line, you are replacing the current shell. So when tmp.ksh exits, you no longer have a shell so out you go.

In other words, if you do

exec ls -l

from the command line, your window will be closed after the file listing finishes.

perl

Galit, January 25, 2004 - 9:41 am UTC

try looking into www.smashing.org/extproc_perl - this will allow use of perl code within a stored procedure.

this is only good for solaris and linux.



Great !

Andreas Bode, July 16, 2004 - 6:06 am UTC


Clarification

Mark Griffin, July 11, 2006 - 10:54 am UTC

"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)....."

Tom, when you state that you can run this script in background, do you mean at command line prompt or as a job?

Thanks! 

Tom Kyte
July 12, 2006 - 3:05 pm UTC

it is a script, you would run it at the command prompt...

Create file on disk from SQL

Sanji, February 22, 2007 - 5:55 pm UTC

Tom,
There is a requirement in our temporary DR strategy.

Need to confirm if the recovery is in progress. If not, we need to initiate it. The recovery db is always in mounted state.

So, essentially, the unix shell script has to look for the recovery db status.

select count(*) from v$recovery_log ;
if the count is > 0, then recovery is in progress, if not, we need to start the recovery by executing a sql.

How do i return a code from withing SQL or PL/SQL, in a mounted database ?
The shell script is seeking a return code, so that it can kick start the sql to recover, if the recovery is already not in progress.
If 0 then some return code, if not 0, then some other return code.

One way was to write a temporary file somewhere, depending on the status of count(*), but that wouldn't work in the mount state.

Would appreciate any feedback.

Rgds
Sanji
Tom Kyte
February 22, 2007 - 7:56 pm UTC

why does the state of anything matter here?

You have a shell script, you are very "rudimentary".

You can use dbms_output to print out something and 'grep' for results

but basically - trying to do heavy duty programming in sqlplus and shell is always going to be a bit "hackish", you'll use grep to look for output or spool and then grep.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library