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.

Why do you want to store your code in external files, rather than in the database? — David Aldridge
– David Aldridge, Commented Jan 14, 2014 at 9:19
To make it not database dependant. I use my sql files on different databases. I have one file that is creating my database structure, so it is the same everytime I need it. Also the databases are in managed by other parties and can do a role-back whenever they want. — Jeroen Mulder
– Jeroen Mulder, Commented Jan 14, 2014 at 9:59
I also tried that, that has the same effect. I have one controle file that is checking what files are nescessary to run. I have 6 external files. That all use the same variables, but they check different options. — Jeroen Mulder
– Jeroen Mulder, Commented Jan 14, 2014 at 10:05
@JeroenMulder - what you've shown shouldn't run at all; are you really enclosing the values in quotes in the call from the control script, e.g. '%v_s%' and (in Nicholas' version) 'v_s'? You also can't call an external file from PL/SQL; the contents of uitvoer.sql are being parsed as part of the block, so v_s is going to be treated as a variable in the included code - you don't need to treat it as a substitution variable at all? — Alex Poole
– Alex Poole, Commented Jan 14, 2014 at 10:48
@AlexPoole No that is just the problem, it is not running at all. I am not enclosing the values in quotes. Because if I do that, it is indeed sending it as text. And it should send the value within the variable. I think it does not parse the variable correctly. — Jeroen Mulder
– Jeroen Mulder, Commented Jan 14, 2014 at 10:56

Alex Poole · Accepted Answer · 2014-01-14 11:25:47Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related