Oracle - Search a value in all columns of a table

Question

Here is my issue.

I have a Oracle table which contains 53 columns. And i would like to do a search for a value for all columns. And i do not want to put all columns in the where conditions.

Is there any optimistic way to accomplish this?

Any help would be appreciated.

• Joe Obbish: Do all of the columns have the same data type? Does the table have a primary key? If so, what is it? Can you clarify what you mean by "And i do not want to put all columns in the where conditions"? Why don't you want to do that?
• Raja: Thanks. I have checked. There is no primary key for this table. I know that there is simple way to search a value in all columns by using where condition. Example: Select * from tablename where col1='value' || col2 = 'value' || col3='value' || col4='value'. So i think that giving 53 columns in the where condition is not right way. Is there any other way to achieve it? thing.

Answer 1

As far as I can tell it's not possible to search all columns of a table for a value without referencing all of the column names that you want to search in some way in the query.

If all of your columns have compatible data types and you just want to return any rows that have at least one column that matches your value here is one way to accomplish that:

SELECT *
FROM tablename
WHERE 'value' IN (col1, col2, ... col53);

If you don't want to go through the work of writing out all of the column names or if you need to generate these types of queries for many different tables you can automate the query writing using Oracle's metadata views. For example, you can generate a list of all column names for a table by querying the ALL_TAB_COLUMNS view. Something like this should work:

SELECT LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID) 
from ALL_TAB_COLUMNS
WHERE OWNER = 'TABLE_OWNER' -- replace with the owner
AND TABLE_NAME = 'tablename'; -- replace with the table name