Home » SQL & PL/SQL » SQL & PL/SQL » dynamic table name
dynamic table name [message #237782] Tue, 15 May 2007 13:07 Go to next message
dryinego
Messages: 16
Registered: May 2007
Junior Member
hey,

Is there a way to perform a select query on a table whose name is returned from some other query.

What i want is something like this:

select * from value from col

SELECT column1 FROM 'SELECT table_name FROM table2 where table2.column2= <some condition>'

is it possible in an sql query????

thanks

Re: dynamic table name [message #237784 is a reply to message #237782] Tue, 15 May 2007 13:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, not this way.
Maybe with dynamic query but what is the real problem?

Regards
Michel
Re: dynamic table name [message #237789 is a reply to message #237782] Tue, 15 May 2007 13:22 Go to previous messageGo to next message
dryinego
Messages: 16
Registered: May 2007
Junior Member
i knw we cant query this way.. was trying to give some idea as to wat i wanted.

i have 2 tables to store values. Both tables are same except one column which stores the value. the data type of VALUE in one table is integer and another is string. so i want to query either tables based on a condition.

Re: dynamic table name [message #237791 is a reply to message #237789] Tue, 15 May 2007 13:25 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
You can use EXECUTE IMMEDIATE.
Re: dynamic table name [message #237792 is a reply to message #237782] Tue, 15 May 2007 13:27 Go to previous messageGo to next message
dryinego
Messages: 16
Registered: May 2007
Junior Member
In an sql query? can you please gimme an example?
Re: dynamic table name [message #237795 is a reply to message #237792] Tue, 15 May 2007 13:43 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
You can do it in a procedure block.

declare

boolean_flag boolean;
table_name varchar2(30);
sql_stmt varchar2(1024);
field_int varchar2(30);
field_char varchar2(30);

begin

boolean_flag := false;

-- True gets the integer value, False gets the varchar2 value
if boolean_flag = true then
table_name := 'table1';
sql_stmt := 'select field1 from ' || table_name || ' where id = 1';
execute immediate sql_stmt into field_int;
else
table_name := 'table2';
sql_stmt := 'select field1 from ' || table_name || ' where id = 1';
execute immediate sql_stmt into field_char;
end if;

dbms_output.put_line('Integer Field' || field_int);
dbms_output.put_line('Char Field' || field_char);

end;

[Updated on: Tue, 15 May 2007 13:45]

Report message to a moderator

Re: dynamic table name [message #237796 is a reply to message #237782] Tue, 15 May 2007 13:44 Go to previous messageGo to next message
dryinego
Messages: 16
Registered: May 2007
Junior Member
Thanks. But i want to know if it can be done in a single query
Re: dynamic table name [message #237797 is a reply to message #237789] Tue, 15 May 2007 13:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Let me state it clearly: you can't do it in SQL.
You have to use dynamic query (execute immediate, dbms_sql) in PL/SQL.
But why not 2 queries if you only have 2 tables?
Well, even with 100 tables, you have to use 100 queries if you want to avoid performances issue.

Regards
Michel
Re: dynamic table name [message #237798 is a reply to message #237782] Tue, 15 May 2007 13:47 Go to previous messageGo to next message
dryinego
Messages: 16
Registered: May 2007
Junior Member
Humm.. ok
Thanks..
Re: dynamic table name [message #237799 is a reply to message #237798] Tue, 15 May 2007 13:51 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
Have you looked into using a PIPELINED FUNCTION and returning everything as a character?

This would mean that you have to create a function to retrieve the value. That way I believe you could accomplish what you want in a sql query.
Re: dynamic table name [message #237822 is a reply to message #237782] Tue, 15 May 2007 16:06 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
You could do something like the following

select col1,col2,col3,str4
from my_table1
where '&which_one' = '1'
union all
select col1,col2,col3,to_char(int5)
from my_table2
where '&which_one' = '2';


This will return the integer value an a number, but will return only from one table at a time. which_one would be a variable that will contain the flag for which query to run.
Re: dynamic table name [message #237838 is a reply to message #237822] Tue, 15 May 2007 23:19 Go to previous message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good trick, assuming the OP use SQL*Plus.

Regards
Michel
Previous Topic: Pl/sql examples
Next Topic: help with queries
Goto Forum:
  


Current Time: Wed Dec 07 10:29:40 CST 2016

Total time taken to generate the page: 0.24286 seconds