Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> counting #rows in tables dynamically

counting #rows in tables dynamically

From: Daniel Clamage <dclamageNOSPAM_at_telerama.com>
Date: 2 Sep 1998 17:39:47 -0400
Message-ID: <01bdd68f$10d53ec0$ec28c9cd@saturn>


a 3rd method would be to use dynamic sql to run count(*) on every table. CREATE OR REPLACE FUNCTION numrows(

    Ptable_name IN user_tables.table_name%TYPE) RETURN NUMBER IS
/*

   Program Name: Number of Rows
   Module Name : numrows.sql
   Written By  : Daniel J. Clamage
   Description :

   This stored function returns the number of rows in a given    table. The query SELECT COUNT(*) FROM table_name is built    and executed dynamically. On an error (such as the table    name was not found) the function returns NULL.    Example:
   BEGIN
     DBMS_OUTPUT.put_line('#rows in EMP: ' ||
                          numrows('emp'));
   END;
   /
   Modification:
   V.001 02-JUN-1998 - djc - Initial release.    V.002 12-JUL-1998 - djc - Added cleanup, comments. */
  dyn_c BINARY_INTEGER := DBMS_SQL.open_cursor;   dyn_stmt VARCHAR2(32767);
  cnt       NUMBER := 0;
  dummy     INTEGER;

BEGIN

Then write a simple pl/sql block to run this routine for every table in the schema:
declare
  cnt number := 0;
begin
  for t in (select table_name from user_tables) loop     cnt := cnt + numrows(t.table_name);   end loop;
  dbms_output.put_line('total #rows in schema: ' || cnt); end;
/

--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough. Received on Wed Sep 02 1998 - 16:39:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US