CODEWITHSUNDEEP
oracle-databaseoracle11g
user1751510
user1751510

Reputation: 291

Oracle: Get column name based on a value in a table

Is there a way to get the name of the column based on its value.

For example

Let's say i have a table called access which has only one row at all times, but differs from one schema to another. Also the value can only be either be 0 or 1)

field0 | field1
---------------
   1    |  0

Is there a way to select column 'field0' based on the value = '1'?

I know i can do it using code for a particular table, but i want to do it using a oracle query.

The only idea i had was to check 

select column_name from ALL_TAB_COLUMNS where TABLE_NAME='table' -- but no way to check for value;

Any suggestions are greatly appreciated.

Upvotes: 2

Views: 8390

Answers (1)

Srikanth Balaji
Srikanth Balaji

Reputation: 2718

Your Approach is inappropriate and there are much better ways to handle your scenario, like having a properties like records in ACCESS table instead of referring the column names for business.

However, You can achieve your scenario using below.

DECLARE
  columnName varchar(128);
  sql_stmt    VARCHAR2(500);
  input1 VARCHAR(20);
  input2 VARCHAR(20);
BEGIN
  input1 := '1';
  input2 := 'schema_Name';
  FOR r IN (select column_name from ALL_TAB_COLUMNS where OWNER = 'DUMMY_TABLE' AND TABLE_NAME='T_PA_CASE' AND DATA_TYPE = 'VARCHAR2') LOOP
  BEGIN
   sql_stmt := 'select ' || r.column_name  || ' from ' || input2 || '.ACCESS WHERE ' || r.column_name || ' = :1';
   EXECUTE IMMEDIATE sql_stmt INTO columnName  USING input1;
   DBMS_OUTPUT.PUT_LINE('First Matched Column is ----'||r.column_name);
   exit;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    CONTINUE;  
  END;
  END LOOP;
END;

Output - First Matched Column name is ----FIELD0

You shall tweak this to a Function and send input for Schema Name and Value to be compared against, to get the output of first matching column name.

Upvotes: 2

Related Questions