Specifically, I am trying to find any credit card numbers that have been typed into text fields of any table in the database. This code below works for a single table where I know the primary key is "ID", but I cannot find a way to make this dynamic so that I can loop through many hundreds of table and rows.
drop table tab1 purge;
create table tab1(id number primary key, col1 varchar2(20), col2 varchar2(20), col3 varchar2(20));
Insert into TAB1 Values (1, 'No card here', 'Hello', 'nothing');
Insert into TAB1 Values (2, '1111222233334444', 'Visa', 'Hello');
Insert into TAB1 Values (3, 'Hello', 'MasterCard', '1111 2222 3333 4444');
Insert into TAB1 Values (4, 'Hello', '1111-2222-3333-4444', 'Visa');
Insert into TAB1 Values (5, 'Amex', 'Hello', '1111 222222 33333');
Insert into TAB1 Values (6, '111122222233333', 'Amex', 'Hello');
Insert into TAB1 Values (7, 'nothing', 'No card here', 'Hello');
COMMIT;
declare
myrow clob;
begin
for i in (select id from tab1) loop
select dbms_xmlgen.getxml('select * from tab1 where id=' || i.id) into myrow from dual;
if regexp_instr(myrow,'[0-9]{4}[ -]?[0-9]{4}[ -]?[0-9]{4}[ -]?[0-9]{4}|[0-9]{4}[ -]?[0-9]{6}[ -]?[0-9]{5}') > 0 then
dbms_output.put_line('May have found one: ' || to_char(i.id));
end if;
end loop;
end;
