Generating SQL with SQL templates

SQL databases offer a huge advantage to developers: we can use queries against the data dictionary to generate SQL code. “SQL templates” are a simple idea that makes this technique easier to use.

Let’s take the query I used in my previous post to compare two tables, and make a template out of it.

select KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING,
sum(OLD_CNT) OLD_CNT, sum(NEW_CNT) NEW_CNT
FROM (
  select KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING,
    1 OLD_CNT, 0 NEW_CNT
  from T_TARGET o
  union all
  select KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING,
    0 OLD_CNT, 1 NEW_CNT
  from T_SOURCE n
)
group by KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING
having sum(OLD_CNT) <> sum(NEW_CNT);

A template is a way of describing a pattern: you distinguish what varies each time from what stays the same, and you put placeholders where the variable stuff should go. In this query, the only parts that vary are the two table names and the list of columns, so we need 3 template placeholders.

select #COL_LIST#,
sum(OLD_CNT) OLD_CNT, sum(NEW_CNT) NEW_CNT
FROM (
  select #COL_LIST#,
    1 OLD_CNT, 0 NEW_CNT
  from #OLD_TABLE# o
  union all
  select #COL_LIST#,
    0 OLD_CNT, 1 NEW_CNT
  from #NEW_TABLE# n
)
group by #COL_LIST#
having sum(OLD_CNT) <> sum(NEW_CNT);

To generate real SQL from the template, I need to get a value for each placeholder and then replace the placeholder by its value. For this template, I’ll enter the names of the two tables and calculate the column list.

with input as (
  select 'T_SOURCE' new_table, 'T_TARGET' old_table from dual
)
, calculated_values as (
  select listagg(column_name, ',')
    within group(order by internal_column_id) col_list
  from user_tab_cols, input
  where table_name = old_table
  and virtual_column = 'NO'  
)
, template as ( select
q'
select sum(OLD_CNT) OLD_CNT, sum(NEW_CNT) NEW_CNT
FROM (
  select #COL_LIST#,
    1 OLD_CNT, 0 NEW_CNT
  from #OLD_TABLE# o
  union all
  select #COL_LIST#,
    0 OLD_CNT, 1 NEW_CNT
  from #NEW_TABLE# n
)
group by #COL_LIST#
having sum(OLD_CNT) <> sum(NEW_CNT);>'
txt from dual
)
select
replace(
  replace(
    replace(
      txt,
      '#COL_LIST#',
      col_list
    ),
    '#NEW_TABLE#',
    new_table
  ),
  '#OLD_TABLE#',
  old_table
) sql_text
from input, calculated_values, template;
  • The wonderful LISTAGG function is available starting in version 11.2. For techniques to use in previous versions, see Oracle-base: string aggregation techniques.
  • I need to skip virtual columns, but keep “invisible” columns. Notice that invisible columns don’t have a column_id! That’s why I use internal_column_id.
  • I use the “quoted literal” starting with “q” because my SQL templates often contain single quotes and they are more legible if the quotes are not doubled.

We can simplify this a bit more by creating a stored function that takes a template, a list of placeholders and a list of values, then returns the SQL code. Update: The package is now declared with “invoker’s rights”, as most generic tools should be.

create or replace package multi_replace AUTHID CURRENT_USER as

  function to_varc(
    p_template in varchar2,
    p_replace_in in sys.odcivarchar2list,
    p_replace_with in sys.odcivarchar2list
  ) return varchar2;

  function to_clob(
    p_template in varchar2,
    p_replace_in in sys.odcivarchar2list,
    p_replace_with in sys.odcivarchar2list
  ) return clob;

end multi_replace;
/

create or replace package body multi_replace as

  procedure check_input(
    p_template in varchar2,
    p_replace_in in sys.odcivarchar2list,
    p_replace_with in sys.odcivarchar2list
  ) is
  begin
    if p_template is null then
      raise_application_error(-20000, 'Parameter p_template must not be null');
    end if;
    if p_replace_in is null then
      raise_application_error(-20001, 'Parameter p_replace_in must not be null');
    end if;
    if p_replace_with is null then
      raise_application_error(-20002, 'Parameter p_replace_with must not be null');
    end if;
    if p_replace_in.count != p_replace_with.count then
      raise_application_error(-20003,
        'Parameters p_replace_in and p_replace_with must have the same number of elements');
    end if;
  end check_input;

  function to_varc(
    p_template in varchar2,
    p_replace_in in sys.odcivarchar2list,
    p_replace_with in sys.odcivarchar2list
  ) return varchar2 is
    l_template varchar2(4000) := p_template;
  begin
    check_input(p_template, p_replace_in, p_replace_with);
    for i in 1..p_replace_in.count loop
      l_template := replace(l_template, p_replace_in(i), p_replace_with(i));
    end loop;
    return l_template;
  end to_varc;

  function to_clob(
    p_template in varchar2,
    p_replace_in in sys.odcivarchar2list,
    p_replace_with in sys.odcivarchar2list
  ) return clob is
    l_template clob := p_template;
  begin
    check_input(p_template, p_replace_in, p_replace_with);
    for i in 1..p_replace_in.count loop
      l_template := replace(l_template, p_replace_in(i), p_replace_with(i));
    end loop;
    return l_template;
  end to_clob;
end multi_replace;
/

Using the function to_varc, our code is a bit shorter and we don’t have to worry about nesting all those REPLACE functions.

with input as (
  select 'T_SOURCE' new_table, 'T_TARGET' old_table from dual
)
, calculated_values as (
  select listagg(column_name, ',')
    within group(order by internal_column_id) col_list
  from user_tab_cols, input
  where table_name = old_table
  and virtual_column = 'NO'
)
select multi_replace.to_varc(
q'
select sum(OLD_CNT) OLD_CNT, sum(NEW_CNT) NEW_CNT
FROM (
  select #COL_LIST#,
    1 OLD_CNT, 0 NEW_CNT
  from #OLD_TABLE# o
  union all
  select #COL_LIST#,
    0 OLD_CNT, 1 NEW_CNT
  from #NEW_TABLE# n
)
group by #COL_LIST#
having sum(OLD_CNT) <> sum(NEW_CNT);>',
sys.odcivarchar2list('#COL_LIST#','#OLD_TABLE#','#NEW_TABLE#'),
sys.odcivarchar2list(COL_LIST,OLD_TABLE,NEW_TABLE)
) sql_text
from input, calculated_values;

My next post will continue the “compare and sync” series: I’ll use the GROUP BY comparison as input to a MERGE statement.
P.S. If you want to use some of this code and you have problems, leave a comment and I’ll help if I can. Please give your Oracle version number, say what you were trying to do and exactly what error you received.

About these ads

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s