7

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.
l0ll1
  • 81
  • 1
  • 5

4 Answers4

4

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;
/
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
3

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

Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • But, alter session is not a DDL. You are correct about the {:} being escaped. I looked up in dbms_sql package spec. Thanks. – l0ll1 Apr 04 '11 at 22:08
  • I think all statements that are not DML (insert, update, delete, merge) are considered DDL. – Vincent Malgrat Apr 05 '11 at 08:54
  • There are instances where SCL and even DML will refuse bind variables. See http://stackoverflow.com/q/25489002/1461424 – sampathsris Jul 15 '15 at 03:33
  • @Krumia: your example #4 is somewhat flawed. You can't use bind variables in place of objects, like a column name or a table name. You can always use a bind variables where you could place a constant, like `'A'` or `SYSDATE`. You would never use `SELECT COUNT(*) FROM SYSDATE`, so you can't use a bind variable to replace `SYSDATE` in this expression. This doesn't mean that "DML will refuse bind variables", it just means that you want to do something that is not compatible with bind variables. – Vincent Malgrat Jul 15 '15 at 07:58
  • @VincentMalgrat: Yes it is flawed to the point of being stupid. But I've seen people doing it so I included it anyway. Also, you can't always use bind variables even for constants. Consider `CREATE TABLE dummy_table ( dummy_column VARCHAR2(1) DEFAULT 'A' )`. You would not be able to use `dummy_column VARCHAR2(1) DEFAULT :def_val)`. You'll get `ORA-01027`. – sampathsris Jul 15 '15 at 09:20
  • @Krumia: but that is DDL, not DML! Exactly like the example in my answer. The whole point of this question! – Vincent Malgrat Jul 15 '15 at 09:32
1

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

Jordan Parmer
  • 36,042
  • 30
  • 97
  • 119
  • 3
    Correct, bind variable substition is done automatically for you. Minor point though: it is possible in PL/SQL to explicitly use bind variables in DML - e.g. if you use EXECUTE IMMEDIATE. – Jeffrey Kemp Apr 04 '11 at 13:39
1

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.

l0ll1
  • 81
  • 1
  • 5