Re: Count # of records

From: Igor Izvekov <igoriz_at_cmtk.net>
Date: 8 Apr 2002 14:36:17 -0700
Message-ID: <9f17469e.0204081336.33a11283@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 Mon Apr 08 2002 - 16:36:17 CDT