Re: Is there anyway to use a tablename in subquery?

: Is there anyway to use a tablename in subquery like this:

: SELECT * FROM ( SELECT tablebame FROM TABLE);

Not directly, and not in plain sql.

You can build the statement as a string using pl/sql and the run it using EXECUTE IMMEDIATE. (google: Oracle Execute Immediate)

You can do the equivalent using COLUMN NEW_VALUE within SQLPlus.

	column TABLE_NAME new_value THE_TABLE_NAME
	select table_name FROM TABLE;
	select * from &THE_TABLE_NAME ;

You can build a view that UNIONS a whole bunch of similar tables together and then select against that view.

	create bunch_of_tables as
	select 'TABLE1' table_name ,t1.* from TABLE1 t1
	UNION ALL
	select 'TABLE2' table_name ,t2.* from TABLE2 t2

	(in real life you would explicily select the columns you needed).

Probably other techniques, but none of them do what you want directly.

$0.10 Received on Wed May 27 2009 - 00:51:19 CDT