How to execute sql query on a table whose name taken from another table

Question

I have a table that store the name of other tables. Like

COL_TAB
--------------
TABLE_NAME
--------------
TAB1
TAB2
TAB3

What i want to do is that, i want to run a sql query on table like this,

SELECT * FROM (SELECT TABLE_NAME from COL_TAB WHERE TABLE_NAME = 'TAB1')

Thanks

Yeah with procedure we can easily do this. Is there any way to do this without procedure? — Pirate, Jul 14 '16 at 15:17
No, there isn't. SQL requires all tables (and columns) to be know _before_ the statement is actually executed (and to be honest: a database model where you require this sounds like a really bad design) — , Jul 14 '16 at 15:22
You don't have to persist the stored procedure. As long as you login to Oracle, you can run the procedure as an anonymous block. Note that in order to return the value, you're better off with a pl/sql table construct. — T Gray, Jul 14 '16 at 16:20
If the tables have the same columns and you know the names in advance, you can do select from a UNION view or else do something with XML. Otherwise, you'll need dynamic code. — William Robertson, Jul 14 '16 at 16:44
@a_horse_with_no_name Could be a school subject on how to make use of dynamic queries. , — Tenzin, Jul 14 '16 at 20:10

score 1 · Answer 1 · answered Jul 14 '16 at 17:40

An Oracle SQL query can use a dynamic table name, using Oracle Data Cartridge and the ANY* types. But before you use those advanced features, take a step back and ask yourself if this is really necessary.

Do you really need a SQL statement to be that dynamic? Normally this is better handled by an application that can submit different types of queries. There are many application programming languages and toolkits that can handle unexpected types. If this is for a database-only operation, then normally the results are stored somewhere, in which case PL/SQL and dynamic SQL are much easier.

If you're sure you've got one of those rare cases that needs a totally dynamic SQL statement, you'll need something like my open source project Method4. Download and install it and try the below code.

Schema Setup

create table tab1(a number);
create table tab2(b number);
create table tab3(c number);
insert into tab1 values(10);
insert into tab2 values(20);
insert into tab3 values(30);
create table col_tab(table_name varchar2(30), id number);
insert into col_tab values('TAB1', 1);
insert into col_tab values('TAB1', 2);
insert into col_tab values('TAB1', 3);
commit;

Query

select * from table(method4.dynamic_query(
q'[
    select 'select * from '||table_name sql
    from col_tab
    where id = 1
]'));

Result:

A
--
10

You'll quickly discover that queries within queries are incredibly difficult. There's likely a much easier way to do this, but it may require a design change.

what if i remove where condition from the query? will the select query execute on all tables? — Pirate, Jul 17 '16 at 21:54
Yes, it would work with multiple tables. However, the column types must be the same for each row. — Jon Heller, Jul 17 '16 at 22:10

score 0 · Answer 2 · answered Jul 14 '16 at 20:08

I don't have a database by hand to test this but I think you are looking for something like this:

DECLARE
    -- Create a cursor on the table you are looking through. 
    CURSOR curTable IS 
        SELECT  * 
        FROM    MainTable;

    recTable    curTable%ROWTYPE;
    vcQuery     VARCHAR2(100);
BEGIN
    -- Loop through all rows of MainTable. 
    OPEN curTable;
    LOOP
        FETCH curTable INTO recTable;
        EXIT WHEN curTable%NOTFOUND;

        -- Set up a dynamic query, with a WHERE example. 
        vcQuery := 'SELECT ColumnA, ColumnB FROM ' || recTable.Table_Name || ' WHERE 1 = 1';

        -- Execute the query. 
        OPEN :dyn_cur FOR vcQuery;
    END LOOP;
    CLOSE curTable;
END;
/

batflix · Answer 3 · 2016-09-01T10:57:26.893

0

Try this

CREATE OR REPLACE PROCEDURE TEST IS
   sql_stmt    VARCHAR2(200);
   V_NAME     VARCHAR2(20);
BEGIN
   sql_stmt := 'SELECT * FROM ';
   EXECUTE IMMEDIATE sql_stmt|| V_NAME;
END;

Update select statement dont work in procedure.

in sql server you can try sql block

Declare @name varchar2(50)

Select @name='Select * from '+TABLE_NAME from COL_TAB WHERE TABLE_NAME = 'TAB1' 

EXEC(@name);

edited Sep 01 '16 at 10:57

answered Aug 31 '16 at 10:34

batflix

196
5

yeah, but i want to do it without using procedure. – Pirate Aug 31 '16 at 10:35
1

That won't work. You can't just run a `SELECT` statement inside a stored procedure without storing the result somewhere in PL/SQL. At the very least you need a ref cursor or something similar. – Aug 31 '16 at 12:45
yes, select statement dont work in procedure. in sql server you can try sql block Declare @name varchar2(50) Select @name='Select * from '+TABLE_NAME from COL_TAB WHERE TABLE_NAME = 'TAB1' EXEC(@name); – batflix Sep 01 '16 at 10:53
1

What use is a SQL Server solution if Pirate is using Oracle? – Sep 01 '16 at 11:23

How to execute sql query on a table whose name taken from another table

3 Answers3