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

Question

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.

Have a look here: https://stackoverflow.com/questions/2838490/table-name-as-variable — sanastasiadis, Oct 25 '19 at 14:06
@sanastasiadis the question is tagged with _oracle_ but the question you linked to is for _SQL Server_. How does this answer the question? — Abra, Oct 25 '19 at 14:08
_I'm not an Oracle expert_ I recommend the [Oracle Database Documentation](https://docs.oracle.com/en/database/oracle/oracle-database/index.html) — Abra, Oct 25 '19 at 14:18
@Abra In the link question I sent, the answer explains that the table names and column names need to be static in general. If it's necessary, we should generate the whole SQL. My intention was not to answer the question, but to provide a valuable resource as a comment. — sanastasiadis, Oct 25 '19 at 14:20

score 1 · Accepted Answer · answered Oct 25 '19 at 14:25

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!!

score 0 · Answer 2 · answered Oct 25 '19 at 14:03

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

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

2 Answers2