Solved

Oracle query

Posted on 2014-12-05
6
630 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 77

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 500 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
Setting up LaraDock for Laravel

Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

615 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