CODEWITHSUNDEEP
sqloracle-database
Probal Basak
Probal Basak

Reputation: 92

Oracle select selected columns from a table whose column names are available in another table

Let me give an example :

Suppose I have a table TableA(Col1, Col2, Col3, Col4, Col5)

I have another table TableB where their are entries of the names of the columns of TableA that required to be fetched, for example Col2 and Col5

Now I want to write an SQL query that will only fetch the columns of TableA as defined in TableB .

Upvotes: 0

Views: 702

Answers (1)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Here is a start.

The idea is to build a concatenated list of column_names as a varchar

'col1, col2, col3, col4'

and to use it in a dynamic sql query.

declare 
column_list xmltype;
column_names varchar(10000);

begin

SELECT 
          XMLAGG (XMLELEMENT (e, t1.column_name || ',')).EXTRACT ('//text()')
          column_name
  into column_list
  FROM all_tab_cols t1
  where t1.table_name = 'TABLEA'
  and exists (select null 
              from TableB
              where t1.column_name = <the field for the column_name in tableB>);

column_names := RTRIM(column_list.getClobVal(), ',');

--this will just display the sql query, you'll need to execute it to get your results with EXECUTE IMMEDIATE
dbms_output.put_line( 'SELECT '||column_names||' from TableA');
end;

Upvotes: 1

Related Questions