Query all tables of Oracle database: return all records that hold string

Dear Experts,

I need to select all records that contain a string in all tables of the database.
I find quite a few entries on the internet and also in EE, but none "works" for me.

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production.
How can we I get started on that?

Thanks for your help.
W.
WatnogAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Sorry for the long delay in posting.  I was tied up all afternoon.

First disclaimer:  What you have asked for is very inefficient and the method I'm about to post is a VERY INEFFICIENT way to do it.  If your requirement is to do this sort of thing on a regular basis and you can about double the size of your database, I can suggest an much more efficient but more complex method and possibly more flexible approach.



I had to tweak the example from the link.  It looked in a single table and it knew the PK column.  To search ANY table and ANY columns, I had to tweak it.

The sample search string you posted was UPPER case.  I didn't know if you only wanted that or any case match.  Both options are available.

If you want any case match (you want tab1 id 4 returned), comment out:
      if regexp_instr(myrowdata,'MAINT') > 0 then
and comment in:
      if regexp_instr(myrowdata,'MAINT',1,1,0,'i') > 0 then

Second disclaimer:
This example assumes you NEVER have a column name with MAINT in it.  Otherwise it will hit on EVERY SINGLE ROW.  If you need to account for that possibility, ask.  The solution will be even worse performing but it is possible.

If you want more than just the rowids, it is another select and likely more resources depending of what you want to display.

If you want to store the data, replace the DBMS_OUTPUT with an INSERT statement.

Third disclaimer:  Depending on how long you need this data to be accurate, under certain circumstances, ROWID's can change for a specific row.  So, what you generate now might not be valid later.

Anyway, here is my latest test case:
/*
drop table tab1 purge;
create table tab1(id number, col1 varchar2(20), col2 varchar2(20));

insert into tab1 values(1,'Hello','World');
insert into tab1 values(2,'MAINT','World');
insert into tab1 values(3,'Hello','MAINT');
insert into tab1 values(4,'Maint','mainT');

drop table tab2 purge;
create table tab2(id number, col1 varchar2(20), col2 varchar2(20));

insert into tab2 values(5,'Hello','World');
insert into tab2 values(6,'MAINT','World');
commit;
*/


declare
	myrowdata clob;
	mycur sys_refcursor;
	myrowid rowid;
begin
	for q in (select table_name from user_tables where table_name in ('TAB1','TAB2')) loop
		open mycur for 'select rowid from ' || q.table_name;
		loop
			fetch mycur into myrowid;
			exit when mycur%NOTFOUND;
			select dbms_xmlgen.getxml('select * from ' || q.table_name || ' where rowid=''' || myrowid || '''') into myrowdata from dual;
			if regexp_instr(myrowdata,'MAINT') > 0 then
			--if regexp_instr(myrowdata,'MAINT',1,1,0,'i') > 0 then
				dbms_output.put_line('Found one in ' || q.table_name || ' rowid: ' || myrowid);
			end if;
		end loop;
	end loop;
end;
/

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Let's make sure I understand:
You want to look through ALL columns in ALL tables for a string?

Can you skip BLOB, LPNG, RAW and other obvious non-string columns?

If that is what you are after, I hope you don't expect it to be fast.  It will be very resource intensive.
0
 
slightwv (䄆 Netminder) Commented:
If you don't need to know what column it is in, here is something I came up with a few years ago that might work for you:
https://www.experts-exchange.com/questions/28499834/I-need-to-find-credit-card-numbers-hidden-in-text-columns-using-Oracle-11g-and-SQLPlus.html#a40267919

It selects all columns into a CLOB and then looks in the CLOB for a string.  It uses regular expression because they were looking for a specific pattern.  You may or may not need that.  Depending of your exact requirements, it might be faster to lower case the CLOB and use LIKE to look for the string.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
WatnogAuthor Commented:
All tables: all tables that would be relevant, good you point that out.
Actually I can list the tables, there are 104 of them.
0
 
WatnogAuthor Commented:
The code in the page you point to, is about finding numbers, correct?
0
 
Mark GeerlingsDatabase AdministratorCommented:
Yes, that example from slightwv was specifically written to find credit card numbers.  But, you just need to change this line in his example:
if regexp_instr(myrow...

to something like this:
if myrow like '%[your_search_string]%'...

We don't know which characters you are looking for, or whether they may be upper-case, lower case or mixed-case.  So, you may need to do an upper (or lower) case conversion on both [your_search_string] and on the data in the database.

Be aware that Oracle is certainly not optimized to search through ALL text columns of ALL tables for a particular character string.  Then, if you need to do an upper or lower case conversion on everything, that will make it even slower.
0
 
johnsoneSenior Oracle DBACommented:
I wouldn't change REGEXP_INSTR to LIKE.  I'm not sure how LIKE would react to things that exceed the limits of VARCHAR2.

Also, REGEXP_INSTR is capable of case insensitive searching.

If you wanted to avoid the overhead of regular expressions, I would go with DBMS_LOB.INSTR, it would certainly be efficient at searching a CLOB.  But I don't believe that is capable of case insensitive searching.
0
 
slightwv (䄆 Netminder) Commented:
>>Actually I can list the tables, there are 104 of them

This is untested but it should be something like:
select dbms_xmlgen.getxml('select * from ' || table_name) into myrow from user_tables where table_name in ('TAB1','TAB2','TAB3');

You can add to the individual table where clause from the example in the other question if you have specific rows in those tables you want.  Since you seem to want everything, I removed it in the code above.
0
 
WatnogAuthor Commented:
Thanks but I can't figure it out...

Let's get back a bit.
There is this code to re-use:

drop table tab1 purge;

create table tab1(id number primary key, col1 varchar2(20), col2 varchar2(20));


insert into tab1 values(1,'No card here','Hello');
insert into tab1 values(2,'1111222233334444','Hello');
insert into tab1 values(3,'Hello','1111 2222 3333 4444');
insert into tab1 values(4,'Hello','1111-2222-3333-4444');
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 then
			dbms_output.put_line('May have found one: ' || to_char(i.id));
		end if;
	end loop;
end;
/

Open in new window


What I need is that tables are searched and that records that have records holding string like %MAINT% are returned.
I would be nice if I had the table name the records are found in and I can indeed specify the table names to be searched in.

Thanks and cheers.
Watnog
0
 
WatnogAuthor Commented:
Thank you for your work,
When I run the query I receive this message: "anonymous block completed"
Nothing in dbms_output either, "set serveroutput ON" doesn't work.
Sorry for being a pain.

Watnog
0
 
slightwv (䄆 Netminder) Commented:
Can you try using sqlplus?

If not, what tool are you using to execute it?
0
 
WatnogAuthor Commented:
Thank you.
In the end I walked other ways to find the data I need.
I appreciate your help and support.
0
 
Mark GeerlingsDatabase AdministratorCommented:
Are you willing to share the "other way" that you used to find the data?  If you do, that may help someone else later.
0
 
slightwv (䄆 Netminder) Commented:
Yes, please share how you solved this problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.