Can you store the results of a query in a variable ?

[MikeHendy]

Hi there,

I haven't seen this before, but thought it should be possible...

Is it possible to place the results of a query into a variable, which can then be used in another query (or nested query)?

example: I want to select the tablename from a table into a variable and then use the variable (table name) to select the data from the appropriate table. Any ideas?


Thanks in advance,
Mike.

 

[jee]

You can do this within a PL/SQL block. If your variable is your tablename, you will have to use Dynamic SQL to execute your second SQL statment, which is avaiable either via the DBMS_SQL package (supplied with Oracle) or the EXECUTE IMMEDIATE statement in PL/SQL. The latter method is the "preferred" method. The former method is just provided for compatibility with earlier versions.

 

[MikeHendy]

Can you think of any way it could be done within SQL without having to rely on a PL/SQL block ?

Is it even possible in raw SQL ?

 

[Triton46]

I don't think you can do this in SQL. You have to set up a varray or array in Pl/SQL in order to hold the results then loop the results back out.

 

[JamesGordon]

Try something like:

1) col var1 new_value tn noprint
2) select col var1 from some_table;
3) select * from &tn where...;

1. Sets up a variable to hold a value.
2. Selects a value from a query into the variable.
Notice that you give the column the same alias as the column name.
3. Use a select that references the value 'tn' as the from clause.

 

[lokesh123]

You could simply use a subquery, which may look something like this,

Select * from table_A where x= (Select tablename from table_B),

where 'x' is the field which will contain the values corresponding to those in the table table_B

 

[JamesGordon]

Lokesh123's idea implies you know the name of the table in advance. If not try my idea or another archaine idea below.

If you are really desperate you could have an SQL file that created a temporary SQL file with the SQL statements dynamically created.

spool c:\sql_temp.sql
select 'select * from '||table_name||';'
from some_driving_table
where some_condition = some_value;
spool off
@c:\sql_temp.sql
host del c:\sql_temp.sql