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 decl…

Accepted Answer

`@` 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;
```

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

Solution

Problem