Home » SQL & PL/SQL » SQL & PL/SQL » calculate rows of user tables
calculate rows of user tables [message #38307] Tue, 09 April 2002 11:24 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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;
Previous Topic: How can I create synonyms iteratively ?
Next Topic: How the following query order the result starting from monday?
Goto Forum:
  


Current Time: Fri Mar 29 09:16:45 CDT 2024