Reputation: 3137
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
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
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
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