I want to pass a variable argument to external SQL file (PL/SQL with SQL*Plus)

Question

I have search this forum and via google for an answer for my question, but I can't find a specific answer for my challenge. That is why I am asking it here to hope to receive an answer from one of you guys.

I want to work with multiple SQL files, whereas one SQL file is the control file that executes the other SQL files with parameters. That file is called: startup.sql

I have a table with all my values (don't mind the names of the columns, I changed them for my post).

create table control (
  S varchar2(15) not null,
  N varchar2(25 char) not null, 
  B varchar2(25 char) not null, 
  Acheck varchar2(25 char) not null, 
  Adcheck varchar2(25) not null, 
  Blu varchar2(25) not null,
  ADB varchar2(25)
)

Where one of the following is inserted (there are more entries, but one is enough to show you the way of working):

    insert into control (S,N,B,Acheck,Adcheck,Blu,ADB)
       values('Test','B','J','J','N','N', '');

My control file looks like:

set escape on
set serveroutput on
SET DEFINE ON

declare
  cursor c_lees_control is
    select S, N, B, Acheck, Adcheck, Blu, ADB
  from control

  v_s           varchar2(30);
  v_b           varchar2(30);
  v_blu     varchar2(30);

begin

  for r_lees_control in c_lees_control
  loop
    v_s := r_lees_control.S;
    v_b := r_lees_control.B;
    v_blu := r_lees_control.Blu;

    if v_b = 'J' then
      --Also tried this.
      --@C:/Temp/uitvoer.sql $v_s $v_blu
      @C:/Temp/uitvoer.sql %v_s% %v_blu%
end if;
  end loop;
end;
/

In my uitvoer.sql I have a variable like this:

    variable_s := '&&1';
    variable_blu := '&&2';

Now the following is happening. I start SQLPlus (with all my credentials) and I start my control file (control.sql). In the output of SQLPlus the following is stated:

old 89:        s = '&&1';
new 89:        s = '%v_s%';
old 128:       b_lu := '&&2';
new 128:       b_lu := '%v_blu%';

I was expecting the following:

old 89:        s = '&&1';
new 89:        s = 'Test';
old 128:       b_lu := '&&2';
new 128:       b_lu := 'J';

Why are my variables in the control file not parsed correctly to the new SQL file?

I also found the following posts: How do you pass an argument to a PL/SQL block within a sql file called using START in sqlplus? / Launch PL/SQL script from batch file (with arguments) It looks like my challenge, but I am not calling from an batchfile but from an sql file.

I hope someone can help me. If something is unclear, I can try to explain it a bit more.

Answer 1

@ is a SQL*Plus command, it has no meaning in PL/SQL. Your script is being included within the PL/SQL block at parse time, which you can see if you list the code in the buffer. The variables declared in your control block are available to the 'included' code directly, without needing substitution.

As an example, if uitvoer.sql just contains:

dbms_output.put_line(v_s);

Then this control script:

set serveroutput on
declare
  v_s varchar2(10) := 'Test';
begin
  @uitvoer.sql
end;
/

list

Produces:

Test

PL/SQL procedure successfully completed.

  1  declare
  2    v_s varchar2(10) := 'Test';
  3  begin
  4  dbms_output.put_line(v_s);
  5* end;

The PL/SQL block in the buffer has the included code, not a reference to uitvoer.sql. But the included code worked because it referred to a variable from the control script which was still in-scope.

---

If you want to allow for the control variables having different names, allowing uitvoer.sql to be called more flexibly perhaps, then you can still use substitution variables, but you're still substituting the variable name, not its value. For example, with this uitvoer.sql (note that the substitution variable assginment does not have quotes around it):

declare
  variable_s varchar2(10);
begin
  variable_s := &&1;
  dbms_output.put_line(variable_s);
end;

And your control script passing the variable name:

declare
  v_s varchar2(10) := 'Test';
begin
  @uitvoer.sql v_s
end;
/

You see:

old   7:   variable_s := &&1;
new   7:   variable_s := v_s;
Test

PL/SQL procedure successfully completed.

  1  declare
  2    v_s varchar2(10) := 'Test';
  3  begin
  4  declare
  5    variable_s varchar2(10);
  6  begin
  7    variable_s := &&1;
  8    dbms_output.put_line(variable_s);
  9  end;
 10* end;