oracle - Quering all columns of single table in where condition for same input data -

if want fetch information based on condition on single column, this

select * contact firstname = 'james' 

if want put conditions on multiple columns, this

select * contact firstname = 'james' or lastname = 'james' or businessname = 'james' 

but if have more 50 columns.

is there better way other condition or keyword? approach should not involve writing column names.

there way in mysql shown here.

if you're wanting search varchar2 columns, following script ought help:

set pages 0; set lines 200  select case when rn = 1 , rn_desc = 1 'select * '||table_name||' '||column_name||' = ''james'';'             when rn = 1 'select * '||table_name||' '||column_name||' = ''james'''             when rn_desc = 1 '    , '||column_name||' = ''james'';'             else '    , '||column_name||' = ''james'''        end sql_stmt   (select table_name,                column_name,                column_id,                row_number() on (partition table_name order column_id) rn,                row_number() on (partition table_name order column_id desc) rn_desc            user_tab_columns          data_type in ('varchar2')         -- ,   table_name in (<list of tables>) -- uncomment , amend appropriate!        ) order table_name, column_id; 

if want search specific tables, have put filter in table_names you're after.

running above script give script containing multiple queries can run