Re: Calling all the SQL Guru's

From: Martin Haltmayer <Martin_Haltmayer_at_d2mail.de>
Date: Thu, 08 Mar 2001 18:43:01 +0100
Message-ID: <3AA7C4A5.A3D4614E@d2mail.de>

Try this:

create or replace
function my_count (i_tabname in user_tables.table_name%type) return number is

	type t is ref cursor;
	r t;
	l_retval number;
begin
	open r for 'select count (*) from "' || i_tabname || '"';
	fetch r into l_retval;
	close r;
	return l_retval;
exception
	when others then
	if r%isopen then
		close r;
	end if;	-- r%isopen then
	raise;

end my_count;
/
show errors

select table_name, my_count (table_name) from user_tables
order by 1;

I tested it on NT 4.0, Oracle 8.1.6.3.1

Martin

Mick Rice wrote:
>
> I was trying to devise a single piece of sql to generate a list of all
> the tables in the database and a count of the number of rows on each
> table. I started out confident that I could code this easily using a
> subquery and 'group by' statement. However an hour later, and
> confidence diminished, I havn't been able to get the syntax right. The
> best I could come up with was the pretty lame looking query following
> which wasn't what I was aiming for at all.
>
> set pagesize 0;
> spool count.sql;
> select 'select count(*) from '||owner||'.'|| table_name||';' from
> all_tables;
> spool off;
>
> Can anyone come up with a single statement which does this ?
>
> It's put me out of my misery,
>
> Thanks,
>
> Mick.
>
> --
> Back it up !
>
> Sent via Deja.com
> http://www.deja.com/
Received on Thu Mar 08 2001 - 11:43:01 CST