CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Mladen Nikolic
Mladen Nikolic

Reputation: 71

SQL - Escape strings (ORACLE DB)

I'm struggling a little bit about this maybe someone sees something that i don't...

select 'BEGIN dbms_stats.gather_table_stats('''SCHEMA'', ''TABLENAME_'' || to_char(trunc(sysdate, 'MM'), 'YYYY-MM')', cascade => true, no_invalidate => false); END;' from dual;

So the output should be:

BEGIN dbms_stats.gather_table_stats('SCHEMA', 'TABLENAME_2021_07'), cascade => true, no_invalidate => false); END;

Somehow I am not finding a way to escape the strings, what am I doing wrong?

Thanks in advance

Upvotes: 1

Views: 243

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If I understand correctly:

select 'BEGIN dbms_stats.gather_table_stats(''SCHEMA'', ''TABLENAME_' ||
       to_char(trunc(sysdate, 'MM'), 'YYYY-MM') ||
       ''', cascade => true, no_invalidate => false); END;'
from dual;

Upvotes: 1

MT0
MT0

Reputation: 167774

You want:

select 'BEGIN dbms_stats.gather_table_stats(''SCHEMA'', ''TABLENAME_'
       || to_char(sysdate, 'YYYY_MM')
       || ''', cascade => true, no_invalidate => false); END;'
from   dual;

I find it helps to format it so that static and dynamic parts are on different lines then you can write out the static strings and then double up all the quotes to escape them and put a leading and trailing quote for the start and end of the string literal.

or, if you want a q-quoted string literal:

select q'[BEGIN dbms_stats.gather_table_stats('SCHEMA', 'TABLENAME_]'
       || to_char(sysdate, 'YYYY_MM')
       || q'[', cascade => true, no_invalidate => false); END;]'
from   dual;

Just write out your static string and add q'[ to the start and ]' to the end.

Upvotes: 0

Marmite Bomber
Marmite Bomber

Reputation: 21043

Here the reversed approach starting with the result and leading the result expression

  • Start with the result string

    BEGIN dbms_stats.gather_table_stats('SCHEMA', 'TABLENAME_2021_07'), cascade => true, no_invalidate => false); END;

  • Split it in the static and dynamic part

     BEGIN dbms_stats.gather_table_stats('SCHEMA', 'TABLENAME_
 2021_07                                                         <--- dynamic
 '), cascade => true, no_invalidate => false); END;

  • enclose the static parts with a q-quotes (so you need not to escape!), replace the dynamic part with the expression and concatente all parts with ||

Add in a select query:

 select
       q'[BEGIN dbms_stats.gather_table_stats('SCHEMA', 'TABLENAME_]' ||
       to_char(trunc(sysdate, 'MM'), 'YYYY-MM') ||                                                     
       q'['), cascade => true, no_invalidate => false); END;]'
    from dual;

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

Use the q-quoting mechanism, it makes it simpler (you don't have to escape single quotes):

SELECT q'[BEGIN dbms_stats.gather_table_stats('SCHEMA', 'TABLENAME_' || to_char(trunc(sysdate, 'MM'), 'YYYY_MM'), cascade => true, no_invalidate => false); END;]' result
  FROM DUAL;

which results in

BEGIN dbms_stats.gather_table_stats('SCHEMA', 'TABLENAME_' || to_char(trunc(sysdate, 'MM'), 'YYYY_MM'), cascade => true, no_invalidate => false); END;

Your statement is long so it is difficult to see it, so - here's a simplified example which shows how to do it:

SQL> select 'stats(''schema'')' old_result,
  2         q'[stats('schema')]' new_result
  3  from dual;

OLD_RESULT      NEW_RESULT
--------------- ---------------
stats('schema') stats('schema')

SQL>
  • q
  • followed by a single quote
  • followed by bracket (use the one that isn't part of the string!)
  • now, write your statement as you would if it weren't enclosed into single quotes
  • end it with a closing bracket ...
  • ... and a terminating single quote

Upvotes: 0

Related Questions