如何在sqlplus中使用START传递参数到SQL文件中的PL/SQL块? [英] How do you pass an argument to a PL/SQL block within a sql file called using START in sqlplus?

问题描述

我有一个bash脚本，可通过sqlplus运行多个sql文件:

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有一些PL/SQL:

file3 has some PL/SQL:

BEGIN

  DBMS_OUTPUT.PUT_LINE(&1);

END;
/

但是它只显示文字"&1"而不是$variable的值.我也在file3中尝试了以下方法:

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

以及以下内容:

DECLARE
  var_b VARCHAR2(64) := &1;

BEGIN

  DBMS_OUTPUT.PUT_LINE(var_b);

END;
/

最后:

DEFINE var_a = &1;

DECLARE
  var_b VARCHAR2(64) := var_a;

BEGIN

  DBMS_OUTPUT.PUT_LINE(var_b);

END;
/

但是，我遇到了各种各样的错误，或者只是所有这些错误的字面值'& 1'.

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

推荐答案

尝试将SET DEFINE ON添加到脚本file3.sql的开头.

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

如果SET DEFINE是ON，则SQL * Plus会将&...替换参数替换为其值.如果SET DEFINE是OFF(似乎适合您)，则SQL * Plus将不会执行此操作.

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.

这篇关于如何在sqlplus中使用START传递参数到SQL文件中的PL/SQL块?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！