One of the tricky questions asked in almost all the Oracle forums “How to search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA”. One such question is asked in Stack Overflow http://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle. The most common answer is using PL/SQL to loop through all the column_name in ALL_TAB_COLUMNS view to check for the given value.
It could be done in a single SQL statement. Let’s look at the SQL solution.
Search for the value KING in SCOTT schema.
SQL> variable val varchar2(10)
SQL> exec :val := 'KING'
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
2 SUBSTR (table_name, 1, 14) "Table",
3 SUBSTR (column_name, 1, 14) "Column"
4 FROM cols,
5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
6 || column_name
7 || ' from '
8 || table_name
9 || ' where upper('
10 || column_name
11 || ') like upper(''%'
12 || :val
13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
14 ORDER BY "Table"
15 /
Searchword Table Column
----------- -------------- --------------
KING EMP ENAME
SQL>
Search for the value 20 in SCOTT schema.
SQL> variable val NUMBER
SQL> exec :val := 20
PL/SQL procedure successfully completed.
SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
2 SUBSTR (table_name, 1, 14) "Table",
3 SUBSTR (column_name, 1, 14) "Column"
4 FROM cols,
5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
6 || column_name
7 || ' from '
8 || table_name
9 || ' where upper('
10 || column_name
11 || ') like upper(''%'
12 || :val
13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
14 ORDER BY "Table"
15 /
Searchword Table Column
----------- -------------- --------------
20 DEPT DEPTNO
20 EMP DEPTNO
20 EMP HIREDATE
20 SALGRADE HISAL
20 SALGRADE LOSAL
SQL>
Credit : Michaels
Hope it helps!
Lalit Kumar B 
Hi Lalit,
Thanks for this query. It helped me a lot.
I tried to find out columns having NULL values in all my tables but, couldn’t get with this query. Can you please help? I saw your solution posted on 25-Sep-2014 for the same but, I am looking for the one with out creating a function.
Thanks,
Babu
Hi,
There is one shortcut way. But you MUST gather statistics first.
Remember to gather statistics before running the SQL.
I have added an example here https://lalitkumarb.wordpress.com/2014/09/25/find-all-columns-having-at-least-a-null-value-from-all-tables-in-the-schema/
hi lalit ,
thanks for the post
i have a problem when i launch it , i got
ERROR at line 1:
ORA-19202: Error occurred in XML processing
ORA-00932: inconsistent datatypes: expected CHAR got LONG
ORA-06512: at “SYS.DBMS_XMLGEN”, line 288
ORA-06512: at line 1
did u know why i got this ?
thanks
@dhibat,
Check this out http://www.oracle-developer.net/display.php?id=430
Hi lalit,
I am getting the below error
ORA-31186: Document contains too many nodes
ORA-06512: at “SYS.XMLTYPE”, line 111
31186. 00000 – “Document contains too many nodes”
*Cause: Unable to load the document because it has exceeded
the maximum allocated number of DOM nodes.
*Action: Reduces the size of the document.
@Sharada,
See https://community.oracle.com/thread/2470912
Im also getting same issue, and im unable to resolve, kindly help
Hi,
Please see the workaround here https://community.oracle.com/thread/2470912
I am getting this error. Any Idea ? I’m using Toad for Oracle.
Variable Val is Number (assume val=1234)
[Error] Execution (2: 15): ORA-00923: FROM keyword not found where expected
My aim is to basically find all the table which have a column called ‘ORDER_NUMBER’ having a value of ‘1234’.
i’m having the same error : From Keyword not found where expected . any help ?
I’m working on a java project that uses a oracle database in netbeans
@Cesar,
Can you post your code. Perhaps you might be having syntax errors. Did you try in the sample SCOTT schema to validate?
SQL> SELECT DISTINCT SUBSTR (10, 1, 11) "Searchword", 2 SUBSTR (table_name, 1, 14) "Table", 3 SUBSTR (column_name, 1, 14) "Column" 4 FROM cols, 5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select ' 6 || column_name 7 || ' from ' 8 || table_name 9 || ' where upper(' 10 || column_name 11 || ') like upper(''%' 12 || 10 13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t 14 ORDER BY "Table" 15 / Se Table Column -- -------------- -------------- 10 DEPT DEPTNO 10 EMP DEPTNO 10 EMP SALHi.
I am running this way below on Toad. And I got the error message:
ORA-00907: missing right parenthesis
VARIABLE VAL VARCHAR2(100)
EXEC :VAL := ‘PHILIPPINES’
SELECT
DISTINCT SUBSTR (:VAL, 1, 11) “SEARCH FOR: “,
SUBSTR (TABLE_NAME, 1, 14) “Table”,
SUBSTR (COLUMN_NAME, 1, 14) “Column”
FROM ALL_TAB_COLUMNS,
TABLE
(
xmlsequence
(
dbms_xmlgen.getxmltype
(‘ SELECT ”COLUMN_NAME” FROM ”TABLE_NAME” WHERE ”COLUMN_NAME” LIKE ”%’:VAL’%” ‘).EXTRACT (‘ROWSET/ROW/*’)
)
) t
ORDER BY “Table”
/
@Pirate X,
Are you sure you do not have any syntax errors? Because it should work without any issues:
SQL> SELECT DISTINCT SUBSTR (10, 1, 11) "Searchword", 2 SUBSTR (table_name, 1, 14) "Table", 3 SUBSTR (column_name, 1, 14) "Column" 4 FROM cols, 5 TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select ' 6 || column_name 7 || ' from ' 8 || table_name 9 || ' where upper(' 10 || column_name 11 || ') like upper(''%' 12 || 10 13 || '%'')' ).extract ('ROWSET/ROW/*') ) ) t 14 ORDER BY "Table" 15 / Se Table Column -- -------------- -------------- 10 DEPT DEPTNO 10 EMP DEPTNO 10 EMP SALI need a similar script to find out the string length.
@kapardhi,
What have you tried so far?
Hi Lalit,
I’m encountering the following error:
TABLE (xmlsequence (dbms_xmlgen.getxmltype (‘select ‘
*
ERROR at line 5:
ORA-19202: Error occurred in XML processing
ORA-00932: inconsistent datatypes: expected NUMBER got SYS.AQ$_SIG_PROP
ORA-06512: at “SYS.DBMS_XMLGEN”, line 288
ORA-06512: at line 1
I am connecting to the database of oracle erp schema. Can you please advise, how to overcome this? Thanks.
The query works pretty much fine in Scott user though.
Regards,
Mukunthan L
Hi,
I am getting the following error while connecting and querying through the oracle erp schema:
TABLE (xmlsequence (dbms_xmlgen.getxmltype (‘select ‘
*
ERROR at line 5:
ORA-19202: Error occurred in XML processing
ORA-00932: inconsistent datatypes: expected NUMBER got SYS.AQ$_SIG_PROP
ORA-06512: at “SYS.DBMS_XMLGEN”, line 288
ORA-06512: at line 1
The Scott user works fine though. Please help. Thanks.
i know the column name here, how can i modify the query.
i have column called ABC in many tables and i want to fetch all the records details having ABC=’1234′
how to modify the above query
Just add the filter in WHERE clause, WHERE column_name = ‘ABC’
This looks like it would work great for what I need, but I need it to exclude tables that have *LOB data types as it errors when it hits them. I tried adding something like “AND DATA_TYPE not like ‘%LOB’ ” into the table area, but I cannot manage to format it correctly so that runs.
Could you provide an example that will skip over *LOB data types?
Thanks!!
This is what I currently get running your search:
SQL> variable val NUMBER
SQL> exec :val := 1147927
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) “Searchword”,
2 SUBSTR (table_name, 1, 14) “Table”,
3 SUBSTR (column_name, 1, 14) “Column”
4 FROM cols,
5 TABLE (xmlsequence (dbms_xmlgen.getxmltype (‘select ‘
6 || column_name
7 || ‘ from ‘
8 || table_name
9 || ‘ where upper(‘
10 || column_name
11 || ‘) like upper(”%’
12 || :val
13 || ‘%”)’ ).extract (‘ROWSET/ROW/*’) ) ) t
14 ORDER BY “Table”
15 /
TABLE (xmlsequence (dbms_xmlgen.getxmltype (‘select ‘
*
ERROR at line 5:
ORA-19202: Error occurred in XML processing
ORA-00932: inconsistent datatypes: expected CHAR got BLOB
ORA-06512: at “SYS.DBMS_XMLGEN”, line 288
ORA-06512: at line 1
facing same error… help please
Hi Lalitkumar,
I need a same requirement but silent change a little bit.
I need to search it based on pattern
For Ex : The table emp has 3 columns name ( varchar2(100),age,address varchar2(300) );
The output should be like the below,
First Pattern ->123456MMM555
Second Pattern ->142456***155
Third Pattern-> 444442—091
First is 6 digit,second is 3 alpha character and third is 3 number. The length is constant.
Need to find like this in the all the columns of the schema.
Thanks
when I run i am getting below error message:
ORA-19202: Error occurred in XML processing
ORA-00904: “SUCCESS”: invalid identifier
ORA-06512: at “SYS.DBMS_XMLGEN”, line 288
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
Hi Lalit Kumar B,
It´s works pretty fine for me, and I want to thank you so much for your help; Now I have one question, I need to search in a schema different than mine. How can I do this?
I really appreciate your answer. Thanks in advanced.
Hi Diana,
To search in a schema different than your own, add a filter to the query AND owner = . ALL_TAB_COLUMNS view has OWNER column which will have the schema_name in which you want to search the string.
Can you elaborate on how to do this?
Pingback: SQL to search a specific value or string in all columns of all tables in an entire schema | Masuk's Blog
Pingback: Tip 051: Database-Wide Text Searches - BareFoot PeopleSoft
Hi i want to search in the same table for a particular value. and need to execute in hive… can u help?
Hi,
Your requirement is unclear to me, could you please elaborate it.
It is running but it keeps running like never ending. Is it obvious with this solution?
Note: Using Oracle SQL Developer to run this.
Please help.
Of course it would depend on lot of factors, like the size of schema, whether statistics are up-to-date, data skewness etc. Check the explain plan and see where does optimizer spends more time.
Getting the error as below: Please help.
Note my search string has the characters / and .
Is it a problem?
Error report –
SQL Error: ORA-19202: Error occurred in XML processing
ORA-00932: inconsistent datatypes: expected CHAR got BLOB
ORA-06512: at “SYS.DBMS_XMLGEN”, line 288
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
You need to filter out the LOB data types. Checkout the datatypes:
Why should I consider only LOB datatype?
I used
SELECT
SUBSTR (table_name, 1, 14) “Table”,
SUBSTR (column_name, 1, 14) “Column”
FROM cols,
TABLE (xmlsequence (dbms_xmlgen.getxmltype (‘select ‘
|| column_name
|| ‘ from ‘
|| table_name
|| ‘ where upper(‘
|| column_name
|| ‘) like upper(”%text%”)’ ).extract (‘ROWSET/ROW/*’) ) ) t
where data_type not like ‘%LOB%’
ORDER BY “Table”;
But I wasn’t successful
ORA-19202: Error occurred in XML processing
ORA-19011: Character string buffer too small
ORA-06512: at “SYS.DBMS_XMLGEN”, line 288
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
Hi Lalit ,
I couldn’t understand the query as a beginner . When I ran the query as it is I am getting error . Could you please tell me what is :val , cols, table in that query . Is there a way to understand that query
Hi,
COLS is a synonym for USER_TAB_COLUMNS. “Table” is an alias to the column in the select list.
Hello Sir Latit Kumar ,
this is what i just need,
it works on my other DB, but while I running it to other DB, I got some error,
it says that I select more that 1 row for single query.
ORA-19202: Error occurred in XML processing
ORA-01427: single-row subquery returns more than one row
ORA-06512: at “SYS.DBMS_XMLGEN”, line 288
ORA-06512: at line 1
Are both database versions same? Is the data also same in both databases?
Hey Lalit. I was trying your query in Oracle and get multiple errors. Is there a way you can help me?
ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at “SYS.DBMS_XMLGEN”, line 288
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
Hi,
“ORA-00942: table or view does not exist” error suggests that you don’t have necessary privilege on the table, or the table might actually be missing. Please rectify that first to debug further.
The query works, Thanks!
variable val varchar2(10) SQL> exec :val := 'yoursearchstring' PL/SQL procedure successfully completed. SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword", SUBSTR (table_name, 1, 14) "Table", SUBSTR (column_name, 1, 14) "Column" FROM cols, TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select ' || column_name || ' from ' || table_name || ' where upper(' || column_name || ') like upper(''%' || :val || '%'')' ).extract ('ROWSET/ROW/*') ) ) t ORDER BY "Table" /You’re welcome!
I have a couple questions based on the query posted. I love how simple it is based on other ways I have seen it done. I have not tried it yet as I am a new developer on the team and I don’t want to stir too much into the pot. My questions are:
1. When it is copied and pasted, what is required and what is not? Meaning is the SQL> required?
2. Do I typed in the PL/SQL procedure part too?
3. Is the dbms_xml part going to be in my schema or is that referencing something else?
4. Does the search for exact matches only or is there a wildcard in there?
I love the work you have done and I see that it does work for most, so thank you for that!
Ryan
1. You just need to set the substitution variable with the value you want to search for:
2. I didn’t get your 2nd question. If you are asking if you need to copy paste the PL/SQL code, then yes you need to.
3. The dbms_xmlgen package is owned by SYS, however, there is a PUBLIC SYNONYM created with same name so you can access it.
4. The substitution variable passes the value to be searched, which is doing a wildcard search using this code:
10 || column_name 11 || ') like upper(''%' 12 || :val 13 || '%'')'Like is using wildcard both sides. If you want to search for specific value, then use “=” operator which will be faster if it has an index on it.
Regards,
Lalit
Lalit,
I sort of got it to work, but I ran into two issues. One is not so bad, but the other doesn’t help me at all. If you can assist, that would be great.
Main issue is:
The xmlsequence ( dbms_xmlgen.getxmltype is only pulling in my tables that I’ve made. Which so far is only one, that’s how I know. I want to search other users, or more specifically one called “LAWSON.” How to do search for the xml of that? I want to target those tables under that “user.”
The second issues is related to the variable not being defined from the one I chose. It always runs without accepting the variable part. A window pops up and I put it in there.
And a quick question… what does the “:” before the val do?
Thanks for all your help!
Ryan
Thank for this, I am getting this error ?
Error at Command Line : 4 Column : 1
Error report –
SQL Error: ORA-00904: : invalid identifier
00904. 00000 – “%s: invalid identifier”
*Cause:
*Action:
I can’t help with just that error message. Edit your comment and post the entire code you are executing.
Thank you for your reply L Kumar
variable val varchar2(10)
exec :val := ‘Ash’
SELECT DISTINCT SUBSTR (:val, 1, 11) “Searchword”,
SUBSTR (table_name, 1, 14) “Table”,
SUBSTR (column_name, 1, 14) “Column”
FROM ALL_TAB_COLUMNS,
TABLE (xmlsequence (dbms_xmlgen.getxmltype (‘select’
|| column_name
|| ‘from’
|| table_name
|| ‘where upper(‘
|| column_name
|| ‘) like upper(”%’||:val|| ‘%”)’ ).extract (‘ROWSET/ROW/*’) ) ) t
ORDER BY “Table”
massage
Error at Command Line : 4 Column : 1
Error report –
SQL Error: ORA-00904: : invalid identifier
00904. 00000 – “%s: invalid identifier”
*Cause:
*Action:
Question
1. How does the query know tables it is running from as code says only from table_name ?
It uses the table_name column from the metadata table “ALL_TAB_COLUMNS”. The actual tables names reside in this table’s column.
ORA-00904: : invalid identifier indicates you have some character which is not an acceptable syntax. Probably you copy pasted the query, and the single/double quotes changed. I would recommend to paste the query on a text/code editor and check for any syntax issues.
Thanks Lalit for this post! Although I read thru all the posts above, I still don’t have an answer to the error I get. Here is my/your code:
variable val varchar2(10)
exec :val := ‘GR_F_GRST’
SELECT DISTINCT SUBSTR (:val, 1, 11) “Searchword”,
SUBSTR (table_name, 1, 14) “Table”,
SUBSTR (column_name, 1, 14) “Column”
FROM cols,
TABLE (xmlsequence (dbms_xmlgen.getxmltype (‘select ‘
|| column_name
|| ‘ from ‘
|| table_name
|| ‘ where upper(‘
|| column_name
|| ‘) like upper(”%’
|| :val
|| ‘%”)’ ).extract (‘ROWSET/ROW/*’) ) ) t
ORDER BY “Table”;
I get the following error msg:
ORA-01460: unimplemented or unreasonable conversion requested
01460. 00000 – “unimplemented or unreasonable conversion requested”
*Cause:
*Action:
If it’s of any help, my Oracle version is 9.2.0.6.0.
Would you have any suggestions please?
Thanks!
For what it’s worth, upgrade your database version as 9.2 has been desupported more than a decade ago. All my examples were run on 12c database and latest database version in 19c. It’s not possible for me to reproduce your issue on a 9.2. You cannot reach out to Oracle support as well, because your version is no longer supported.
Is it possible to modify this script to not only look through all the tables of the schema but also go through all the schemas doing this?
Which database version are you using? This solution was posted on 11.2.0.4 long back, so newer versions might have a better and efficient solution.
Pingback: Search All Fields In All Tables For A Specific Value (Oracle)