HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
Merge template for table based on data dictionary
ORACLE STUFFS
18-Aug-2008
Usefull generator of merge statement for specified table. It is worth to use it to create initial data upsert script. Script gathers data dictionary metadata about table including primary or unique key information to propose matching criteria.

--Script returns template for constant value Update/Insert using Merge. 
--Owner and Table name are required as parameters.
--Returned values will be passed through DBMS_OUTPUT. 
SET SERVEROUTPUT ON
Declare
  c_table_owner Varchar2(30):=NVL('&owner_or_empty',USER);
  c_table_name Varchar2(30):='&table_name';
 -----------------------
  TYPE ta_v30  is table of VARCHAR2(30);
  TYPE ta_FLAG is table of CHAR(1);
  va_cols ta_v30 ;
  va_pk   ta_FLAG;
  v_constraint_name Varchar2(30):='#';
  v_delim varchar2(4);
Begin
  Begin
    select min(constraint_name)
     keep(DENSE_RANK FIRST 
       order by translate(constraint_type,'PU','12'),constraint_name)
     into v_constraint_name
     from all_constraints    
     where table_name=c_table_name 
     and owner=c_table_owner 
     and constraint_type in ('P','U');
  Exception
     when NO_DATA_FOUND then null;
  End;
  Select a.column_name
      ,case when b.position is null then 'N' else 'Y' end
    bulk collect into va_cols,va_pk  
    from all_tab_columns a
    left join all_cons_columns b 
      on b.owner=a.owner 
      and b.table_name=a.table_name 
      and b.column_name=a.column_name 
      and b.constraint_name=v_constraint_name 
    where a.table_name=c_table_name and a.owner=c_table_owner 
    order by COLUMN_ID;
  DBMS_OUTPUT.ENABLE(1000000);
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('MERGE INTO '||c_table_owner||'.'||c_table_name||' TRG$');
  DBMS_OUTPUT.PUT_LINE('USING ( SELECT');
  v_delim:=' ';
  for i in 1..va_cols.count LOOP
    DBMS_OUTPUT.PUT_LINE('  '||v_delim||'as '||va_cols(i));
    v_delim:=',';
  end LOOP; 
  DBMS_OUTPUT.PUT_LINE('FROM DUAL) SRC$');
  DBMS_OUTPUT.PUT_LINE('ON(');
  v_delim:='   ';
  for i in 1..va_cols.count LOOP
    if va_pk(i)='Y' then
      DBMS_OUTPUT.PUT_LINE('  '||v_delim||' TRG$.'||va_cols(i)||'=SRC$.'||va_cols(i));
      v_delim:='AND';
    end if;
  end LOOP; 
  DBMS_OUTPUT.PUT_LINE(')');
  DBMS_OUTPUT.PUT_LINE('WHEN MATCHED THEN UPDATE SET');
  v_delim:=' ';
  for i in 1..va_cols.count LOOP
    if va_pk(i)='N' then
      DBMS_OUTPUT.PUT_LINE('  '||v_delim||'TRG$.'||va_cols(i)||'=SRC$.'||va_cols(i));
      v_delim:=',';
    end if;
  end LOOP; 
  DBMS_OUTPUT.PUT_LINE('WHEN NOT MATCHED THEN INSERT(');
  v_delim:=' ';
  for i in 1..va_cols.count LOOP
    DBMS_OUTPUT.PUT_LINE('  '||v_delim||'TRG$.'||va_cols(i));
    v_delim:=',';
  end LOOP; 
  DBMS_OUTPUT.PUT_LINE(')VALUES(');
  v_delim:=' ';
  for i in 1..va_cols.count LOOP
    DBMS_OUTPUT.PUT_LINE('  '||v_delim||'SRC$.'||va_cols(i));
    v_delim:=',';
  end LOOP; 
  DBMS_OUTPUT.PUT_LINE(');');
End;
/

[Download]


Following variant generates code with differencial clause of update counting with nullability of all columns:

SET SERVEROUTPUT ON 
Declare 
  c_table_owner Varchar2(30):=NVL('&owner_or_empty',USER); 
  c_table_name Varchar2(30):='&table_name'; 
 ----------------------- 
  TYPE ta_v30  is table of VARCHAR2(30);
  TYPE ta_FLAG is table of CHAR(1);
  va_cols ta_v30 ; 
  va_pk   ta_FLAG; 
  v_constraint_name Varchar2(30):='#';
  v_delim varchar2(4);
Begin
  Begin 
    select min(constraint_name) 
     keep(DENSE_RANK FIRST 
       order by translate(constraint_type,'PU','12'),constraint_name) 
     into v_constraint_name 
     from all_constraints 
     where table_name=c_table_name 
     and owner=c_table_owner 
     and constraint_type in ('P','U');
  Exception 
     when NO_DATA_FOUND then null;
  End;
  Select a.column_name 
      ,case when b.position is null then 'N' else 'Y' end 
    bulk collect into va_cols,va_pk 
    from all_tab_columns a 
    left join all_cons_columns b 
      on b.owner=a.owner 
      and b.table_name=a.table_name 
      and b.column_name=a.column_name 
      and b.constraint_name=v_constraint_name 
    where a.table_name=c_table_name and a.owner=c_table_owner 
    order by COLUMN_ID;
  DBMS_OUTPUT.ENABLE(1000000);
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('');
  DBMS_OUTPUT.PUT_LINE('MERGE INTO '||c_table_owner||'.'||c_table_name||' 
TRG$');
  DBMS_OUTPUT.PUT_LINE('USING ( SELECT');
  v_delim:=' ';
  for i in 1..va_cols.count LOOP 
    DBMS_OUTPUT.PUT_LINE('  '||v_delim||'as '||va_cols(i)); 
    v_delim:=',';
  end LOOP;
  DBMS_OUTPUT.PUT_LINE('FROM DUAL) SRC$');
  DBMS_OUTPUT.PUT_LINE('ON('); 
  v_delim:='   '; 
  for i in 1..va_cols.count LOOP 
    if va_pk(i)='Y' then 
      DBMS_OUTPUT.PUT_LINE('  '||v_delim||' TRG$.'||va_cols(i)||'=SRC$.'
||va_cols(i));
      v_delim:='AND'; 
    end if;
  end LOOP;
  DBMS_OUTPUT.PUT_LINE(')');
  DBMS_OUTPUT.PUT_LINE('WHEN MATCHED THEN UPDATE SET');
  v_delim:=' ';
  for i in 1..va_cols.count LOOP 
    if va_pk(i)='N' then 
      DBMS_OUTPUT.PUT_LINE('  '||v_delim||'TRG$.'||va_cols(i)||'=SRC$.'
||va_cols(i));
      v_delim:=','; 
    end if;
  end LOOP;
  DBMS_OUTPUT.PUT_LINE('WHERE --update when'); 
  v_delim:='    '; 
  for i in 1..va_cols.count LOOP 
    if va_pk(i)='N' then 
      DBMS_OUTPUT.PUT_LINE('  '||v_delim||'((TRG$.'||va_cols(i)||' is not null or SRC$.'||va_cols(i)||' is not null)'); 
      DBMS_OUTPUT.PUT_LINE('  '||'      and '||'LNNVL(TRG$.'||va_cols(i)||
' = SRC$.'||va_cols(i)||'))'); 
      v_delim:=' or '; 
    end if;
  end LOOP;
  DBMS_OUTPUT.PUT_LINE('WHEN NOT MATCHED THEN INSERT(');
  v_delim:=' ';
  for i in 1..va_cols.count LOOP 
    DBMS_OUTPUT.PUT_LINE('  '||v_delim||'TRG$.'||va_cols(i)); 
    v_delim:=',';
  end LOOP;
  DBMS_OUTPUT.PUT_LINE(')VALUES(');
  v_delim:=' ';
  for i in 1..va_cols.count LOOP 
    DBMS_OUTPUT.PUT_LINE('  '||v_delim||'SRC$.'||va_cols(i)); 
    v_delim:=',';
  end LOOP;
  DBMS_OUTPUT.PUT_LINE(');');
End;
/

[Download]
Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky