Oracle query

If I want to try and find a keyword in a table but dont know which column this keyword would exist in, is there a way for me to run a query that would find this keyword in the table? Im not sure if you would have to query system tables or what but I just want to know if there is a way to query every column for a keyword in Oracle and if so what would that query look like?

Thanks
JK
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
This will be VERY resource intensive but it can be done.

Based off a query here:
http://www.experts-exchange.com/Database/Oracle/Q_24096252.html#a23502848

Something like this.  Just change the where clause to look for ALL tables you want.  I added a where clause to just look at the two tables I created.

When prompted, enter an a or z to see it in action.

drop table my_tab1 purge;
drop table my_tab2 purge;

create table my_tab1(col1 char(1), col2 char(1));
create table my_tab2(col1 char(1), col2 char(1));

insert into my_tab1 values('a','z');
insert into my_tab2 values('z','a');
commit;

select table_name, column_name, 
	case when to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(' || column_name || ') X from '||
	table_name || ' where lower(' ||  column_name || ') = ''&some_value'''))
          ,'/ROWSET/ROW/X')) > 0 then 'FOUND' else 'NOT FOUND' end result
from all_tab_columns
where table_name like 'MY_TAB%'
/

Open in new window

0
sdstuberCommented:
simple, way

  SELECT    'select * from '
         || t.table_name
         || ' where '
         || RTRIM(
                XMLQUERY(
                    '//x/text()'
                    PASSING XMLAGG(XMLELEMENT("x", c.column_name || '||')) RETURNING CONTENT
                ),
                '|'
            )
         || ' like ''%'
         || 'YOUR_KEY_WORD'
         || '%'''
    FROM all_tables t, all_tab_cols c
   WHERE t.owner = c.owner
     AND t.table_name = c.table_name
     AND c.data_type IN ('CHAR', 'VARCHAR2')
     AND t.owner = 'YOUR_SCHEMA'
     AND t.table_name = 'YOUR_TABLE'
GROUP BY t.table_name;

use that to generate your query, then run that query
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
Slight, I wasnt able to run the query you sent me. I got this error
---------
ORA-19202: Error occurred in XML processing
ORA-00932: inconsistent datatypes: expected - got CLOB
ORA-06512: at "SYS.DBMS_XMLGEN", line 176
ORA-06512: at line 1
19202. 00000 -  "Error occurred in XML processing%s"
*Cause:    An error occurred when processing the XML function
*Action:   Check the given error message and fix the appropriate problem
------------

SDStuber - This worked great. Pretty slick...
Im gonna look this up but I was wondering if you could explain the portion below to me real quick...
RTRIM(
                 XMLQUERY(
                     '//x/text()'
                     PASSING XMLAGG(XMLELEMENT("x", c.column_name || '||')) RETURNING CONTENT
                 ),

Thanks and I have to give sdStuber the points but thanks to you both...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jknj72Author Commented:
Thanks
0
sdstuberCommented:
working from the inside out...

let's say my table has 3 varchar2 columns:  A, B, C



(XMLELEMENT("x", c.column_name || '||')

this creates an xml tag called <x> for each row returned by the query and I append the concatenation operator to each column name
So, I get the following 3 rows

<x>A||</x>
<x>B||</x>
<x>C||</x>

Open in new window


XMLAGG(...)

xmlagg aggregates invidual xml nodes into a single xmltype value

So my previous 3 rows become a single xml snippet

<x>A||</x><x>B||</x><x>C||</x>

Open in new window


 XMLQUERY('//x/text()' PASSING ... RETURNING CONTENT),

XMLQUERY extracts the xpath expression for the given xml
//x/text()  means pull the text out of each x node wherever they may be
So I get this...

A||B||C||

Open in new window


RTRIM(...)

Since I added the concatenation operator to every column, I end up with one extra at the end, RTRIM lets me remove it  thus yielding my final result

A||B||C

Open in new window

0
sdstuberCommented:
also note,  including all_tables in my query wasn't really necessary, all of the info needed is in all_tab_cols
  SELECT    'select * from '
         || c.table_name
         || ' where '
         || RTRIM(
                XMLQUERY(
                    '//x/text()'
                    PASSING XMLAGG(XMLELEMENT("x", c.column_name || '||')) RETURNING CONTENT
                ),
                '|'
            )
         || ' like ''%'
         || 'YOUR_KEY_WORD'
         || '%'''
    FROM all_tab_cols c
   WHERE c.data_type IN ('CHAR', 'VARCHAR2')
     AND c.owner = 'YOUR_SCHEMA'
     AND c.table_name = 'YOUR_TABLE'
GROUP BY c.table_name;

Open in new window



and, I intentionally left out CLOBs from my list of data types for efficiency, but you can add them back in
just change this:

      c.data_type IN ('CHAR', 'VARCHAR2')

to this

      c.data_type IN ('CHAR', 'VARCHAR2','CLOB')
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.