| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Calling UNIX shell script from PL/SQL procedure
A copy of this was sent to Michael Rothwell
<michael_rothwell_at_non-hp-usa-om46.om.hp.com>
(if that email address didn't require changing)
On Tue, 20 Oct 1998 09:41:16 -0700, you wrote:
>Is there a way to do something similar in NT?
>
Unfortunately, due to the way NT works -- no, its not as easy out of the box.
On NT, I've used plex (see http://govt.us.oracle.com/ under downloadable utilities for a description of plex) to implement the equivalent of external procedures in v7.x of the database (you need pro*c and a c compiler for this to work)... If you like the description of plex, download the unix version that is there and email me for the NT copy (the nt copy just overlays a couple of the unix pieces so you need that one first)
NT out of the box has a pretty poor scripting language so unless you get the gnu shell or use perl or something, you cannot do it as easily as we did below.
>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.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
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 - 12:59:57 CDT
 |
 |