Using SELECT within SELECT statement in ORACLE

Question

I have a table name SAMPLETABLE this has the tablenames of the tables I require in column TABLENAMES. Lets say the tablenames are TABLEA, TABLEB and TABLEC.

On query

SELECT TABLENAMES FROM SAMPLETABLE WHERE ROWNUM = 1

I get the output the output of TABLENAMES column with TABLEA value.

My problem is, now I want to use this selected value in a select statement. That is,

SELECT * FROM (SELECT TABLENAMES FROM SAMPLETABLE WHERE ROWNUM = 1) 

My idea is that it'd return the contents of TABLEA because when the nested SELECT returns TABLEA, the outer SELECT should capture and display it.

On the contrary, I get the output only of the inner statement, that is,

SELECT TABLENAMES FROM SAMPLETABLE WHERE ROWNUM = 1

and

SELECT * FROM (SELECT TABLENAMES FROM SAMPLETABLE WHERE ROWNUM = 1) 

return the same output.

I want the first SELECT statement to fetch the returned value of second SELECT and display the table. They above query doesn't do that, so how do I do it? And what is wrong with my idea?

I am on Oracle 10g, any help appreciated.

Answer 1

You can do this with help of dynamic sql. Since the table name is obtained during run time you have to frame the query dynamically and run it.

Declare
Tab_Name Varchar2(30);
Begin

  SELECT TABLENAMES into Tab_Name FROM SAMPLETABLE WHERE ROWNUM = 1;

  Execute Immediate 'Select * into (Collection Variable) from ' || Tab_Name;

End
/

I just gave it as example. You declare a variable to get the data out or something else as you need. But when you try to use execute immediate with input parameter read about sql injection and then write your code.

Answer 2

As table name is not known at compile time you need to use dynamic SQL(execute immediate, native dynamic SQL, for instance) to be able to select from a table, name of which is stored as a string literal - you cannot accomplish it with static SQL

Here is an example:

-- table which contains names of other tables
-- in the table_name column
SQL> create table Table_Names as
  2    select 'employees' as table_name
  3      from dual
  4  ;
Table created


SQL> set serveroutput on;

-- example of an anonymous PL/SQL block
-- where native dynamic SQL (execute immediate statement)
-- is used to execute a dynamically formed select statement  
SQL> declare
  2    type T_record is record(   -- example of record for fetched data
  3      f_name varchar2(123),    
  4      l_name varchar2(123)
  5    );  
  6  
  7    l_table_name varchar2(123);  -- variable that will contain table name
  8    l_select     varchar2(201);   
  9    l_record     T_Record;       -- record we are going to fetch data into
 10  begin
 11    select table_name
 12      into l_table_name          -- querying a name of a table
 13     from table_names            -- and storing it in the l_table_name variable
 14    where rownum = 1;
 15  
 16    l_select := 'select first_name, last_name from ' ||
 17                 dbms_assert.simple_sql_name(l_table_name) ||   
 18                ' where rownum = 1';   -- forming a query              
 19  
 20    execute immediate l_select    -- executing the query
 21       into l_record;
 22    -- simple output of data just for the sake of demonstration
 23    dbms_output.put_line('First_name: ' || l_record.f_name || chr(10) ||
 24                         'Last name:  ' || l_record.l_name);
 25  exception
 26    when no_data_found
 27    then dbms_output.put_line('Nothing is found');
 28  end;
 29  /

First_name: Steven
Last name:  King

PL/SQL procedure successfully completed

As a second option you could use weakly typed cursors - refcursors to execute a dynamically formed select statement:

SQL> variable refcur refcursor;
SQL> declare
  2    l_table_name varchar2(123);
  3    l_select     varchar2(201);
  4  begin
  5    select table_name
  6      into l_table_name
  7     from table_names
  8    where rownum = 1;
  9  
 10    l_select := 'select first_name, last_name from ' ||
 11                 dbms_assert.simple_sql_name(l_table_name) ||
 12                ' where rownum = 1';
 13  
 14    open :refcur
 15     for l_select;
 16  
 17  exception
 18    when no_data_found
 19    then dbms_output.put_line('Nothing is found');
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> print refcur;

FIRST_NAME           LAST_NAME                                                  
-------------------- -------------------------                                  
Steven               King                                                       

SQL> spool off;

Find out more about cursors and cursor variables