jknj72
asked on
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
Thanks
JK
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
---------
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...
ASKER
Thanks
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
XMLAGG(...)
xmlagg aggregates invidual xml nodes into a single xmltype value
So my previous 3 rows become a single xml snippet
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...
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
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>
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>
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||
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
also note, including all_tables in my query wasn't really necessary, all of the info needed is in all_tab_cols
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')
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;
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')
Based off a query here:
https://www.experts-exchange.com/questions/24096252/how-do-i-get-table-name-and-no-of-records.html?anchorAnswerId=23502848#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.
Open in new window