How to escape ":" in Oracle dynamic SQL and also have bind variables?
I'm trying to make the following a dynamic SQL, but :
character is messing up -
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开发者_如何学JAVA 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.
Any ideas on how to fix this other than concatenating the bind value?
-- Edited on April 4th at 5.10pm CST to add following:
Alter session is not DDL command. Below is proof.
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.
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;
/
You can't use bind variables with 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 ('
).
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.
http://www.akadia.com/services/ora_bind_variables.html
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.
精彩评论