Re: Calling UNIX shell script from PL/SQL procedure

Is there a way to do something similar in NT?

Michael

Thomas Kyte wrote:
>
> A copy of this was sent to ds236_at_my-dejanews.com
> (if that email address didn't require changing)
> On Mon, 19 Oct 1998 19:31:55 GMT, you wrote:
>
> >In a pl/sql script, I want to call a shell script
> >that kicks off sqlldr and loads a data file. I was
> >told that I could issue the command
> >!nameofshellscript inside the procedure and this
> >would call the script. However, the pl/sql
> >compiler does not like the '!' symbol. How should
> >this be done, or can it be done?
> >
> >-----== Posted via Deja News, The Leader in Internet Discussion ==-----
> >http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>
> 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 (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).....
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Herndon VA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
Received on Tue Oct 20 1998 - 11:41:16 CDT