Compare and sync tables: Generating the code

[Update 2015-01-04: I now get the primary key columns from ALL_CONS_COLUMNS, not ALL_IND_COLUMNS.]
[Update 2014-11-29: I simplified the code a bit and changed the C## prefix to Z##. Oracle uses the C## prefix for “common users” in CDBs, and I wanted to avoid any confusion.]

(Here is a list of my posts about comparing and synchronizing tables.)

To continue my presentation of the GROUP BY + MERGE technique, here is a query that generates the SQL to synchronize two tables. You just need to specify the source and the target; the source can be a local or remote table or view, or a query. The target must be a local table (or view!) with a primary key.

The starting point is the MERGE statement presented at the end of my previous post.

merge into T_TARGET o
using (
  select * from (
    select KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING,
    count(*) over(partition by KEY_NUM, KEY_DATE)
      - sum(Z##CNT) Z##IUD_FLAG
    FROM (
      select KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING,
        -1 Z##CNT
      from T_TARGET o
      union all
      select KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING,
        1 Z##CNT
      from T_SOURCE n
    )
    group by KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING
    having sum(Z##CNT) != 0
  )
  where Z##IUD_FLAG < 3
) n
on (o.KEY_NUM = n.KEY_NUM and o.KEY_DATE = n.KEY_DATE)
when matched then update
  set VAL_TS = n.VAL_TS, VAL_STRING = n.VAL_STRING
  delete where n.Z##IUD_FLAG = 2
when not matched then insert
  (KEY_NUM, KEY_DATE, VAL_TS, VAL_STRING)
  values(n.KEY_NUM, n.KEY_DATE, n.VAL_TS, n.VAL_STRING);

As I mentioned in Generating SQL with SQL templates, we need to identify the elements that change and represent them with placeholders:

• ‘#OLD_TABLE#’: the target table name, with or without the preceding owner name
• ‘#ALL_COLS#’: a list of all the non-virtual columns, taken from the target table
• ‘#PK_COLS#’: a list of all the columns in the target primary key
• ‘#NEW_SOURCE#’: the table, view – or query in parentheses – representing the source
• ‘#ON_COLS#’: the primary key columns, used in the ON clause of the MERGE statement
• ‘#SET_COLS#’: the columns not in the primary key, used in the SET clause
• ‘#INSERT_COLS#’: a list of all the columns used in the VALUES clause of the INSERT

Using as input the “old” table name (and optionally the “old” owner name), we can get the column names from the ALL_TAB_COLS view. The primary key columns come from the ALL_CONS_COLUMNS view, once we get the name of the primary key index – which is available in the ALL_CONSTRAINTS view.

VARIABLE P_OLDOWNER varchar2(30)
VARIABLE P_OLDTABLE varchar2(30)
VARIABLE P_NEWSOURCE varchar2(256)
EXEC :P_OLDTABLE := 'T_TARGET';
EXEC :P_NEWSOURCE := 'T_SOURCE';

with INPUT as (
  select UPPER(NVL(:P_OLDOWNER, user)) OLD_OWNER,
  UPPER(:P_OLDTABLE) OLD_TABLE_NAME,
  :P_NEWSOURCE NEW_SOURCE,
  UPPER(NVL2(:P_OLDOWNER, :P_OLDOWNER || '.' || :P_OLDTABLE, :P_OLDTABLE)) OLD_TABLE
  from DUAL
)
, TAB_COLS as (
  select COLUMN_NAME, INTERNAL_COLUMN_ID COLUMN_ID
  from ALL_TAB_COLS, INPUT
  where (OWNER, TABLE_NAME) = ((OLD_OWNER, OLD_TABLE_NAME))
)
, KEY_COLS as (
  select COLUMN_NAME, POSITION
  from ALL_CONS_COLUMNS, INPUT
  where (OWNER, CONSTRAINT_NAME) = (
    select OWNER, CONSTRAINT_NAME from ALL_CONSTRAINTS
    where (OWNER, TABLE_NAME, CONSTRAINT_TYPE) = ((OLD_OWNER, OLD_TABLE_NAME, 'P'))
  )
)
, COL_LIST as (
  select LISTAGG(COLUMN_NAME,',') within group(order by COLUMN_ID) ALL_COLS,
  LISTAGG('n.' || COLUMN_NAME,',') within group(order by COLUMN_ID) INSERT_COLS
  from TAB_COLS
)
, PK_LIST as (
  select LISTAGG(COLUMN_NAME,',') within group(order by POSITION) PK_COLS,
  LISTAGG('o.'||COLUMN_NAME||'=n.'||COLUMN_NAME,' and ')
    within group(order by POSITION) ON_COLS
  from KEY_COLS
)
, SET_LIST as (
  select LISTAGG(COLUMN_NAME || '=n.'||COLUMN_NAME,',')
    within group(order by COLUMN_ID) SET_COLS
  from TAB_COLS
  where COLUMN_NAME not in (select COLUMN_NAME from KEY_COLS)
)
select MULTI_REPLACE.TO_VARC(
'merge into #OLD_TABLE# O
using (
  select * from (
    select #ALL_COLS#,
    COUNT(*) over(partition by #PK_COLS#)
      - SUM(Z##_CNT) Z##IUD_FLAG
    from (
      select #ALL_COLS#,
        -1 Z##_CNT
      from #OLD_TABLE# O
      union all
      select #ALL_COLS#,
        1 Z##_CNT
      from #NEW_SOURCE# N
    )
    group by #ALL_COLS#
    having SUM(Z##_CNT) != 0
  )
  where Z##IUD_FLAG < 3
) N
on (#ON_COLS#)
when matched then update
  set #SET_COLS#
  delete where N.Z##IUD_FLAG = 2
when not matched then insert
  (#ALL_COLS#)
  values(#INSERT_COLS#)',
SYS.ODCIVARCHAR2LIST('#OLD_TABLE#','#ALL_COLS#','#PK_COLS#',
  '#NEW_SOURCE#','#ON_COLS#','#SET_COLS#','#INSERT_COLS#'),
SYS.ODCIVARCHAR2LIST(  OLD_TABLE,    ALL_COLS,    PK_COLS,
    NEW_SOURCE,    ON_COLS,    SET_COLS,    INSERT_COLS)
) SQL_TEXT
from INPUT, COL_LIST, PK_LIST, SET_LIST;

Here is the result, ready to execute:

merge into T_TARGET o
using (
  select * from (
    select KEY_NUM,KEY_DATE,VAL_TS,VAL_STRING,
    count(*) over(partition by KEY_NUM,KEY_DATE)
      - sum(Z##_CNT) Z##IUD_FLAG
    FROM (
      select KEY_NUM,KEY_DATE,VAL_TS,VAL_STRING,
        -1 Z##_CNT
      from T_TARGET o
      union all
      select KEY_NUM,KEY_DATE,VAL_TS,VAL_STRING,
        1 Z##_CNT
      from T_SOURCE n
    )
    group by KEY_NUM,KEY_DATE,VAL_TS,VAL_STRING
    having sum(Z##_CNT) != 0
  )
  where Z##IUD_FLAG < 3
) n
on (o.KEY_NUM=n.KEY_NUM and o.KEY_DATE=n.KEY_DATE)
when matched then update
  set VAL_TS=n.VAL_TS,VAL_STRING=n.VAL_STRING
  delete where n.Z##IUD_FLAG = 2
when not matched then insert
  (KEY_NUM,KEY_DATE,VAL_TS,VAL_STRING)
  values(n.KEY_NUM,n.KEY_DATE,n.VAL_TS,n.VAL_STRING);

Limitations and disclaimer

There are some situations that this utility does not cover: the target table may not have a primary key, or it may be in a remote database; also, some data types such as LOBs cannot be “grouped by”. There are some possible workarounds that would require enhancements to what I’ve presented here.

Above all, please be careful! All this tool does is generate some SQL code; please review and test the generated code carefully before using it on real data! I cannot share any responsibility for changes you make to your data; once the SQL is generated, it is yours and yours alone. To use or not to use: that is your question.

Of course, if you find any bugs in the tool, please leave a reply with details so I can correct my mistakes. Thanks in advance!

Advertisement