如何在 Oracle 动态 SQL 中转义“：”并且还有绑定变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5531584/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to escape ":" in Oracle dynamic SQL and also have bind variables?
提问by l0ll1
I'm trying to make the following a dynamic SQL, but :character is messing up -
我正在尝试使以下内容成为动态 SQL，但:角色搞砸了 -
alter session set events 'sql_trace [sql: asasasaass]';
Example:
例子：
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;
/
Above fails with:
以上失败：
ERROR at line 1:
ORA-01006: bind variable does not exist
One :is required as per syntax of the SQL, and another :is for bind variable.
:根据 SQL 的语法需要一个，另一个:用于绑定变量。
Any ideas on how to fix this other than concatenating the bind value?
除了连接绑定值之外，关于如何解决这个问题的任何想法？
-- Edited on April 4th at 5.10pm CST to add following:
-- 于 4 月 4 日下午 5 点 CST 进行编辑，添加以下内容：
Alter session is not DDL command. Below is proof.
更改会话不是 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.
回答by Jeffrey Kemp
For this statement I'd just forget about using a bind variable, e.g.:
对于这个语句，我只是忘记了使用绑定变量，例如：
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;
/
回答by Vincent Malgrat
You can't use bind variables with DDL:
你不能在 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
In addition, you don't get this meaningful error message because the :characters are already escaped in your statement since they are between quotes (').
此外，您不会收到这条有意义的错误消息，因为这些:字符已经在您的语句中转义了，因为它们位于引号 ( ')之间。
回答by Jordan Parmer
You cannot use bind variables with DDL. With DML in PL/SQL, you cannot use bind variables either because they are automatically applied when you concatenate values to SQL statements. Each reference to a PL/SQL variable is in fact a bind variable.
您不能将绑定变量与 DDL 一起使用。对于 PL/SQL 中的 DML，您也不能使用绑定变量，因为当您将值连接到 SQL 语句时它们会自动应用。每个对 PL/SQL 变量的引用实际上都是一个绑定变量。
回答by l0ll1
I was also given following explanation, which correlate with above answers:
我还得到了以下解释，与上述答案相关：
You have to use concatenation (taking care of SQL injection risks, of course).
First, alter session set events requires a string literal. It does not support expressions, where a bind variable could be used.
Second, you try to use a bind variable inside a string literal (embedded in another string literal). Bind variables are not SQL*Plus substitution variables (&var or &&var). Substitution variables are applied by SQL*Plus before any parsing and they do not recognize any SQL syntax. They can come up anywhere in any statement. They are applied on the client not in the server.
But host bind variables are SQL syntax elements. They are allowed as operands (with a specific SQL data type) in expressions in DML, queries and PL/SQL anonymous blocks. They are not allowed in DDL or session control statements.
您必须使用串联（当然要注意 SQL 注入风险）。
首先，alter session set events 需要一个字符串文字。它不支持可以使用绑定变量的表达式。
其次，您尝试在字符串文字中使用绑定变量（嵌入在另一个字符串文字中）。绑定变量不是 SQL*Plus 替换变量（&var 或 &&var）。替换变量在任何解析之前由 SQL*Plus 应用，它们不识别任何 SQL 语法。它们可以出现在任何语句中的任何位置。它们应用于客户端而不是服务器。
但主机绑定变量是 SQL 语法元素。它们被允许作为 DML、查询和 PL/SQL 匿名块中的表达式中的操作数（具有特定的 SQL 数据类型）。在 DDL 或会话控制语句中不允许使用它们。
