Re: Select Count off tables.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 27 Jul 2001 05:04:58 -0700
Message-ID: <9jrlda0242o@drn.newsguy.com>

In article <9jqhl3$plg$1_at_gruvel.une.edu.au>, "Grahame says...
>
>How can I do this.
>
>For example
>
>If you firstly
>select distinct table_name from all_tab_columns
>where owner = 'SYS'
>
>You will return x number of rows
>
>Including
> USER_TABLES
> USER_SOURCE
>
>I want to have a query (PLSQL or SQL) where you can return the above record
>(most likely into a cursor) and the have another sql that does the following
>
>Select count(*), first_table_returned
>from first_table_returned;
>
>then select count(*), second_table_name_returned
>from second_table_name_retuned;
>
>etc, etc
>
>So that you would get the following output (from the above example)
>
>32,USER_TABLES
>343,USER_SOURCE
>
>etc, etc
>
>Any Ideas?
>
>
>

sigh, never a version in sight.....

Ok, i'll asssume Oracle8i or up. You can do this:

ops$tkyte_at_ORA8I.WORLD> create or replace   2 function get_rows( p_tname in varchar2 ) return number   3 as
  4 l_columnValue number default NULL;   5 begin

  6      execute immediate
  7         'select count(*)
  8            from ' || p_tname INTO l_columnValue;
  9  
 10      return l_columnValue;

 11 end;
 12 /

Function created.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> select user, table_name,   2 get_rows( user||'.'||table_name) cnt   3 from user_tables
  4 /

USER                           TABLE_NAME                            CNT
------------------------------ ------------------------------ ----------
OPS$TKYTE                      CHAINED_ROWS                            0
OPS$TKYTE                      DR$B_IDX$I                              0
OPS$TKYTE                      DR$B_IDX$K                              0
OPS$TKYTE                      DR$B_IDX$N                              0
OPS$TKYTE                      DR$B_IDX$R                             20
OPS$TKYTE                      DR$C_IDX$I                              0
OPS$TKYTE                      DR$C_IDX$K                              0
OPS$TKYTE                      DR$C_IDX$N                              0
OPS$TKYTE                      DR$C_IDX$R                             20
OPS$TKYTE                      EXTENTS_LOG                         22066
OPS$TKYTE                      IOT                                     0
OPS$TKYTE                      MYTABLE                                 0
OPS$TKYTE                      PARTITIONED                             0
OPS$TKYTE                      SCUBAGEAR                            2000
OPS$TKYTE                      T                                       1
OPS$TKYTE                      T1                                      3
OPS$TKYTE                      T2                                      2
OPS$TKYTE                      TBLNAME                                 1
OPS$TKYTE                      TEST                                10000
OPS$TKYTE                      TMP_ROW_COUNT                           0
OPS$TKYTE                      TT                                      0
OPS$TKYTE                      TTT                                     0
OPS$TKYTE                      T_TMP                                 100
OPS$TKYTE                      X                                       0

24 rows selected.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Fri Jul 27 2001 - 07:04:58 CDT