> 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