如何在 Oracle 动态 SQL 中转义 ":" 并使用绑定变量？

Question

我正在尝试将以下内容制作为动态SQL，但是:字符会导致问题 -

alter session set events 'sql_trace [sql: asasasaass]';

例子：

declare
 l_trc_cmd   varchar2(500);
 l_sql_id    varchar2(500) := 'asasasaass';
begin
  l_trc_cmd     := q'# alter session set events 'sql_trace [sql: :L_SQL_ID]' #';
  execute immediate l_trc_cmd using l_sql_id;
end;
/

上述操作失败，出现以下错误：

ERROR at line 1:
ORA-01006: bind variable does not exist

---

根据SQL语法，需要一个:，另一个:是用于绑定变量。

除了将绑定值连接起来，还有什么其他的解决方法吗？

-- 在4月4日下午5点10分CST进行了编辑，以下是证明：

Alter session不是DDL命令。

sqlplus+> select * from t2;

         A
----------
         1

1 row selected.

sqlplus+> insert into t2 values(2);

1 row created.

sqlplus+> alter session set tracefile_identifier ="umappsperf1" statistics_level=all;

Session altered.

sqlplus+> alter session set events 'sql_trace wait=true';

Session altered.

sqlplus+> select * from t2;

         A
----------
         2
         1

2 rows selected.

sqlplus+> rollback;

Rollback complete.

sqlplus+> select * from t2;

         A
----------
         1

1 row selected.

- l0ll1

---

你为什么认为ALTER SESSION不是DDL，以及在SQL*Plus中运行它如何证明它不是DDL？ - Jeffrey Kemp

Answer 1

对于这个语句，我建议不要使用绑定变量，例如：

declare
 l_trc_cmd   varchar2(500);
 l_sql_id    varchar2(500) := 'asasasaass';
begin
  l_trc_cmd := REPLACE(
    q'# alter session set events 'sql_trace [sql: %SQLID%]' #'
    ,'%SQLID%',l_sql_id);
  execute immediate l_trc_cmd;
end;
/

- Jeffrey Kemp

---

在DDL中是否可能使用绑定变量？ - Vincent Malgrat

Answer 2

DDL语句不能使用绑定变量:

SQL> exec execute immediate 'CREATE TABLE test AS SELECT :x t FROM DUAL' USING 1;

ORA-01027: bind variables not allowed for data definition operations
ORA-06512: at line 2

此外，由于冒号字符 (:) 已经被引号 (') 包裹并转义了，所以您不会收到这个有意义的错误信息。

- Vincent Malgrat

---

但是，alter session 不是 DDL。你关于 {:} 被转义的说法是正确的。我在 dbms_sql 包规范中查找了一下。谢谢。 - l0ll1

我认为所有不是DML（插入，更新，删除，合并）的语句都被视为DDL。 - Vincent Malgrat

有时SCL甚至DML会拒绝绑定变量。请参见https://dev59.com/ZV8e5IYBdhLWcg3wq79K - sampathsris

@Krumia：你的第四个例子有些错误。你不能在对象的位置使用绑定变量，比如列名或表名。你总是可以在可以放置常量的地方使用绑定变量，比如 'A' 或 SYSDATE。你永远不会使用 SELECT COUNT(*) FROM SYSDATE，所以你不能使用绑定变量来替换这个表达式中的 SYSDATE。这并不意味着“DML将拒绝绑定变量”，只是意味着你想做一些与绑定变量不兼容的事情。 - Vincent Malgrat

@VincentMalgrat：是的，这种做法非常愚蠢。但我见过有人这么做，所以我还是把它包含了进来。此外，即使对于常量，你也不能总是使用绑定变量。考虑 CREATE TABLE dummy_table ( dummy_column VARCHAR2(1) DEFAULT 'A' )。你将无法使用 dummy_column VARCHAR2(1) DEFAULT :def_val)。你会得到 ORA-01027。 - sampathsris

@Krumia：但那是DDL，不是DML！就像我回答中的例子一样。这个问题的整个重点！ - Vincent Malgrat

Answer 3

在DDL中，你不能使用绑定变量。在PL/SQL中的DML操作也不能使用绑定变量，因为当你将值拼接到SQL语句时，它们会自动应用。实际上，每个对PL/SQL变量的引用都是一个绑定变量。

http://www.akadia.com/services/ora_bind_variables.html

- Jordan Parmer

---

没问题，绑定变量替换会自动为您完成。不过需要指出的是，在PL/SQL中明确地在DML中使用绑定变量也是可能的，例如，如果您使用EXECUTE IMMEDIATE。 - Jeffrey Kemp

Answer 4

我还收到了以下解释，与上面的答案相关：

引用：

您必须使用连接（当然要注意SQL注入风险）。

首先，alter session set events需要一个字符串字面量。它不支持表达式，其中可以使用绑定变量。

其次，您尝试在字符串字面量中使用绑定变量（嵌入在另一个字符串字面量中）。绑定变量不是SQL*Plus替换变量（&var或&&var）。替换变量在任何解析之前由SQL*Plus应用，并且它们不识别任何SQL语法。它们可以出现在任何语句中的任何位置。它们在客户端上应用而不是在服务器上。

但是，主机绑定变量是SQL语法元素。它们允许作为DML、查询和PL/SQL匿名块中表达式中的操作数（具有特定的SQL数据类型）。它们不允许在DDL或会话控制语句中使用。

- l0ll1