Question Details [ID: 150612348067]
              
             Print 
             Help 

         QuestionsSearch/Archives

        Search  Archives  [ Question Details ]  

        
                    
                    
            Shabbir -- Thanks for the question regarding "How do I execute an 
            executable from PLSQL", version 

            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 
            http://osi.oracle.com/~tkyte/plex/index.html
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).....

                    
            Was this response helpful to you? Let us know!   Reviews   
                    March 20, 2001 
                  Reviewer:  alex  from Moscow, Russia 


                    March 25, 2001 
                  Reviewer:  A reader 


                    May 11, 2001 
                  Reviewer:  Shunan Xiang  from Rochester, NY 


                    May 24, 2001 
                  Reviewer:  Hossam Eldin Emam  from EGYPT 
                  thank than I need 

                  Bloody marvelous  June 07, 2001 
                  Reviewer:  Andrew Fyfe  from Ottawa, Ontario, Canada 
                  Solved my FTP problem, and has potential for hundreds of other 
                  uses! 




            Bookmark this page with the link HERE 
                        Information 


                               This page provides the details of the question 
                              asked. To find another question click on the 
                              search tab. To view the question archives by week 
                              click on the archives tab. 








       
      Copyright  2001 Oracle Corporation, All rights reserved. 


