Re: Help, SQL Problem!!!

From: Matt Brennan <mbrennan_at_gers.antispam.com>
Date: Mon, 10 Aug 1998 22:16:03 GMT
Message-ID: <01bdc4ac$7e064100$049a0580@mcb>

This is a bit hokey, but you can spool the table names to a file by selecting from dba_tables (this is assuming you are doing this in UNIX, BTW - don't know how other ones work) and make that file a script.

1. Make a script called myscript.sql (for the purpose of this example) for your update:

set echo off
define table_to_update = '&1'
update &table_to_update
set blah blah blah...
/
undefine table_to_update
set echo on
exit;

2. Create another .sql script that creates a text file to run as a UNIX executable and create the file as a series of commands (one for each table) to call your script and feed it the table name as an input parameter:

set echo off
set feedback off
set verify off
set heading off
set pagesize 0
set linesize 33
set trimspool on
spool somefile
select 'sqlplus / @myscript '||table_name||' &'||wait from dba_tables
where table_name like 'SALES_000%'
order by table_name
/
spool off
set trimspool off
set linesize <whatever size>
set pagesize <whatever size>
set heading on
set verify on
set feedback on

!chmod 777 somefile.lst
!rm somefile
!mv somefile.lst somefile
!somefile

set echo on

It's a bit of a workaround, but it'll get the job done. --
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com
(Original email address is spam-blocked.)

Valerie St Esteve <val22.se_at_btinternet.com> wrote in article <35CF90B4.D1BECF6C_at_btinternet.com>...
> 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.
>
> Thanks in advance
>
> Val
>
>
Received on Mon Aug 10 1998 - 17:16:03 CDT