Retrieval of Column name in Oracle

Question

how to retrieve a column name based on the value given in i wnat to get the column_name in oracle

like i dont know a cloumn name but i know the column value

i have tried in this way

select column_name from table_name where column_value=XXXXX;

possible duplicate of [PL / SQL to search a string in whole database](http://stackoverflow.com/questions/28299146/pl-sql-to-search-a-string-in-whole-database) — Lalit Kumar B, Jun 04 '15 at 05:28
This looks bit of a hacking, it's not the way you should use a relational database... You should be sure which column stores the attribute you want to filter your records by. — David Lakatos, Jun 04 '15 at 05:32

score 1 · Answer 1 · answered Jun 04 '15 at 05:15

Try like this:

SQL> select table_name, 
       column_name, 
       :search_string search_string, 
       result
  from cols, 
       xmltable(('ora:view("'||table_name||'")/ROW/'||column_name||'[ora:contains(text(),"%'|| :search_string || '%") > 0]')
       columns result varchar2(10) path '.'
       )
where table_name in ('EMP', 'DEPT')

Source

score 0 · Answer 2 · answered Jun 04 '15 at 05:23

If you want to do it in plain SQL, then you could use the XML approach.

For example, to search for the value KING in SCOTT schema:

SQL> variable val varchar2(10)
SQL> exec :val := 'KING'

PL/SQL procedure successfully completed.

SQL> SELECT DISTINCT SUBSTR (:val, 1, 11) "Searchword",
  2    SUBSTR (table_name, 1, 14) "Table",
  3    SUBSTR (column_name, 1, 14) "Column"
  4  FROM cols,
  5    TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  6    || column_name
  7    || ' from '
  8    || table_name
  9    || ' where upper('
 10    || column_name
 11    || ') like upper(''%'
 12    || :val
 13    || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
 14  ORDER BY "Table"
 15  /

Searchword  Table          Column
----------- -------------- --------------
KING        EMP            ENAME

SQL>

Read SQL to Search for a VALUE in all COLUMNS of all TABLES in an entire SCHEMA

Retrieval of Column name in Oracle

2 Answers2