create table cust
(col1 varchar(10),col2 varchar(10), col3 varchar(10),col4 varchar(10), col5 varchar(10));

insert into cust
values('custno' ,'name' , 'address' ,'city' , 'zip' );
insert into cust
values('1' ,'abc' , '123 street' ,'Houston' , '77004' );
insert into cust
values('2' ,'qwe' , 'test ave' ,'Pune' , '41125' );
insert into cust
values('3' ,'xyz' , '10 floor' ,'New York' , '44547' );
insert into cust
values('4' ,'pqr' , '15 floor' ,'New York' , '44557' );
insert into cust
values('5' ,'hhh' , 'Test' ,'Mumbai' , '77455' );

SQL> select * from cust where not regexp_like(col1,'^\d+$');
COL1       COL2       COL3       COL4       COL5
---------- ---------- ---------- ---------- ----------
custno     name       address    city       zip

SCOTT@orcl_12.1.0.2.0> select * from cust
  2  /

COL1       COL2       COL3       COL4       COL5
---------- ---------- ---------- ---------- ----------
custno     name       address    city       zip
1          abc        123 street Houston    77004
2          qwe        test ave   Pune       41125
3          xyz        10 floor   New York   44547
4          pqr        15 floor   New York   44557
5          hhh        Test       Mumbai     77455

6 rows selected.

SCOTT@orcl_12.1.0.2.0> variable g_name_and_city refcursor
SCOTT@orcl_12.1.0.2.0> declare
  2    v_name_col  varchar2(30);
  3    v_city_col  varchar2(30);
  4  begin
  5    for r in
  6  	 (select column_name
  7  	  from	 user_tab_columns
  8  	  where  table_name = 'CUST')
  9    loop
 10  	 begin
 11  	   execute immediate
 12  	     'select ''' || r.column_name ||
 13  	     ''' from	dual
 14  	      where  exists
 15  		     (select *
 16  		      from   cust
 17  		      where ' || r.column_name || ' = ''name'')'
 18  	     into v_name_col;
 19  	     exit when v_name_col is not null;
 20  	 exception
 21  	   when no_data_found then null;
 22  	 end;
 23    end loop;
 24    for r in
 25  	 (select column_name
 26  	  from	 user_tab_columns
 27  	  where  table_name = 'CUST')
 28    loop
 29  	 begin
 30  	   execute immediate
 31  	     'select ''' || r.column_name ||
 32  	     ''' from	dual
 33  	      where  exists
 34  		     (select *
 35  		      from   cust
 36  		      where ' || r.column_name || ' = ''city'')'
 37  	     into v_city_col;
 38  	     exit when v_city_col is not null;
 39  	 exception
 40  	   when no_data_found then null;
 41  	 end;
 42    end loop;
 43    open :g_name_and_city for
 44  	 'select ' || v_name_col || ' name,' || v_city_col || ' city from cust where '
 45  	 || v_name_col || ' != ''name'' and ' || v_city_col || ' != ''city''';
 46  end;
 47  /

PL/SQL procedure successfully completed.

SCOTT@orcl_12.1.0.2.0> print :g_name_and_city

NAME       CITY
---------- ----------
abc        Houston
qwe        Pune
xyz        New York
pqr        New York
hhh        Mumbai

5 rows selected.

SCOTT@orcl_12.1.0.2.0> select x.name, x.city
  2  from   (select dbms_xmlgen.getxml
  3  		      ('select '||t1.column_name||' name,'||t2.column_name||' city from cust
  4  			where exists (select * from cust where '||t1.column_name||'=''name'')
  5  			and   exists (select * from cust where '||t2.column_name||'=''city'')') xml_data
  6  	     from   user_tab_columns t1, user_tab_columns t2
  7  	     where  t1.table_name = 'CUST'
  8  	     and    t2.table_name = 'CUST'
  9  	     and    t1.column_name != t2.column_name) t,
 10  	    xmltable
 11  	      ('//ROWSET/ROW'
 12  	       passing xmltype (t.xml_data)
 13  	       columns
 14  		 name  varchar2(10)  path '/ROW/NAME',
 15  		 city  varchar2(10)  path '/ROW/CITY') x
 16  where  t.xml_data is  not null
 17  and    x.name != 'name'
 18  and    x.city != 'city'
 19  /

NAME       CITY
---------- ----------
abc        Houston
qwe        Pune
xyz        New York
pqr        New York
hhh        Mumbai

5 rows selected.

SQL> with
  2    names_row as (select * from cust where not regexp_like(col1,'^\d+$')),
  3    names as (
  4      select position,
  5             decode(position, 1,col1, 2,col2, 3,col3, 4,col4) column_name
  6      from names_row,
  7           (select level position from dual connect by level <= 4)
  8    )
  9  select decode(c1.position, 1,col1, 2,col2, 3,col3, 4,col4) name,
 10         decode(c2.position, 1,col1, 2,col2, 3,col3, 4,col4) city
 11  from cust,
 12       (select position from names where column_name='name') c1,
 13       (select position from names where column_name='city') c2
 14  where regexp_like(col1,'^\d+$')
 15  /
NAME       CITY
---------- ----------
abc        Houston
qwe        Pune
xyz        New York
pqr        New York
hhh        Mumbai

5 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from cust
  2  /

COL1       COL2       COL3       COL4       COL5
---------- ---------- ---------- ---------- ----------
name       address    city       zip        custno
abc        123 street Houston    77004      1
qwe        test ave   Pune       41125      2
xyz        10 floor   New York   44547      3
pqr        15 floor   New York   44557      4
hhh        Test       Mumbai     77455      5

6 rows selected.

SCOTT@orcl_12.1.0.2.0> select x.name, x.city
  2  from   (select dbms_xmlgen.getxml
  3  		      ('select '||t1.column_name||' name,'||t2.column_name||' city from cust
  4  			where exists (select * from cust where '||t1.column_name||'=''name'')
  5  			and   exists (select * from cust where '||t2.column_name||'=''city'')') xml_data
  6  	     from   user_tab_columns t1, user_tab_columns t2
  7  	     where  t1.table_name = 'CUST'
  8  	     and    t2.table_name = 'CUST'
  9  	     and    t1.column_name != t2.column_name) t,
 10  	    xmltable
 11  	      ('//ROWSET/ROW'
 12  	       passing xmltype (t.xml_data)
 13  	       columns
 14  		 name  varchar2(10)  path '/ROW/NAME',
 15  		 city  varchar2(10)  path '/ROW/CITY') x
 16  where  t.xml_data is  not null
 17  and    x.name != 'name'
 18  and    x.city != 'city'
 19  /

NAME       CITY
---------- ----------
abc        Houston
qwe        Pune
xyz        New York
pqr        New York
hhh        Mumbai

5 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from cust
  2  /

COL1       COL2       COL3       COL4       COL5
---------- ---------- ---------- ---------- ----------
name       address    city       zip        custno
abc        123 street Houston    77004      1
qwe        test ave   Pune       41125      2
xyz        10 floor   New York   44547      3
pqr        15 floor   New York   44557      4
hhh        Test       Mumbai     77455      5

6 rows selected.

SCOTT@orcl_12.1.0.2.0> with
  2    names_row as (select * from cust where not regexp_like(col1,'^\d+$')),
  3    names as (
  4  	 select position,
  5  		decode(position, 1,col1, 2,col2, 3,col3, 4,col4) column_name
  6  	 from names_row,
  7  	      (select level position from dual connect by level <= 4)
  8    )
  9  select decode(c1.position, 1,col1, 2,col2, 3,col3, 4,col4) name,
 10  	    decode(c2.position, 1,col1, 2,col2, 3,col3, 4,col4) city
 11  from cust,
 12  	  (select position from names where column_name='name') c1,
 13  	  (select position from names where column_name='city') c2
 14  where regexp_like(col1,'^\d+$')
 15  /

no rows selected