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

Question

I have a bash script running several sql files via 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 the literal "&1" instead of the value of $variable. I have also tried the following in file3:

DEFINE var_a = &1;
BEGIN

  DBMS_OUTPUT.PUT_LINE(var_a);

END;
/

and also 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 am getting various errors or just the literal value '&1' for all of these.

just small correction ... >> end should be rather ... << end — igr
– igr, Commented Mar 21, 2013 at 22:17

Luke Woodward · Accepted Answer · 2013-03-21 22:24:00Z

4

Try adding SET DEFINE ON to the start of your script file3.sql.

If SET DEFINE is ON, SQL*Plus will replace &... substitution parameters with their values. If SET DEFINE is OFF (which it seems to be for you), SQL*Plus won't do this.

answered Mar 21, 2013 at 22:24

Luke Woodward

65.4k16 gold badges94 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xdhmoore Over a year ago

Thanks. Indeed, my file1.sql had a set define off in it.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related