Re: Count # of records

From: Bricklen Anderson <bricklen_at_shaw.ca>
Date: Mon, 08 Apr 2002 15:48:13 GMT
Message-ID: <3CB1BAEB.5088BAB1@shaw.ca>

maybe this will work?

spool temp.sql
begin
for x in (select owner,table_name from all_tables where

owner='your_owner_name') loop
  dbms_output.put_line('PROMPT '||x.owner||'.'||x.table_name);
  dbms_output.put_line('SELECT COUNT(*) FROM '||x.table_name||';');

  end loop;
end;
/
spool off
@@temp.sql

Robert wrote:
>
> 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 - 10:48:13 CDT