469,328 Members | 1,315 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,328 developers. It's quick & easy.

Provide table name dynamically in query

13,262 8TB
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.
Oct 26 '07 #1
10 9511
MMcCarthy
14,534 Expert Mod 8TB
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
Oct 26 '07 #2
eWish
971 Expert 512MB
I've not worked with Oracle. If I understand your intentions correctly this resource might be of help.
Oct 27 '07 #3
MMcCarthy
14,534 Expert Mod 8TB
Actually that will probably work. Forgot oracle had an All_Tables facility.
Oct 27 '07 #4
debasisdas
8,127 Expert 4TB
try to use

Expand|Select|Wrap|Line Numbers
  1. select TABLE_NAME from user_tables;
for the name of all the tables in hte current schema.

else this

Expand|Select|Wrap|Line Numbers
  1. select OWNER, TABLE_NAME from all_all_tables
for all the tables in the database.
Oct 27 '07 #5
r035198x
13,262 8TB
try to use

Expand|Select|Wrap|Line Numbers
  1. select TABLE_NAME from user_tables;
for the name of all the tables in hte current schema.

else this

Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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 ...
Oct 27 '07 #6
debasisdas
8,127 Expert 4TB
Try to use execute immediate in a PL/SQL block.

Also use CURSORS.
Oct 27 '07 #7
MMcCarthy
14,534 Expert Mod 8TB
Try something like ...

Expand|Select|Wrap|Line Numbers
  1. SELECT * from all_tables tabs                                         
  2. WHERE tabs.table_name IN
  3. (Select tableName from OtherTables where ID = 10)
  4.  
This is based on eWish's link
Oct 27 '07 #8
amitpatel66
2,367 Expert 2GB
Try something like ...

Expand|Select|Wrap|Line Numbers
  1. SELECT * from all_tables tabs                                         
  2. WHERE tabs.table_name IN
  3. (Select tableName from OtherTables where ID = 10)
  4.  
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.
Oct 28 '07 #9
r035198x
13,262 8TB
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.
Oct 29 '07 #10
debasisdas
8,127 Expert 4TB
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.
Oct 29 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Michael Hobbs | last post: by
3 posts views Thread by ryanmhuc | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by listenups61195 | last post: by
reply views Thread by haryvincent176 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.