Is it possible to target a column in table B, based on a value in table A?

Question

I want to see the tab_b table data without using direct column selection from tab_b. The column name value should come from tab_a. Column names of tab_b are storing in tab_a. How to select name, salary column name dynamically from tab_a for tab_b?

Select (Select tab_a.col_a from tab_a where col_a='Name') from tab_b;

but it is showing only Name in the results. I want values of tab_b for Name column, without directly selecting from tab_b.

Create table tab_a(Col_a varchar2(100));

insert into tab_a(col_a) values ('Name');
insert into tab_a(col_a) values ('Salary');

Commit;

Create table tab_b(Name varchar2(100), Salary Number);

insert into tab_b(Name, Salary) values ('ABC',1000);
insert into tab_b(Name, Salary) values ('EDF',1000);

Commit;

Answer 1

One option is to create a pl/sql procedure that will create a view using dynamicaly created DDL. Run the procedure and then select all columns from that view...

CREATE OR REPLACE PROCEDURE MK_VIEW AS 
BEGIN
      Declare
          CURSOR c IS
            Select COL_A From tab_a;
          mCol Varchar2(32);
          mSQL  Varchar2(1000) := 'CREATE OR REPLACE VIEW some_view AS Select ';
      Begin 
          OPEN c;
          LOOP
            FETCH c Into mCol;
            EXIT WHEN c%NOTFOUND;
            mSQL := mSQL || mCol || ',';
          END LOOP;
          CLOSE c;
          mSQL := Substr(mSQL, 1, Length(mSQL) - 1) || ' From tab_b';
          Execute Immediate mSQL;
      End;
END MK_VIEW;
/

BEGIN
   MK_VIEW;
END;
/

Select * From SOME_VIEW;

R e s u l t :

NAME             SALARY
------------ ----------
ABC                1000
EDF                1000