pl/sql-urgent [message #36716] |
Fri, 14 December 2001 01:20 |
norik
Messages: 2 Registered: December 2001
|
Junior Member |
|
|
hello all
"SELECT table_name FROM user_tables" GIVES THE LIST OF TABLES THE USER HAVE ,AND THEN IF I GIVE "SELECT count(*) FROM" on any table in the list will give me the records of that particular table. But what i want is a program which will give me the the number of records of all the tables which are listed by the first said select statement.
so can anybody give me a pl/sql program/procedure which can do this.
i am a novice in this field.i need it urgently.
----------------------------------------------------------------------
|
|
|
Re: pl/sql-urgent [message #36717 is a reply to message #36716] |
Fri, 14 December 2001 02:22 |
smk
Messages: 5 Registered: November 2001
|
Junior Member |
|
|
Hi,
First spool this query and then execute it, hope this will solve your problem.
select 'select count(*) from '||table_name||' ;'
from user_Tables
This will give the list of sql statements like select count(*) from emp; and so on.
Best Luck
Sanjay
----------------------------------------------------------------------
|
|
|
Re: pl/sql-urgent [message #36727 is a reply to message #36716] |
Fri, 14 December 2001 06:54 |
Suresh Vemulapalli
Messages: 624 Registered: August 2000
|
Senior Member |
|
|
try this:
declare
type ref1 is ref cursor;
r1 ref1;
l_count number;
cursor c1 is select * from user_tables;
begin
dbms_output.enable(100000);
dbms_output.put_line(rpad('TABLE NAME',30,' ')|| chr(9)||'NO OF RECORDS');
for crec in c1 loop
execute immediate 'select count(*) from '||crec.table_name into l_Count;
dbms_output.put_line(rpad(crec.table_name,30,' ')|| chr(9)||l_count);
end loop;
end;
HTH
SURESH
----------------------------------------------------------------------
|
|
|