Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql-urgent
pl/sql-urgent [message #36716] Fri, 14 December 2001 01:20 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

----------------------------------------------------------------------
Previous Topic: variable concat.
Next Topic: HELP PLEASE!
Goto Forum:
  


Current Time: Wed Apr 01 07:24:35 CDT 2020