Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle query

Posted on 2014-12-05
6
Medium Priority
?
648 Views
Last Modified: 2014-12-05
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
0
Comment
Question by:jknj72
  • 3
  • 2
6 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40483025
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40483044
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
 

Author Comment

by:jknj72
ID: 40483376
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:jknj72
ID: 40483377
Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 40483438
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 40483457
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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question