| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How can I call a SQL Plus script from a PL/SQL script?
also Tom posted the following
From: Thomas Kyte (tkyte_at_us.oracle.com)
Subject: Re: ??? Executing a Unix command from PL/SQL
Newsgroups: comp.databases.oracle.misc, comp.databases.oracle.tools
View: Complete Thread (7 articles) | Original Format
Date: 1998/05/13
A copy of this was sent to Bjorn Borud <borud_at_guardian.no> (if that email address didn't require changing) On 13 May 1998 15:58:19 +0200, you wrote:
>[tkyte_at_us.oracle.com (Thomas Kyte)]
>|
>| See chapter 10 in the PL/SQL manual. Its about External Procedures
>| which let you call C from PL/SQL directly. Basically, you'll create
>| a .dll on NT or .so on Unix and then you'll be able to call the
>| functions/procedures in that after you map them to pl/sql.
>
>this is something that I've been scanning the docs of Oracle for, but
>never been able to find. I looked in the PL/SQL 2.3 manual but I
>didn't find anything there.
>
>in what versions of Oracle is this possible (i generally use 7.3 under
>HP-UX and Solaris)?
>
>are there any resources available on the web describing this
>mechanism?
>
>-Bjørn
Its (external procedure) are only available with Oracle8.0 and up.
A quick and dirty way to do this is with a csh script and sqlplus as such (cut and pasted from another email)
Ok, so can you do this without C? Yes. 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 (instead of using the PLEX binary, use this shell script)
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;
----------------------- 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
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.
-- Google Home - Advertise with Us - Search Solutions - News and Resources - Language Tools - Jobs, Press, Cool Stuff... ©2002 Google In article <3d346257.1130579890_at_news-east.newscene.com>, me_at_anon.com (Don) wrote:Received on Tue Jul 16 2002 - 16:55:24 CDT
>I think youhave it backwards. I think his question is how does he
>call a SQL Plus script from the Anonymous Block. I have the same
>problem and can't figure it out.
>
>For example, I have a script called script.sql. It has DDL and SQL
>commands. At the SQL Plus prompt I can type 'run sript.sql' (or
>@script.sql) to run it and it executes with no problem
>
>Now I need to execute it from the PL/SQL block as follows:
>DECLARE
>....
>BEGIN
> LOOP
> run script.sql -- *** This is what I can't do ***
> END LOOP
>END
>
>Can you help us?
>
>
>
>On Tue, 16 Jul 2002 15:36:49 GMT, Daniel Morgan
><dmorgan_at_exesolutions.com> wrote:
>
>>mbiker wrote:
>>
>>> I have some SQL Plus scripts that help me load data. I now need to
>>> add some more complicated logic so PL/SQL is required. The PL/SQL
>>> will need a loop which calls the existing SQL Plus scripts passing a
>>> date.
>>>
>>> Is this possible?
>>>
>>> Rewriting the existing SQL Plus scripts to use PL/SQL would be a large
>>> job. These scripts work and have been in production for a while. I
>>> would like to avoid making major changes.
>>>
>>> Thanks,
>>> Don
>>
>>What you are looking for is an 'anonymous block'.
>>
>>DECLARE
>>-- your variables here
>>BEGIN
>>-- your code here inlcuding loops, IF-THEN-ELSE, whatever
>>END;
>>
>>Or a stored procedure if you want to store it permanently in the database.
>>
>>Daniel Morgan
>>
>
Julio and Colin and Lyse See my babies at http://www.geocities.com/colin_and_lyse/colin_lyse.html
 |
 |