Re: [PL/SQL] Help - Select into from a variable tablename?

From: Kris Kuhl <kkuhl_at_nswc.navy.mil>
Date: 1997/04/21
Message-ID: <1997Apr21.144840.5165@relay.nswc.navy.mil>#1/1

In article <5j97k7$1pn_at_news1-alterdial.uu.net>, music_at_waymark.com says...
If you are looking for stats on user's tables, why don't you query the
data dictionary?
Ref. Oracle7 Server Application Developer's Guide Appendix D for other data dictionary view descriptions. The ALL_TABLES view lets you look at stats on any tables to which you have access. It includes OWNER, TABLE_NAME, TABLESPACE_NAME, NUM_ROWS statistics plus more. Go into SQLPLUS and do a DESC to see more field names.

You could do:

   To report on all tables to which you have access from within SQL*Plus:

     BREAK ON OWNER SKIP 1

     SELECT OWNER, TABLE_NAME, NUM_ROWS
     FROM ALL_TABLES
     ORDER BY OWNER, TABLE_NAME
 

   To do it within a procedure:    

   Procedure CountTable(tbl char) is

      cnt number;

   BEGIN

     SELECT NUM_ROWS INTO CNT FROM ALL_TABLES
     WHERE TABLE_NAME = tbl
     DBMS_OUTPUT.PUT_LINE(tbl || ' ' || cnt);

   END; Will either of the above suggestions do it for you? Good Luck,
Kris Kuhl

>I need some help selecting into from a variable tablename.
>Example:
>
> Procedure CountTable(tbl char) is
> cnt number;
> BEGIN
> select count(*) into cnt from tbl;
> dbms_output.put_line (tbl || ' ' || cnt);
> end;
>
>PL/SQL interprets tbl as an existing table and fails. I would
 like to have a
>loop call this routine for each table in a user's schema yielding
 these
>results:
>
>Tablename Cnt
>--------- ---
>TABLE1 234
>TABLE2 823
>.
>.
>.
>
>Any ideas? Thanks in advance.
>
>--David H.
Received on Mon Apr 21 1997 - 00:00:00 CDT