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

mbiker <annon_at_someaddress.com> wrote in message news:<pb28ju4tn6dkc5eoi85oniblph7ube28a4_at_4ax.com>...
> 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

1- There is no need to crosspost

2- With 8.1 and below the only way I know to pass information to the OS from plsql is using one of the following packages: dbms_alert or dbms_pipe with a deamon type program running on the OS to handle the messages or to write an External procedure that issues OS comamnds/scripts.

3- If the script can run on the database server, What about writing a shell script that fires a session that invokes the plsql routines and have the routines write a .sql file with the sqlplus scripts in them.

example sql file: start script1 datevalue1

                  start script1 datevalue2 ...

Then when the plsql scripts finish the shell can launch the just written control sqlplus script via a sqlplus u/p _at_control_script.sql

I would expect that script1 would accept variables using the &1 parameter syntax.

Just my idea of something to think about.

• Mark D Powell --