How about this

CREATE OR REPLACE function get_rows( p_tname in varchar2 ) return number as
l_columnValue number default NULL;
begin
execute immediate 'select count(*) from ' || p_tname INTO l_columnValue;
return l_columnValue;
end;

select table_name,get_rows(table_name) cnt from user_tables where nested='NO' and partitioned='NO';


-----Original message-----
From: "Whittle Jerome Contr 805 CSPTS/NCI " oracledba-ezmlmshield-x46829441.[Email address protected]
Date: Mon, 03 Jul 2006 14:50:33 -0500
To: "LazyDBA Discussion" [Email address protected]
Subject: RE: select count(*) from more than one table

> SELECT COUNT(*) "Tablename count", 'a_user' "Tablename" from a_user
> UNION ALL
> SELECT COUNT(*) "Tablename count", 'b_project' "Tablename" from
> b_project
> ORDER BY 2;
>
> Jerry Whittle
> COINS DBA
> NCI Information Systems Inc.
> jerome.whittle.[Email address protected]
> 618-622-4145
> -----Original Message-----
> From: Anthony Bernardino
> [mailto:oracledba-ezmlmshield-x37615808.[Email address protected]
>
> I need to get a count(*) from two tables.
> Example:
> a_user and b_project.
>
> I can't do a select count(*) from a_user, b_project because that is't
> accurate.
>
> I want the output to look like:
>
>
> Tablename count Tablename
> 98000 a_user
> 1000000 b_project
>
>
> --------
> website: http://www.LazyDBA.com
> Please don't reply to RTFM questions
> Oracle documentation is here: http://tahiti.oracle.com
> To unsubscribe: see http://www.lazydba.com/unsubscribe.html
> To subscribe: see http://www.lazydba.com
> By using this list you agree to these terms:http://www.lazydba.com/legal.html
>
DISCLAIMER:

This message contains privileged and confidential information and is intended only for the individual named. If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system.

Oracle LazyDBA home page