Re: Rookie Question

> Hello,
> possibly it is a very simple "rookie" question but:
> This statement don't work. why?
>
> DECLARE
> CURSOR c1 IS
> SELECT a.tablename FROM info_tablerows a;
> rows number;
> BEGIN
> FOR r IN c1 LOOP
> SELECT count(*) INTO rows FROM r.tablename;
> UPDATE info_tablerows SET rowcount = rows WHERE tablename =
> r.tablename;
> END LOOP;
> END;
> /
>
> At "SELECT count(*) INTO rows FROM r.tablename;" the message
> "identifier r.tablename must be declared" appears.
> Isn't it possible to reference my tablename?
>
> regards ruediger

Ruediger,

You might want to try dmbs_stats.gather_table_stats alternatively:

create table t1 (a number);

begin
  for i in 100 .. 110 loop
    insert into t1 values (i);
  end loop;
end;
/

create table t2 (a number);

begin
  for i in 1000 .. 1100 loop
    insert into t2 values (i);
  end loop;
end;
/

create table t3 (a number);
begin
  for i in 2000 .. 2200 loop
    insert into t3 values (i);
  end loop;
end;
/

commit;

create table info_tablerows (
  tablename varchar2(30) primary key
);

insert into info_tablerows values ('t1');
insert into info_tablerows values ('t2');
insert into info_tablerows values ('t3');

commit;

declare
  cursor c is select tablename from info_tablerows; begin
  for r in c loop
    dbms_output.put_line(r.tablename);
    dbms_stats.gather_table_stats(user,r.tablename);   end loop;
end;
/

create view v_num_rows as
  select num_rows, table_name from
  user_tables u, info_tablerows i where
  u.table_name = upper(i.tablename);

select * from v_num_rows;  

hth
Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html