with arr as (select a1||b1||..||n1 as one, t.* from table t
union all
select a2||b2||..||n2 as two, t.* from table 
..
union all
select an||bn||..||nn as n, t.* from table) 
select * from t where t.two like '%значение%'

with arr as (select t1.a1||t1.b1||..||t1.n1 as one, t1.* from table t1
union all
select t2.a2||t2.b2||..||t2.n2 as two, t2.* from table t2
..
union all
select tn.an||tn.bn||..||tn.nn as n, tn.* from table tn) 
select * from arr where arr.two like '%значение%'

select t2.a2||t2.b2||..||t2.n2 as two, t2.a2, t2.b2,..., t2.n2 from table t2

with t as (
  select d.dummy||d.dummy||d.dummy as ONE, d.* from dual d 
  union all
  select d.dummy||d.dummy||d.dummy as TWO, d.* from dual d)
select *
from t
where t.TWO is not null;

SQL Error: ORA-00904: "T"."TWO": недопустимый идентификатор
00904. 00000 -  "%s: invalid identifier"

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2  select 1 id,1 a1,11 a2,111 an from dual union all
  3  select 2 id,11 a1,11 a2,11 an from dual union all
  4  select 3 id,111 a1,null a2,111 a3 from dual )
  5* select id,a1||a2||an s,a1,a2,an from t
SQL> /

        ID S                            A1         A2         AN
---------- -------------------- ---------- ---------- ----------
         1 111111                        1         11        111
         2 111111                       11         11         11
         3 111111                      111 null              111

with arr as (select _1_||t1.a1||t1.b1||..||t1.n1 as env, t1.* from table t1
union all
select _2_||t2.a2||t2.b2||..||t2.n2, t2.* from table t2
..
union all
select _n_||tn.an||tn.bn||..||tn.nn as n, tn.* from table tn) 
select * from arr where arr.env like '%значение%'