Re: iterating through all_tables

"Jim Kennedy" <kennedy-down_with_spammers_at_attbi.com> wrote in message news:<AXEta.747959$L1.212695_at_sccrnsc02>...
> Not sure why one would want to, but look at NDS(native dynamic sql or
> dbms_sql package)
> Jim
>
> --
> Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
> with family. Remove the negative part, keep the minus sign. You can figure
> it out.
> "christopher lambert" <cjlambert_at_llnl.gov> wrote in message
> news:1932d1c8.0305051457.3efca49_at_posting.google.com...
> > I'd like to do the equivalent of a foreach loop with oracle. something
> like this..
> >
> > foreach i (`select table_name from all_tables`)
> > select * from $i;
> > end
> >
> > does anyone know how to do this?
> >
> > - chris

Chris, Look at the following cooperative FAQ article which contains pl/sql that uses dynamic SQL as Jim mentioned to select count(*) from all [selected] tables in your application. It would be any easy mod, but the dbms_output buffer is limited to 1M in total size. You might have to replace it with utl_file.

Is there a good way of counting the number of rows in a table ? http://www.jlcomp.demon.co.uk/faq/count_rows.html

Also it is easy to use SQL to generate a sqlplus script with a 'select *' for every desired table on your system and your output is limited only by the space available for your spool file.

HTH -- Mark D Powell -- Received on Tue May 06 2003 - 09:11:32 CDT