oracle 为什么我不能在动态 SQL 的 DDL/SCL 语句中使用绑定变量？

Why cannot I use bind variables in DDL/SCL statements in dynamic SQL?

提问by sampathsris

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

我正在尝试使用绑定变量在动态 SQL 中执行 SQL 命令：

-- 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');

But this get's me ORA-02248: invalid option for ALTER SESSION.

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

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

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

回答by sampathsris

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

DDL 语句中不允许绑定变量。所以下面的语句会导致错误：

• Example #1: DDL statement. Will cause ORA-01027: bind variables not allowed for data definition operations

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

• Example #2: DDL statement. Will cause ORA-00904: : invalid identifier

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

• Example #3: SCL statement. Will cause ORA-02248: invalid option for ALTER SESSION

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

• 示例#1：DDL 语句。会导致ORA-01027: 数据定义操作不允许绑定变量

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

• 示例#2：DDL 语句。会导致ORA-00904::invalid identifier

  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;
  

Problem

问题

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

要了解为什么会发生这种情况，我们需要查看如何处理动态 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.

¹_{Emphasis added by answerer}

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

¹_{重点由回答者添加}

Note that parsing step happens beforebinding 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.

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

• 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_namewould 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_CALENDARare built in constants (for a given Oracle version?). Parser cannot tell if the bound variable will have a valid value.

• 示例 #1：解析器无法判断绑定值是否有效。如果不是USING 42，程序员写的USING 'forty-two'怎么办？
• 示例 #2：解析器无法判断是否:col_name是有效的列名。如果绑定列名称是'identifier_that_well_exceeds_thirty_character_identifier_limit'？
• 示例 #3： 的值NLS_CALENDAR是内置常量（对于给定的 Oracle 版本？）。解析器无法判断绑定变量是否具有有效值。

So the answer is that you cannot bind schema elements such as table names, column names in dynamic SQL. Nor you can bind built in constants.

所以答案是你不能在动态 SQL 中绑定表名、列名等模式元素。你也不能绑定内置常量。

---

---

Solution

解决方案

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

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

• Example #1:

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

• Example #2:

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

• Example #3:

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

• 示例#1：

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

• 示例#2：

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

• 示例#3：

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