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

Question

我试图在绑定变量的动态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;

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

Test_Pkg.Set_Nls_Calendar('Thai Buddha');

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

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

Answer 1

DDL语句中不允许使用绑定变量。下面的语句会导致错误:

• # 1:示例DDL语句 。将导致ORA-01027: bind变量不允许进行数据定义操作

  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:sci声明 。将导致ORA-02248: ALTER SESSION无效选项

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

要理解为什么会发生这种情况，我们需要看看动态SQL语句是如何处理的。

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

¹ _{回答者加重语气}

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

• 示例#1:解析器无法判断绑定值是否有效。如果程序员写的不是USING 42，而是USING 'forty-two'呢?
• 示例#2:解析器无法判断:col_name是否为有效的列名。如果绑定的列名是'identifier_that_well_exceeds_thirty_character_identifier_limit'呢?
• 示例#3: NLS_CALENDAR的值是在常量中构建的(对于给定的Oracle版本?)解析器无法判断绑定变量是否为有效值。

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

---

解决方案

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

• 示例# 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 || '''';