Uniqualize rows of any table using ora_te

One of the cool ways to use my small template engine for Oracle (ora_te).

procedure uniqualize_rows_no_commit( a_table_name in varchar2 ) as 
  v_clob clob;
  v_te_uniq ty_te;
begin
  v_te_uniq := ty_te.compile_named( q'#
  delete from {$table_name} 
  where rowid in ( 
    select rowid 
    from (
      select rowid
        , row_number() over ( partition by {%for cur:1|join(',')%}{$column_name}{%endfor%} order by null ) rn
      from {$table_name}
    ) 
    where 1 < rn
  )
#'
  );  
  
  select pk_te.substitute( 
    v_te_uniq
    , ty_m( ty_p('table_name', a_table_name ) )
    , cursor( select ty_m( ty_p( 'column_name', column_name ) ) from user_tab_columns where table_name like upper( a_table_name ) ) 
  )
  into v_clob
  from dual;
--  pk_utils.eat( pk_utils.dbms_output_clob( v_clob ) );

  execute immediate v_clob;
end;

Leave a Reply

Your email address will not be published. Required fields are marked *