oracle對非使用繫結變數的語句去重

透過如下過程可以對非使用繫結變數的語句去重
create or replace procedure query_sql is      
cursor fms is select FORCE_MATCHING_SIGNATURE as fms, count(1) as count
  from sys.LARGE2S_TABLE
 where FORCE_MATCHING_SIGNATURE > 0
   and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
 group by FORCE_MATCHING_SIGNATURE
having count(1) > 100
 order by 2; 
v_fms number;
v_sql01 varchar2(3999);
v_sql02 varchar2(3999);
begin
for i in fms loop
v_sql01:='insert into shsnc.long_sql(sql_id,FORCE_MATCHING_SIGNATURE,sql_text)  select sql_id,FORCE_MATCHING_SIGNATURE,sql_fulltext from (select sql_id,FORCE_MATCHING_SIGNATURE,sql_fulltext from sys.LARGE2S_TABLE where FORCE_MATCHING_SIGNATURE='||i.fms||' and sql_id  is not null and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from shsnc.long_sql)) where rownum<2';
v_sql02:='update shsnc.long_sql set count='||i.count ||' where FORCE_MATCHING_SIGNATURE='||i.fms;
execute immediate v_sql01;
commit;
execute immediate v_sql02;
commit;
end loop;
end;
/