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?
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 @control_script.sql
I would expect that script1 would accept variables using the &1 parameter syntax.
Just my idea of something to think about.