SQL Templates and multiple REPLACE

I frequently use what I call “SQL templates”, where I replace multiple placeholders by calculated values. Here are two methods that don’t require a stored procedure.

What do I mean by “template”?

SQL statements have to be parsed before they can be executed. The standard way to minimise parsing is to use bind variables. If we use a bind variable instead of a literal, the same statement can be parsed once and executed many times with different values assigned to the variable.

We cannot put the names of tables or columns into bind variables: these are data objects that have to be known at parse time.

What I mean by “template” is a SQL statement with a specific logic, but without the names of the data objects. I get the names I need from input parameters and queries, then I put them in the statement where the placeholders were. If I do this twice with different parameters and the resulting text is different, the optimizer will of course parse each result separately before execution.

So: don’t confuse this technique with bind variables.

Please keep in mind that I never use this technique in production. I generate the SQL, read it, test it and then deploy it. This technique is meant to make SQL development more productive, not replace the developer.

If you ever want to generate SQL in production, work very hard to avoid SQL injection bugs. For example, use DBMS_ASSERT or data dictionary lookups to make sure input parameters are really data objects.

Examples

select * from emp where ename = 'KING';

This statement is not a template and it doesn’t use bind variables.

select * from emp where ename = :ename;

This statement is not a template, but it uses a bind variable.

select /*+ qb_name(COMPARE) */
  #COL_LIST#,
  decode(sign(sum(NEW_CNT)), 1, 'I', 'D') OP,
  abs(sum(NEW_CNT)) CNT
FROM (
  select /*+ qb_name(old) */
  #COL_LIST#
    , -1 NEW_CNT
  from #OLD_TABLE# O
  union all
  select /*+ qb_name(new) */
  #COL_LIST#
    , 1 NEW_CNT
  from #NEW_TABLE# N
)
group by
  #COL_LIST#
having sum(NEW_CNT) != 0
order by 1, OP;

This is a “template” as I use the word here. It shows the logic for comparing two tables using the GROUP BY method. To get a usable SQL statement, I need to replace all the placeholders (enclosed in #) by real values.

Model clause technique

Here is one way to do multiple replacements using pure SQL:

with template(sql_text) as (select cast(
q'§<template>§'
as varchar2(4000)) from dual
)
, parms(v_from, v_to) as (
  <parms>
)
select sql_text from template
model
REFERENCE P ON (select rownum p_dim, v_from, v_to from parms)
  DIMENSION BY (p_dim) measures (v_from, v_to) 
main M
dimension by (1 m_dim)
measures(sql_text)
rules iterate(99) until P.v_from[iteration_number+1] is null (
  sql_text[1] = replace(sql_text[1],P.v_from[iteration_number+1], P.v_to[iteration_number+1])
);
  1. Put the template text in the TEMPLATE subquery.
  2. In the PARMS subquery, create one row per replacement: V_FROM is the placeholder and V_TO is the value.
  3. The MODEL clause will iterate through the reference model P and do each replacement in turn.

Here is an example that generates a comparison. It’s a silly example because I am comparing EMP to itself, but it’s just for illustration!

with template(sql_text) as (select cast(
q'§select /*+ qb_name(COMPARE) */
  #COL_LIST#,
  decode(sign(sum(NEW_CNT)), 1, 'I', 'D') OP,
  abs(sum(NEW_CNT)) CNT
FROM (
  select /*+ qb_name(old) */
  #COL_LIST#
    , -1 NEW_CNT
  from #OLD_TABLE# O
  union all
  select /*+ qb_name(new) */
  #COL_LIST#
    , 1 NEW_CNT
  from #NEW_TABLE# N
)
group by
  #COL_LIST#
having sum(NEW_CNT) != 0
order by 1, OP;§'
as varchar2(4000)) from dual
)
, parms(v_from, v_to) as (
  select '#NEW_TABLE#', 'EMP' from dual
  union all
  select '#OLD_TABLE#', 'EMP' from dual
  union all
  select '#COL_LIST#',
    listagg(column_name, ',')  within group(order by internal_column_id)
  from user_tab_cols
  where table_name = 'EMP'
  and virtual_column = 'NO'
)
select sql_text from template
model
REFERENCE P ON (select rownum p_dim, v_from, v_to from parms)
  DIMENSION BY (p_dim) measures (v_from, v_to) 
main M
dimension by (1 m_dim)
measures(sql_text)
rules iterate(99) until P.v_from[iteration_number+1] is null (
  sql_text[1] = replace(sql_text[1],P.v_from[iteration_number+1], P.v_to[iteration_number+1])
);
select /*+ qb_name(COMPARE) */
  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO,
  decode(sign(sum(NEW_CNT)), 1, 'I', 'D') OP,
  abs(sum(NEW_CNT)) CNT
FROM (
  select /*+ qb_name(old) */
  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
    , -1 NEW_CNT
  from EMP O
  union all
  select /*+ qb_name(new) */
  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
    , 1 NEW_CNT
  from EMP N
)
group by
  EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
having sum(NEW_CNT) != 0
order by 1, OP;

Using new stuff: JSON

The problem with the MODEL technique is that the output cannot be a CLOB. To get a CLOB, suppose we use a bit of PL/SQL in a WITH clause? Now we have to pass the placeholder / value pairs as parameters. I decided that a JSON object would be a reasonable implementation. Warning: this looks nice but requires version 12.2 or later.

with  function multi_replace(
    p_template in varchar2,
    p_parms in varchar2
  ) return clob is
    l_out_text clob := p_template;
    l_json JSON_OBJECT_T := JSON_OBJECT_T.parse(p_parms);
    l_keys JSON_KEY_LIST;
  begin
    l_keys := l_json.get_keys;
    for i in 1..l_keys.count loop
      l_out_text := replace(l_out_text, l_keys(i), l_json.get_string(l_keys(i)));
    end loop;
    return l_out_text;
  end multi_replace;
input(template, parms) as (select
q'§select /*+ qb_name(COMPARE) */
  #COL_LIST#,
  decode(sign(sum(NEW_CNT)), 1, 'I', 'D') OP,
  abs(sum(NEW_CNT)) CNT
FROM (
  select /*+ qb_name(old) */
  #COL_LIST#
    , -1 NEW_CNT
  from #OLD_TABLE# O
  union all
  select /*+ qb_name(new) */
  #COL_LIST#
    , 1 NEW_CNT
  from #NEW_TABLE# N
)
group by
  #COL_LIST#
having sum(NEW_CNT) != 0
order by 1, OP;§',
  json_object(
    '#NEW_TABLE#' is 'EMP',
    '#OLD_TABLE#' is 'EMP',
    '#COL_LIST#'  is (
      select listagg(column_name, ',')  within group(order by internal_column_id)
      from user_tab_cols
      where table_name = 'EMP' and virtual_column = 'NO'
    )
  )
  from dual
)
select multi_replace(template, parms) sql_text
from input;
  • The PL/SQL function parses the input JSON object, gets all the keys (the placeholders), then uses them to get the values one by one and apply them to the template.
  • The JSON_OBJECT function in SQL turns each placeholder / replacement pair into a JSON name:value pair.

I like this approach because it shows each placeholder and its replacement value side by side. It’s just easier for me to read (and write!).

Advertisements

One thought on “SQL Templates and multiple REPLACE

  1. Hello Stew,

    Very nice approach using JSON !

    Just as a curiosity / bug, LiveSQL cannot cope with a WITH clause that contains both PL/SQL functions and regular subqueries !

    Attempting to run the query in LiveSQL produces the following:

    ORA-00905: missing keyword
    Unsupported Command
    Unsupported Command
    ORA-06550: line 8, column 1: PLS-00103: Encountered the symbol “INPUT”

    I will try to reproduce the problem with a simple query and send it to the LiveSQL team for checking.

    Another approach for solving the multiple replace, in fact a natural counterpart to the MODEL clause,
    is to use a recursive WITH query, like this:

    with template(sql_text) as (select cast(
    q’§§’
    as varchar2(4000)) from dual
    )
    , parms(v_from, v_to) as (

    )
    , parms_list (iteration, v_from, v_to) as (
    select rownum, v_from, v_to from parms
    )
    , template_iterations (iteration, sql_text) as (
    select p.iteration,
    replace (t.sql_text, p.v_from, p.v_to)
    from template t,
    parm_list p
    where p.iteration = 1
    union all
    select p.iteration,
    replace (t.sql_text, p.v_from, p.v_to)
    from template_iterations t,
    parm_list p
    where p.iteration = t.iteration + 1
    )
    select max(sql_text) keep(dense_rank last order by iteration)
    from template_iterations
    /

    Thanks a lot,
    Best regards, Happy holidays and Happy New Year 2019 :)

    Iudith Mentzel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s