<div>
Using PL/SQL with dynamic sql can also get you what you need. 
declare 
v_sql varchar2(255); 
v_search varchar2(255) := 'abcdefghijk'; 
v_cnt pls_integer; 
begin 
for t in 
(select table_name from user_tables 
&nbsp;where column_name = 'CASE_ID' 
&nbsp;order by table_name) 
loop 
v_sql = 'select count(*) from '||t.table_name||' where case_id = '||v_search; 
execute immediate v_sql into v_cnt; 
if v_cnt &gt;&nbsp;0 then 
&nbsp;dbms_output.put_line(t.table_name||' is populated with '||v_search; 
end if; 
end loop; 
end; 
/ 
You may want to change user_tables to all_tables or dba_tables depending on your need and privileges.
</div>

<div>
You don't need nested loops in PL/SQL to do it. &nbsp;Use the same query generating SQL that you would use to spool the statements, then just loop through the results. 

<pre><code class="code-10 nolinks" id="code-20-41804988-1">DECLARE 
 v_cnt PLS_INTEGER; 
 v_owner VARCHAR2(30); 
 v_table VARCHAR2(30); 
BEGIN 
 FOR c1rec IN (SELECT 'select ''' 
 || owner 
 || ''' owner, ''' 
 || table_name 
 || ''' table_name, count(1) match_count from ' 
 || owner 
 || '.' 
 || table_name 
 || ' where case_id = ''abcdefghijk''' stmt 
 FROM dba_tab_columns 
 WHERE column_name = 'CASE_ID') LOOP 
 EXECUTE IMMEDIATE c1rec.stmt INTO v_owner, 
 v_table, 
 v_cnt; 

 dbms_output.Put_line(v_owner 
 || '.' 
 || v_table 
 || ' = ' 
 || v_cnt); 
 END LOOP; 
END; 

/ 
</code></pre>
This is the beauty of using SQL to generate SQL statements. &nbsp;You don't need to keep iterating through loops to get to the statement. &nbsp;You need dynamic SQL anyway, just run one query to generate the full statement for you. 
 
I don't care for doing it this way. &nbsp;Dynamic SQL isn't very efficient and DBMS_OUTPUT can be troublesome if there are a lot of tables involved (you can blow out the internal buffer pretty easily, even at maximum). &nbsp;Scripts and spooling get around this.
</div>

<div>
When I try the 1st suggestion: 
SELECT 'select ''' 
&nbsp; &nbsp; &nbsp; &nbsp;|| owner 
&nbsp; &nbsp; &nbsp; &nbsp;|| ''' owner, ''' 
&nbsp; &nbsp; &nbsp; &nbsp;|| table_name 
&nbsp; &nbsp; &nbsp; &nbsp;|| ''' table_name, count(1) match_count from ' 
&nbsp; &nbsp; &nbsp; &nbsp;|| owner 
&nbsp; &nbsp; &nbsp; &nbsp;|| '.' 
&nbsp; &nbsp; &nbsp; &nbsp;|| table_name 
&nbsp; &nbsp; &nbsp; &nbsp;|| ' where case_id = ''abcdefghijk'';' 
FROM &nbsp; all_tab_columns 
WHERE &nbsp;column_name = 'CASE_ID'; 
 
it appears to be returning a row for every table that has that column name regardless of 
whether or not my value really exists in that table because when I do a simple select * 
from... on several of the tables, it doesn't return any rows. 
 
When I try the 2nd suggestion: 
declare 
v_sql varchar2(255); 
v_search varchar2(255) := 'abcdefghijk'; 
v_cnt pls_integer; 
begin 
for t in 
(select table_name from all_tab_columns 
&nbsp;where column_name = 'CASE_ID' 
&nbsp;order by table_name) 
loop 
v_sql = 'select count(*) from '||t.table_name||' where case_id = '||v_search; 
execute immediate v_sql into v_cnt; 
if v_cnt &gt;&nbsp;0 then 
&nbsp;dbms_output.put_line(t.table_name||' is populated with '||v_search; 
end if; 
end loop; 
end; 
/ 
 
I get the following error report: 
 
Error report: 
ORA-06550: line 11, column 7: 
PLS-00103: Encountered the symbol &quot;=&quot; when expecting one of the following: 
 
&nbsp; &nbsp;:= . ( @ % ; 
ORA-06550: line 12, column 1: 
PLS-00103: Encountered the symbol &quot;EXECUTE&quot; 
06550. 00000 - &nbsp;&quot;line %s, column %s:\n%s&quot; 
*Cause: &nbsp; &nbsp;Usually a PL/SQL compilation error. 
*Action: 
 
When I try the 3rd suggestion: 
 
DECLARE 
&nbsp; &nbsp; v_cnt &nbsp; PLS_INTEGER; 
&nbsp; &nbsp; v_owner VARCHAR2(30); 
&nbsp; &nbsp; v_table VARCHAR2(30); 
BEGIN 
&nbsp; &nbsp; FOR c1rec IN (SELECT 'select ''' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| owner 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| ''' owner, ''' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| table_name 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| ''' table_name, count(1) match_count from ' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| owner 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| '.' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| table_name 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| ' where case_id = ''abcdefghijk''' stmt 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM &nbsp; all_tab_columns 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE &nbsp;column_name = 'CASE_ID') LOOP 
&nbsp; &nbsp; &nbsp; &nbsp; EXECUTE IMMEDIATE c1rec.stmt INTO v_owner, 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; v_table, 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; v_cnt; 
 
&nbsp; &nbsp; &nbsp; &nbsp; dbms_output.Put_line(v_owner 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| '.' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| v_table 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| ' = ' 
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;|| v_cnt); 
&nbsp; &nbsp; END LOOP; 
END; 
 
/ 
 
I &nbsp;get the following error report: 
 
Error report: 
ORA-00933: SQL command not properly ended 
ORA-06512: at line 17 
00933. 00000 - &nbsp;&quot;SQL command not properly ended&quot; 
*Cause: &nbsp; &nbsp; 
*Action:
</div>

<div>
The first one should return a row for every table that has a column CASE_ID in it. &nbsp;There is a count on each one. &nbsp;You can always filter out the counts of 0 from the output. 
 
This is the second one with the syntax errors corrected: 

<pre><code class="code-10 nolinks" id="code-20-41805156-1">DECLARE 
 v_sql VARCHAR2(255); 
 v_search VARCHAR2(255) := 'abcdefghijk'; 
 v_cnt PLS_INTEGER; 
BEGIN 
 FOR t IN (SELECT table_name 
 FROM all_tab_columns 
 WHERE column_name = 'CASE_ID' 
 ORDER BY table_name) LOOP 
 v_sql := 'select count(*) from ' 
 ||t.table_name 
 ||' where case_id = ' 
 ||v_search; 

 EXECUTE IMMEDIATE v_sql INTO v_cnt; 

 IF v_cnt &gt; 0 THEN 
 dbms_output.Put_line(t.table_name 
 ||' is populated with ' 
 ||v_search); 
 END IF; 
 END LOOP; 
END; 

/ 
</code></pre>
However, since ALL_TAB_COLUMNS is used, no owner is selected or added to the dynamic query, so it will fail if the user you are running the block as does not own the table. 
 
The third one works perfectly fine on my system. &nbsp;No errors.
</div>

<div>
Sorry for the confusion. v_sql = 'select ...' was meant to be v_sql := 'select ...' 
johnsone, thanks for the correction.
</div>

<div>
There was also a missing ) on the DBMS_OUTPUT function call. 
 
No problem. &nbsp;I already had a window open so just threw it in to get the syntax fixed. 
 
I'm not sure why people that ask questions aren't capable of figuring that out on their own. &nbsp;Most of the time we just free type these in.
</div>

<div>
Thank you both for your help. The first suggestion ended up being the best option for my level of privileges.
</div>

<div>
<div class="content wysiwyg-content">
Hello Experts, 
 
Is there a way to search an Oracle 12c database to find a specific value in a specific column for all tables that have that column? For example, if the column name is CASE_ID and the value that I am looking for is the string 'abcdefghijk' , I want to know all tables that have the CASE_ID column populated with that value.
</div>
</div>

Hello Experts,

Is there a way to search an Oracle 12c database to find a specific value in a specific column for all tables that have that column? For example, if the column name is CASE_ID and the value that I am looking for is the string 'abcdefghijk' , I want to know all tables that have the CASE_ID column populated with that value.

Oracle SQL Syntax

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Oracle Database

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.