Wrox Home  
Search P2P Archive for: Go

  Return to Index  

sql_language thread: counts on multiple tables


Message #1 by timbal25@y... on Thu, 15 Aug 2002 20:38:10
I'm trying to do a count on all tables in one script.  I have set the 
variable tabname to change for each table but i get a message stating that 
tabname is not declared.  
PLS-00201: identifier 'TABNAME' must be declared

Can you not use a variable in the FROM statement?

--- Start of script
create table tab_count (tablename varchar2(15), reccount number);
DECLARE
	recs 	number;
	tabname tab.tname%TYPE;

cursor tabrange is
	select tname
	from tab

cursor tabcnt is
	select count(*)
	from tabname;
BEGIN

open tabrange;
fetch tabrange into tabname;
while tabrange%found loop	
	open tabcnt;
		fetch tabcnt into recs;
		insert into tab_count (tablename, reccount)
		values (tabname, recs);
	close tabcnt;
fetch tabrange into tabname;
end loop;
close tabrange;
END;
/
select * from tab_count;
drop table tab_count;
-- End Script


  Return to Index