Solved

Oracle query

Posted on 2014-12-05
6
599 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 76

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 73

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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Closing Comment

by:jknj72
ID: 40483377
Thanks
0
 
LVL 73

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 73

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now