How do you pass an argument to a PL / SQL block in a sql file called START in sqlplus?

Question

I have a bash script executing multiple sql files through sqlplus:

sqlplus $connectioninfo << end
start file1.sql
start file2.sql
start file3.sql $variable
quit
end

file3 has some PL / SQL:

BEGIN

  DBMS_OUTPUT.PUT_LINE(&1);

END;
/

But it just prints literal "&1"instead of value $variable. I also tried the following in file3:

DEFINE var_a = &1;
BEGIN

  DBMS_OUTPUT.PUT_LINE(var_a);

END;
/

as well as the following:

DECLARE
  var_b VARCHAR2(64) := &1;

BEGIN

  DBMS_OUTPUT.PUT_LINE(var_b);

END;
/

and finally:

DEFINE var_a = &1;

DECLARE
  var_b VARCHAR2(64) := var_a;

BEGIN

  DBMS_OUTPUT.PUT_LINE(var_b);

END;
/

However, I get various errors or just the literal value '& 1' for all of these.

Answer 1

Try adding SET DEFINE ONto the top of your script file3.sql.

SET DEFINE - ON, SQL * Plus &... . SET DEFINE - OFF (, , ), SQL * Plus .