Re: How can I call a SQL Plus script from a PL/SQL script?

From: <Christopher.Jones_at_oracle.comX>
Date: 17 Jul 2002 13:17:48 +1000
Message-ID: <u8z4bcaar.fsf@oracle.comX>

mbiker <annon_at_someaddress.com> writes:

> 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.

Depending on what the subscripts do and whether/when the loaded data needs PL/SQL post-processing, you may be able to do something like:

    set serveroutput on
    set trimspool off
    set termout off
    spool parent.sql
    begin

	dbms_output.put_line('@myscript.sql '||to_char(sysdate,'DD-MON-YYYY'));
	dbms_output.put_line('@myscript.sql '||'01-JAN-2002');
	dbms_output.put_line('exit');

    end;
    /
    spool off

    set termout on
    @parent.sql

Chris

-- 
Christopher Jones, Oracle Corporation, Australia.

Received on Tue Jul 16 2002 - 22:17:48 CDT