create PROCEDURE findCountryData(HeaderGuid IN VARCHAR2)
IS
v_found_it NUMBER;
BEGIN
DBMS_OUTPUT.put_line('start');
FOR x
IN ( SELECT owner, table_name, column_name
FROM all_tab_columns
WHERE data_type not IN ('NUMBER', 'DATE', 'CLOB', 'NCLOB')
AND owner IN ('IEMINT2')
– and TABLE_NAME = ‘CTCOUNTRYCODE’
and COLUMN_NAME not in (‘GUID’, ‘STCREATEDBY’, ‘STUPDATEDBY’, ‘STDATASTATUS’)
and COLUMN_NAME not like ‘GUID’
and TABLE_NAME not in
(‘COUNTRY_DATA’, ‘COUNTRY_DATA_TABLE_NAME’, ‘IFACELOG’, ‘STEXCELQUEUE’, ‘STEXPORTQUEUE’, ‘STJOB’,
‘STLOG’, ‘STLOGS’, ‘STNUMBERRANGE’, ‘STNUMBERRANGEMODELCONFIG’, ‘STPARAM’)
and TABLE_NAME not like ‘%BAK’
and TABLE_NAME not like 'QRTZ%’
and TABLE_NAME not like ‘R_%’
and TABLE_NAME not like ‘%_TEMP’
and TABLE_NAME not in (select distinct TABLE_NAME from COUNTRY_DATA_TABLE_NAME)
ORDER BY owner, table_name, column_name)
LOOP
EXECUTE IMMEDIATE
'select count(0) from "'
|| x.owner
|| '"."'
|| x.table_name
|| '" where "'
|| x.column_name
|| '" like ''%'
|| 'XXXXXXXX'
|| '%'' '
|| ' and rownum = 1'
INTO v_found_it;
-- merge into COUNTRY_DATA_TABLE_NAME dest using(
– select x.table_name “TABLE_NAME” from dual
– ) source on (dest.TABLE_NAME = source.TABLE_NAME)
– when not matched then
– insert (table_name, dtcreatedtime) values (x.table_name, sysdate);
insert into COUNTRY_DATA_TABLE_NAME values (x.TABLE_NAME, x.COLUMN_NAME, sysdate);
IF v_found_it > 0
THEN
insert into COUNTRY_DATA values (x.table_name, x.column_name);
DBMS_OUTPUT.put_line(
'"' || x.owner || '"."' || x.table_name || '"."' || x.column_name || '"'
);
END IF;
commit;
END LOOP;
DBMS_OUTPUT.put_line('end');
END;
/