Re: Count # of records

This solution works just great.
Thanks for helping me out.

Robert.

"Igor Izvekov" <igoriz_at_cmtk.net> wrote in message news:9f17469e.0204081336.33a11283_at_posting.google.com...
> Try using dynamic SQL :
>
> declare
> cnt number;
> begin
> for line in (select owner,table_name from all_tables
> where owner='in_schema_name')
> loop
> execute immediate
> 'select count(*) from '||line.owner||'.'||line.table_name into
> cnt;
> dbms_output.put_line(line.table_name||' has '||cnt||' lines');
> end loop;
> end;
> /
>
> This way you don't have to analyze tables . Do you want to analyze
> them every time you want to execute this ? I doubt it.
>
> "Robert" <robert_mulder_at_hotmail.com> wrote in message
news:<hvis8.38$PA6.1023_at_nreader1.kpnqwest.net>...
> > Hi all,
> >
> > I'm not that experienced in writing PL/SQL code and now I have to write
a
> > PL/SQL procedure to count the number of records in a given schema.
> > So I created a cursor that retrieves all table names for a given schema,
> > like ex. below
> > CURSOR snaps_to_run IS
> > SELECT s.owner || '.' || s.table_name as get_table_names
> > FROM all_tables s
> > WHERE s.owner=in_schema_name
> > ORDER BY s.table_name
> >
> > In a loop I want to count the number of records for each table, and
that's
> > were it all goes wrong. I can't just say "select count(*) into variable
from
> > get_table_names, because it can't resolve the actual table name.
Apparently
> > I have to bind the real value.
> >
> > Can anyone help me with this?
> >
> > Thanks in advance,
> >
> > Robert.
Received on Tue Apr 09 2002 - 04:02:09 CDT