CODEWITHSUNDEEP
sqloracle-databaseplsql
Sashko Bily
Sashko Bily

Reputation: 23

How do I reuse a variable as a table name in select query

I need to get an id from a table, whose table name is defined by running another query.

I tried to implement it by defining a variable that saves the results from the first query. Then I created a second query that consumes the table name from the first one.

define tablename = (SELECT table_name FROM tables_names WHERE row_id = 147);
define rowid = SELECT row_id FROM &tablename WHERE title is NULL;
select * from &rowid;

However it doesn't work. I also tried to create a stored procedure, but I don't understand how it should work either:

DECLARE 
    tablename varchar(128);
    rowid int;
BEGIN
    SELECT table_name INTO tablename FROM tables_names WHERE row_id = 147);
    SELECT row_id INTO rowid FROM &tablename WHERE title is NULL;
END;

The output should give me the expected rowid. I'm not an Oracle expert and this seems like a simple thing, but I don't know how to achieve it.

Upvotes: 2

Views: 511

Answers (2)

Popeye
Popeye

Reputation: 35900

Use execute immediate as following:

DECLARE 
    rowid_ int; -- dont use oracle reserved words as variable name. 
    -- added _ after rowid
BEGIN
    EXECUTE IMMEDIATE 'SELECT row_id FROM ' 
    ||  (SELECT table_name FROM tables_names WHERE row_id = 147) 
    || ' WHERE title is NULL' INTO ROWID_;
-- do something with rowid_ or other logic
END;
/

Cheers!!

Upvotes: 1

TineO
TineO

Reputation: 1033

This is called dynamic sql. Edit the procedure and have it do 

EXECUTE IMMEDIATE 
"SELECT row_id INTO :x FROM " || tablename || " WHERE title IS NULL" 
USING rowid;

This is from memory so it might not work, but should give you a good start on how to do it

Upvotes: 0

Related Questions