CODEWITHSUNDEEP
sqloracle-databaseescaping
l0ll1
l0ll1

Reputation: 81

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

Upvotes: 7

Views: 6937

Answers (4)

l0ll1
l0ll1

Reputation: 81

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.

Upvotes: 1

Jordan Parmer
Jordan Parmer

Reputation: 37184

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

Upvotes: 1

Vincent Malgrat
Vincent Malgrat

Reputation: 67742

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

Upvotes: 3

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

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

Upvotes: 4

Related Questions