output data from all_tab_columns

Question

i have a table test with many filler columns whose values are actually the column names from some tables in the database. i want to frame a query which returns the owner,table_name,column_name for all the filler columns

DROP TABLE TEST;
create  table test (cat varchar2(10) , filler_1 varchar2(10), filler_2 varchar2(10));

INSERT INTO TEST
SELECT 
'A',    'DOB','BIRTH_DT' FROM DUAL
UNION ALL
SELECT 'B', 'GENDER'    ,'T_GENDER' FROM DUAL
UNION ALL
SELECT 'C', 'ACT_ID',   'ACT_NO' FROM DUAL;
COMMIT;

expected output :

CAT      OWNER       TABLE_NAME     COLUMN_NM
A       SCOTT        ABC            DOB
A       SCOTT        XXX            BIRTH_DT
B       HR           AAA            GENDER
B       HR           BBB            T_GENDER
C       SCOTT        CCC            ACT_ID
C       HR           DDD            ACT_NO

how do i achieve this using all_tab_columns as there are many filler columns of that sort. Thanks

Answer 1

Here is a script option using a dynamically created view. Will that work?

DECLARE
  sql_string VARCHAR2(4000);

  CURSOR columns_cur IS
    SELECT column_name
    FROM   all_tab_columns
    WHERE  table_name = 'TEST'
    AND    column_name LIKE 'FILLER\_%' ESCAPE '\';
BEGIN
  sql_string := 'CREATE OR REPLACE VIEW test_v AS ';

  FOR r IN columns_cur LOOP
    sql_string := sql_string||' SELECT cat, '||r.column_name||' filler FROM test UNION ';
  END LOOP;

  sql_string := SUBSTR(sql_string, 1, LENGTH(sql_string) - 6);

  EXECUTE IMMEDIATE sql_string;
END;
/

SELECT b.cat, a.owner, a.table_name, a.column_name column_nm
FROM   all_tab_columns a,
       test_v b
WHERE  a.column_name = b.filler
AND    a.column_name IN (
  SELECT filler
  FROM   test_v);

You could obviously extend this technique to include everything in one view.