Re: Help, SQL Problem!!!

Valerie St Esteve wrote:
>
> Hi
>
> I hope this doesn't seem like a silly question but I'm new to all this.
> Is there any way to dynamically alter the FROM part of a select
> statement either in SQL or PL/SQL.
>
> My requirement is this: I have to gather data from 52 tables all of
> which have an identical structure. They are only differentiated by their
> names. The first is called sales_00001, the next sales_00002 and so on.
> All the data has to be spooled into a single text file in one go.
> Apart from coding a query that has 52 selects all unioned together with
> hardcoded table names for each of the tables, is there any way of having
> a single select statement, say within some kind of loop that has its
> from part dynamically altered for each iteration of the loop. If so, how
> do I achieve this? I can't find out how it is done anywhere.

You may be saved because you have that naming-convention. I suggest something like:

spool selectstmts.sql
select 'Select * From ' || u1.Table_name || ';' from User_Tables u1
where u1.Table_Name like 'SALES_00%';
spool off

spool datacoll.lst
start selectstmts.sql
spool off

You have to apply some statements like 'set heading off', 'set feedback off', I'm not quite sure how many or which ones, anyway that's in the SQL*Plus users guide.
>
> Thanks in advance
>
> Val

Good luck,
Njål
--
Njål Arild Ekern, ADB-seksjonen USIT
Postboks 1059 Blindern, 0316 OSLO
Tlf 22852477, fax 22852730 Received on Mon Aug 10 1998 - 20:38:39 CDT