Re: must run a unix script in PLSQL

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Fri, 08 Jan 1999 17:31:23 +0800
Message-ID: <3695D06B.1D58@bhp.com.au>

Dennis Havermans wrote:
>
> Help me,
>
> I need to run a unix-script, or startup oracle*reports (r30run) from PLSQL
> (I'm using Oracle 7.3.3 on Solaris)
>
> Dennis Havermans
> Oracle DBA

Courtesy a previous post some time ago from Thomas K.

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

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

sqlplus userid/password <<"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).....

--

---

Connor McDonald
BHP Information Technology
Perth, Western Australia
"The difference between me and a madman is that I am not mad"

• Salvador Dali

Received on Fri Jan 08 1999 - 03:31:23 CST