Oracle Discussions

[Prabhat Kumar]

I would like to fetch all table rows count. I am using these strategy -
Create a Cursor to fetch all table name and read one by one. and this fetched result i will send it select statement, but my select statement does not recognize table name. How i achive this result.

[vijaydiwakar]

I would like to fetch all table rows count. I am using these strategy -
Create a Cursor to fetch all table name and read one by one. and this fetched result i will send it select statement, but my select statement does not recognize table name. How i achive this result.

show me thy qry

[rectoverso]

show me thy qry

you can do it using dynamic sql in the procedure
declare
i number := 0;
j number := 0;
begin
for c in ( select table_name from user_tables ) loop
execute immediate ' select count(*) from '||c.table_name into i;
j := j + 1;
end loop;
dbms_output.put_line(' my total count is '||j);
end;

[rectoverso]

you can do it using dynamic sql in the procedure
declare
i number := 0;
j number := 0;
begin
for c in ( select table_name from user_tables ) loop
execute immediate ' select count(*) from '||c.table_name into i;
j := j + 1;
end loop;
dbms_output.put_line(' my total count is '||j);
end;

excuse

the script is

declare
i number := 0;
j number := 0;
begin
for c in ( select table_name from user_tables ) loop
execute immediate ' select count(*) from '||c.table_name into i;
j := j + i;
end loop;
dbms_output.put_line(' my total count is '||j);
end;