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

You cannot call Sql scripts from a PLSQL block...i.e. Unless you invoke them using external procedure. If you want to loop in your procedure just to call different scripts but the value of DATE remains constant then you can use something like in step (1). If your DATE value will change depending on processing in Sql scripts then goto (2).

(1)
You have to use SQL*Plus for this.
Create a new script e.g. PlsqlScript.sql. In the script declare a global variable of type VARCHAR2. Note: You cannot have DATE type global variable.

• Start of script VARIABLE MyDate_ VARCHAR2(25);

DECLARE
   MyRealDate_ DATE;
BEGIN

• Put your logic here and convert your date to VARCHAR2 and assign value. e.g. MyRealDate_ := sysdate; :MyDate_ := TO_CHAR(MyRealDate_, 'MMDDYYYY'); END; /
  • Now call your Sql script @MyScript.sql
  • In MyScript.sql, you can refer to MyDate_ variable as :MyDate_ and
  • convert it back to DATE e.g. INSERT INTO MyTable (col1, col2, col3) VALUES (TO_DATE(:MyDate_, 'MMDDYYYY'), 'VAL2', 'VAL3');

(2)
You can use above steps and if your DATE value changes after running each script, you can insert another PLSQL block after running the script to assign a different value to :MyDate_.

HTH
//Rauf Sarwar Received on Tue Jul 16 2002 - 15:20:18 CDT