Re: all table names with row counts?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 27 Jan 1999 22:36:04 GMT
Message-ID: <36b09134.117581703@inet16.us.oracle.com>

On Wed, 27 Jan 1999 21:38:48 +0000, "Christopher M. Day" <christopher.day_at_rdbms.freeserve.co.uk> wrote:

But wait till 8i! This example will allow you to dynamically execute ANY sql statement ( DDL or DML ) inside a sql ANY statement or trigger.

eg.

Connected to:
Oracle8 Enterprise Edition Release 8.1.3.0.0 - Beta With the Partitioning and Objects options PL/SQL Release 8.1.3.0.0 - Beta

SQL> create or replace function count_of( p_tname in varchar2 ) return number   2 as
  3 pragma autonomous_transaction;
  4 l_cnt number;
  5 begin
  6 execute immediate 'select count(*) cnt from ' || p_tname   7 into l_cnt;
  8 return l_cnt;
  9 end;
10 /
Function created.

SQL> select table_name, count_of( table_name ) from user_tables 2 /

TABLE_NAME                     COUNT_OF(TABLE_NAME)
------------------------------ --------------------
ARTICLES                                          8
AUDIT_TRAIL                                      30
CLASSIFICATION                                    0
COMMUNITIES                                      44
COMMUNITIES_SAVE                                 40
DEBUGTAB                                          0
LABELS                                            0
MEMBERS                                           6
PEOPLE                                            4
PROFILE_VALUES                                    0
STICKIES                                          0
WWV_DOCUMENT                                      7
WWV_DOCUMENTPART                                  0

13 rows selected.

chris.

>Vadim,
>
>You wouldn't be allowed to use dynamic SQL (DBMS_SQL) in the count_rows
>function as its purity level is not set.(See PRAGMA RESTRICT_REFERENCES)
>
>Chris
>
>Vadim Tropasko wrote:
>>
>> Well, if I make
>>
>> select count(*) from table_name
>>
>> to be a function with 'table_name' argument, then
>>
>> select table_name, count_rows(table_name) from all_tables
>>
>> will do the job. I dont seem to need a cursor for that. I would have to
>> use dynamic SQL
>> in count_rows() function, though.
>>
>> Jonathan Lewis wrote:
>>
>> > Vadim Tropasko wrote in message <36AF6683.8816B009_at_us.oracle.com>...
>> > >I want to perform query like this
>> > >
>> > >select table_name, (select count(*) from table_name) from all_tables
>> > >
>> > >but cannot fugure out how to build join correctly. Any ideas?
>> > >
>> > >
>> > >No procedural solution, please.
>> > >
>> >
>> > Does this also preclude using a procedure that returns a REF CURSOR
>> > so that you can use SQL*Plus to do the format and presentation side
>> > of the report ?
>> >
>> > Jonathan Lewis
>> > Yet another Oracle-related web site: www.jlcomp.demon.co.uk

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jan 27 1999 - 16:36:04 CST