calculate rows of user tables [message #38307] |
Tue, 09 April 2002 11:24 |
George
Messages: 68 Registered: April 2001
|
Member |
|
|
Hi, I want to calculate how many rows of each table of a user. but my code give me the same numbers for all tables> could you help me to fix this:
set serveroutput on
declare
type rc is ref cursor;
lc rc;
tab varchar2(128);
u varchar2(128);
no number(10);
invalid exception;
begin
dbms_output.enable(10000);
select user into u from dual;
if u in ('SYS','SYSTEM') then
raise invalid;
end if;
open lc for 'select table_name from user_tables';
loop
fetch lc into tab;
select count(*) into no from tab ;
exit when lc%notfound;
dbms_output.put_line(' ' || tab || ' has records: '|| to_char(no) );
end loop;
close lc;
exception
when invalid then
raise_application_error(-20000,'Do not run this script as SYS or SYSTEM!!!!!!!!!!');
end;
/
|
|
|
Re: calculate rows of user tables [message #38308 is a reply to message #38307] |
Tue, 09 April 2002 11:40 |
Rick Cale
Messages: 111 Registered: February 2002
|
Senior Member |
|
|
I think you have to use dbms_sql or execute_immediate
because you are using dynamic sql.
I am not sure of exact syntax but you could try
execute_immediate ('select count(*) into no from ' || :tab);
Another alternative is to analyze the tables which would populate num_rows column.
|
|
|
Re: calculate rows of user tables [message #38310 is a reply to message #38307] |
Tue, 09 April 2002 12:30 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Here is an alternative that only uses one variable, a cursor for loop (no open/fetch/close), and dynamic SQL:
declare
v_count pls_integer;
begin
if user not in ('SYS', 'SYSTEM') then
for r in (select table_name from user_tables) loop
execute immediate 'select count(*) from ' || r.table_name into v_count;
dbms_output.put_line( 'Table ' || r.table_name || ' has ' || v_count || ' rows');
end loop;
end if;
end;
|
|
|