Solved

Oracle query

Posted on 2014-12-05
6
616 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query display the latest row 10 53
oracle query 3 26
SQL query 7 15
SQL Syntax Question 9 22
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

749 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