为什么不能在动态SQL的DDL/SCL语句中使用绑定变量? [英] Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?

问题描述

我正在尝试在具有绑定变量的动态SQL中执行SQL命令:

I am trying to execute an SQL command within dynamic SQL with bind variables:

-- this procedure is a part of PL/SQL package Test_Pkg
PROCEDURE Set_Nls_Calendar(calendar_ IN VARCHAR2)
IS
BEGIN
   EXECUTE IMMEDIATE
      'ALTER SESSION
      SET NLS_CALENDAR = :cal'
      USING IN calendar_;
END Set_Nls_Calendar;

然后在客户端，我试图调用该过程:

Then on the client side, I am trying to invoke the procedure:

Test_Pkg.Set_Nls_Calendar('Thai Buddha');

但这就是我ORA-02248: invalid option for ALTER SESSION.

我的问题是: 为什么不能在动态SQL的DDL/SCL语句中使用绑定变量?

And my question is: Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?

推荐答案

DDL语句中不允许使用绑定变量.因此，以下语句将导致错误:

Bind variables are not allowed in DDL statements. So following statements will cause errors:

• 示例1:DDL语句 .将导致 ORA-01027:数据定义操作不允许使用绑定变量

EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT :def_val )'
  USING 42;

示例2:DDL语句 .会导致 ORA-00904::无效标识符

EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table ( :col_name NUMBER )'
  USING var_col_name;

示例#3:SCL语句 .会导致 ORA-02248:ALTER SESSION的无效选项

EXECUTE IMMEDIATE
  'ALTER SESSION SET NLS_CALENDAR = :cal'
  USING var_calendar_option;

要了解为什么会发生这种情况，我们需要查看动态SQL语句的方式已处理.

To understand why this happens, we need to look at How Dynamic SQL Statements Are Processed.

通常，应用程序会提示用户输入SQL语句的文本以及该语句中使用的主机变量的值.然后，Oracle解析SQL语句.也就是说，Oracle检查SQL语句以确保其遵循语法规则，并且引用有效的数据库对象.解析还涉及检查数据库访问权限 ¹ ，保留所需的资源以及找到最佳访问路径.

Typically, an application program prompts the user for the text of a SQL statement and the values of host variables used in the statement. Then Oracle parses the SQL statement. That is, Oracle examines the SQL statement to make sure it follows syntax rules and refers to valid database objects. Parsing also involves checking database access rights¹, reserving needed resources, and finding the optimal access path.

¹ _{应答者添加的重点}

请注意，解析步骤发生在将所有变量绑定到动态语句之前.如果查看以上四个示例，您将意识到，解析器无法在不知道绑定变量值的情况下保证这些动态SQL语句的语法有效性.

Note that parsing step happens before binding any variables to the dynamic statement. If you examine the above four examples, you will realize that there is no way for the parser to guarantee the syntactical validity of these dynamic SQL statements without knowing the values for bind variables.

• 示例#1 :解析器无法判断绑定值是否有效.如果程序员写了USING 'forty-two'而不是USING 42怎么办?
• 示例2 :解析器无法确定:col_name是否是有效的列名.如果绑定的列名称是'identifier_that_well_exceeds_thirty_character_identifier_limit'怎么办?
• 示例#3 :NLS_CALENDAR的值内置于常量中(对于给定的Oracle版本?).解析器无法确定绑定变量是否将具有有效值.

• Example #1: Parser cannot tell if the bind value will be valid. What if instead of USING 42, programmer wrote USING 'forty-two'?
• Example #2: Parser cannot tell if :col_name would be a valid column name. What if the bound column name was 'identifier_that_well_exceeds_thirty_character_identifier_limit'?
• Example #3: Values for NLS_CALENDAR are built in constants (for a given Oracle version?). Parser cannot tell if the bound variable will have a valid value.

因此，答案是您不能在动态SQL中绑定架构元素，例如表名，列名.也不能绑定内置常量.

实现动态引用架构元素/常量的唯一方法是在动态SQL语句中使用字符串连接.

The only way to achieve referencing schema elements/constants dynamically is to use string concatenation in dynamic SQL statements.

• 示例1:

• Example #1:

EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table ( dummy_column NUMBER DEFAULT ' || to_char(42) || ')';

示例2:

Example #2:

EXECUTE IMMEDIATE
  'CREATE TABLE dummy_table (' || var_col_name || ' NUMBER )';

示例3:

Example #3:

EXECUTE IMMEDIATE
  'ALTER SESSION SET NLS_CALENDAR = ''' || var_calendar_option || '''';

这篇关于为什么不能在动态SQL的DDL/SCL语句中使用绑定变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！