Provide table name dynamically in query

[r035198x]

Hi Oracle experts,
I have a table called, say, OtherTables which stores IDs of other tables and their respective table names.

OtherTables(ID, tableID, tableName)

ID is the key of the OtherTables table.

I want to select from a table whose tableID is stored in the OtherTables table. I have the ID of the OtherTables row that contains this table that I want to select from.

I hope this is possible and that someone would be able to help get a solution in one query.

Thanks in advance.

[MMcCarthy]

I could do this in VBA and assume it could be similarly done in any application that would allow you to build a SQL string. Are you using any other application with Oracle in this case?

I'm not an expert in Oracle syntax and as such I'm not aware of any way to retrieve a table name from one query to use in the FROM part of another query.

Mary

[eWish]

I've not worked with Oracle. If I understand your intentions correctly this resource might be of help.

[MMcCarthy]

Actually that will probably work. Forgot oracle had an All_Tables facility.

[debasisdas]

try to use

select TABLE_NAME from user_tables;

for the name of all the tables in hte current schema.

else this

select OWNER, TABLE_NAME from all_all_tables

for all the tables in the database.

[r035198x]

try to use

select TABLE_NAME from user_tables;

for the name of all the tables in hte current schema.

else this

select OWNER, TABLE_NAME from all_all_tables

for all the tables in the database.

First, thanks to everyone who's responded.
selecting from user_tables won't help me here. The OtherTables table is mine. I created it and it stores information about other tables.
I want to retrieve a table from a row in that OtherTables table and then do a select on that table that I will have retrieved from the OtherTables table all in one query.

Something like

select * from (Select tableName from OtherTables where ID = 10)

but this obviously simply returns one table name. I want to select the data in that table that is returned by the above query instead.

I hope I've made myself more clear ...

[debasisdas]

Try to use execute immediate in a PL/SQL block.

Also use CURSORS.

[MMcCarthy]

Try something like ...

SELECT * from all_tables tabs                                         
WHERE tabs.table_name IN
(Select tableName from OtherTables where ID = 10)
 

This is based on eWish's link

[amitpatel66]

Try something like ...

SELECT * from all_tables tabs                                         
WHERE tabs.table_name IN
(Select tableName from OtherTables where ID = 10)
 

This is based on eWish's link

Mary, the above query would give information about table details ie tablespace used etc and not the data of that table.

Make us of dymamic sql (EXECUTE IMMEDIATE) to achieve the functionality as suggested in previous posts.

[r035198x]

Mary, the above query would give information about table details ie tablespace used etc and not the data of that table.

Make us of dymamic sql (EXECUTE IMMEDIATE) to achieve the functionality as suggested in previous posts.

I suppose there's no way of doing it in one query then? I'm trying to use this in a report in ireports and I don't think I can use PL/SQL blocks in ireports.

[debasisdas]

I suppose there's no way of doing it in one query then? I'm trying to use this in a report in ireports and I don't think I can use PL/SQL blocks in ireports.

Then try use the code inside a stored procedure and call it. You need to use ref cursor as the output type of the procedure.