select data of specific column from table where column names are returned by query

oracle, sql

Solution

You'll have to use dynamic SQL for this (BTW, I got rid of the subselect for the USER_TABLES query - it's unnecessary):

var  cur refcursor
/
declare
  v_stmt varchar2(4000);
begin
  v_stmt := 'SELECT ';  
  for cur in (
    SELECT COLUMN_NAME
    FROM ALL_TAB_COLUMNS
    WHERE TABLE_NAME =
       'MY_TABLE'
    AND COLUMN_NAME NOT IN ('AVOID_COLUMN')
  ) 
  loop
    v_stmt := v_stmt || cur.column_name || ',';
  end loop;
  -- get rid of trailing ','
  v_stmt := regexp_replace(v_stmt, ',$', '');

  v_stmt := v_stmt || ' from my_table MT WHERE MT.COL1 = ''1''';
  dbms_output.put_line(v_stmt);
  open :cur for v_stmt;
end;

Problem

I have written 2 separate queries 1) ``` SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = (SELECT DISTINCT UT.TABLE_NAME FROM USER_TABLES UT WHERE UT.TABLE_NAME = 'MY_TABLE') AND COLUMN_NAME NOT IN ('AVOID_COLUMN') ``` ` 2) ``` SELECT * FROM MY_TABLE MT WHERE MT.COL1 = '1' ``` ` The 1st query returns the names of all the columns except the one I want to avoid. The 2nd one returns data of all the columns from the table. Is there some way to merge these queries so that only those column's data is selected from the 2nd query, which are returned from the 1st query? Thanks in advance

Original source