SQL - Escape strings (ORACLE DB)

Question

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

Instead of having dynamic table names for each months you my consider partitioning — Wernfried Domscheit
– Wernfried Domscheit, Commented Jul 2, 2021 at 11:48
Does it need to be dynamic? It looks like you could construct the table name as TABLENAME_2021_07 and then just pass it to dbms_stats.gather_table_stats as normal. — William Robertson
– William Robertson, Commented Jul 2, 2021 at 13:37

Marmite Bomber · Accepted Answer · 2021-07-02 11:27:25Z

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;

Gordon Linoff · Accepted Answer · 2021-07-02 12:39:36Z

1

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;

edited Jul 2, 2021 at 12:39

answered Jul 2, 2021 at 11:04

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

2 Comments

Mladen Nikolic Over a year ago

Hi, thx for your answer, the only issue here is the ' after TABLENAME with that query I get: 'TABLENAME'_DATE any idea?

Gordon Linoff Over a year ago

@MladenNikolic . . . I moved the closing quote.

Littlefoot · Accepted Answer · 2021-07-02 11:22:13Z

0

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

edited Jul 2, 2021 at 11:22

answered Jul 2, 2021 at 11:04

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

3 Comments

Mladen Nikolic Over a year ago

Hi, thx for your answer, can I use multiple q-quoting mechanisms in a single select statement? since the trunc() function needs to calculate the actual date in a string it does not do that so I suppose I need to divide it from the string right?

Littlefoot Over a year ago

There are 2 typos in code you posted (and I reused), here: 'YYYY-MM')': table name can't have a dash (should probably be underline), and the last single quote should be removed. As of your question: you don't have to use the mechanism multiple times (but you can). Though, if you fix what I just wrote (and fixed in the answer as well), it might work OK.

Marmite Bomber Over a year ago

The challange here is to not "escape" the || concatenations in the q-string. Which of course works but produces a different result. I know it from own hard experience;)

MT0 · Accepted Answer · 2021-07-02 11:31:44Z

0

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.

edited Jul 2, 2021 at 11:31

answered Jul 2, 2021 at 11:26

MT0

173k12 gold badges70 silver badges136 bronze badges

Collectives™ on Stack Overflow

SQL - Escape strings (ORACLE DB)

4 Answers 4

Comments

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related