How to escape ":" in Oracle dynamic SQL and also have bind variables?

Question

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 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.

Answer 1

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;
/

Answer 2

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 (').

Answer 3

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