CODEWITHSUNDEEP
sqloracle-databaseplsql
Suraj
Suraj

Reputation: 3137

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

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

Upvotes: 4

Views: 2941

Answers (3)

batflix
batflix

Reputation: 196

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);

Upvotes: 0

Tenzin
Tenzin

Reputation: 2505

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;
/

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36922

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.

Upvotes: 1

Related Questions