Querying for Records in an Oracle Database. Open to suggestions, ideas or commands that might be helpful?

I am using PL SQL with an Oracle Database. I have several hundred tables. Trying to find a needle in a haystack.
Also If you know of any Open Source or free tools if you know of that might work better I am listening.
I am wondering if it is possible to QUICKLY....without having to MANUALLY type the names of every single field in a Table with hundreds of fields in each table.

I am hoping I can somehow start with this:
select * from OracleDB.Table1 WHERE Field1 = 1234

Is there a command or way to add all fields in from a given table and check for a specific record in all of them..?

And if per se it is a number field or string field a way to select all string or number fields exclusively and have it look for a record
Like EIther Field1 = 1234      or    field1 = "1234"

May be wishful thinking. I have to find where a specific set of records are stored in an Oracle DB that no one even me is familiar with.
Doing the best I can just looking for a way to shorten this before my EYE's pop out of my head....!!!!
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
 
_agx_Connect With a Mentor Commented:
Figured out how to quote string fields only

-- TODO: Remove or Replace ' .... *** TABLE NAMES TO SEARCH HERE*** ....' in WHERE clause
SELECT 'SELECT '''|| TABLE_NAME ||''' AS Source, t.* FROM '|| TABLE_NAME 
||' t WHERE '
|| LISTAGG(
      CASE WHEN DATA_TYPE = 'VARCHAR2' THEN COLUMN_NAME || ' = ''1234'''
           ELSE  COLUMN_NAME || ' = 1234 ' 
       END
      , ' OR ') within group (order by table_name) 
FROM   ALL_TAB_COLUMNS 
WHERE TABLE_NAME IN (' .... *** TABLE NAMES TO SEARCH HERE*** ....') 
AND   DATA_TYPE IN ('VARCHAR2','NUMBER')
GROUP BY TABLE_NAME;

Open in new window

0
 
_agx_Connect With a Mentor Commented:
I don't use Oracle much, but a quick and dirty trick I've use in SQL Server is query metadata tables.  Use the table and column info to build the desired SQL string dynamically.  Then execute the generated string separately.

This is kind of cheat, as it quotes both string and numeric values (because my Oracle knowledge is poor) but something like this might get you started:

SELECT 'SELECT '''|| TABLE_NAME ||''' AS Source, t.* FROM '|| TABLE_NAME ||' t WHERE '|| LISTAGG(COLUMN_NAME || ' = ''1234''', ' OR ') WITHIN GROUP(ORDER BY TABLE_NAME) 
FROM    ALL_TAB_COLUMNS 
WHERE  TABLE_NAME IN (' .... table names to search here....') 
-- only include string and numeric columns
AND   DATA_TYPE IN ('VARCHAR2','NUMBER')
GROUP BY TABLE_NAME;

Open in new window


It'll produce a bunch of strings like this, that you can execute - one per table:

         
  SELECT 'MyTableName' AS Source, t.* FROM MyTableName t WHERE ID = '1234' OR TITLE = '1234'

Open in new window


Might want to limit the query to 1 or 2 tables at first. See if it works for you.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
There is no way to QUICKLY search all columns in all tables for a value.

I also don't know of any tools that might do this easily.

Similar to the above post, you'll need to go through the data dictionary.  You might also need to expand the data type list to include more than just VARCHAR2 and NUMBER.  There are a few other text based data types.  Some harder to work with like CLOBs and others next to impossible to work with LONG.

There are examples out there on ways to do this.  I like the DBMS_XMLGEN method because I find it easier to read.  Just a personal preference.

Here are a couple of examples:
https://www.experts-exchange.com/questions/28608105/Searching-all-fields-in-an-Oracle-database.html
https://www.experts-exchange.com/questions/28069987/identifying-columns-in-all-the-tables-with-a-specific-value.html
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Mark GeerlingsConnect With a Mentor Database AdministratorCommented:
As slightwv said, there is no way to do this quickly.

Here are two Oracle SQL queries that can quickly generate the lists of SQL queries for you, one for NUMBER columns and one for VARCHAR2 columns:
select 'select * from '||t.owner||'.'||t.table_name||' WHERE '||tc.column_name||' = 1234;' "Command"
from all_tables t, all_tab_columns tc
where tc.owner = t.owner
and tc.table_name = t.table_name
and tc.data_type = 'NUMBER'
and tc.owner <> 'SYS'
order by t.owner, t.table_name, tc.column_id;

Open in new window


select 'select * from '||t.owner||'.'||t.table_name||' WHERE '||tc.column_name||' = ''1234'';' "Command"
from all_tables t, all_tab_columns tc
where tc.owner = t.owner
and tc.table_name = t.table_name
and tc.data_type = 'VARCHAR2'
and tc.owner <> 'SYS'
order by t.owner, t.table_name, tc.column_id;

Open in new window


You will need to capture the output from these two queries, then run each of those commands.  That is not difficult if you use a "spool [filename];" command before each of these, and "spool off;" at the end of these two.  Then you can simply run those two files like this:
@filename1
@filename2

Also, it is possible that your database has data in some of the less-common data types like slightwv mentioned.  But, I would start with these two common data types (NUMBER and VARCHAR2) first.

Then, if the actual data just starts with, or includes these values, but the actual values in Oracle are more like : 12345, or "abc1234", should those be included?  The queries I suggested will not return those.
1
 
_agx_Connect With a Mentor Commented:
Mark GeerlingsDatabase wrote:
select 'select * from '||t.owner||'.'||....

Open in new window


Oh, yeah. I didn't include owner/schema name in my query, but that's an important addition.
0
 
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Thank you fellas. I may post another question to help identify the terms inside that code as a legend might be needed since I am not familiar with the organized structure of those statements.
0
 
_agx_Commented:
In case it's helpful

ALL_TAB_COLUMNS - "Lists (columns) in all tables, views, etc... accessible to current user". Includes details about columns like table owner (schema), column name, data type, etc...
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm


LISTAGG - Concatenates multiple values, with a given delimiter, into a single string
https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm#SQLRF30030
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.