Oracle-L: How to get the count of all tables using dbms

From: CHAN Chor Ling Catherine (CSC) <clchan_at_nie.edu.sg>
Date: Thu, 17 Oct 2002 01:18:32 -0800
Message-ID: <F001.004EBBCF.20021017011832@fatcity.com>

Hi,

I need to insert the total number of records all the tables into the table, MIGRATION_TABLE. I want to use the
How do I obtain the count(*) into a variable in "dbms_sql.parse(cid, 'SELECT COUNT(*) FROM ' ||i.owner||'.'||i.table_name, dbms_sql.v7); " ?

TIA Declare

cid INTEGER;
BEGIN
for i in (select OWNER,TABLE_NAME from DBA_TABLES) loop

Open new cursor and return cursor ID. cid := dbms_sql.open_cursor;

/* Parse and immediately execute dynamic SQL statement built by

      concatenating table name to DROP TABLE command.  (Unlike DML 
      statements, DDL statements are executed at parse time.) */

dbms_sql.parse(cid, 'SELECT COUNT(*) FROM ' ||i.owner||'.'||i.table_name, dbms_sql.v7);

/* Close cursor. */

dbms_sql.close_cursor(cid);

end loop;

EXCEPTION
   /* If an exception is raised, close cursor before exiting. */    WHEN OTHERS THEN
      dbms_sql.close_cursor(cid);
END; Regds,
New Bee
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: CHAN Chor Ling Catherine (CSC)
  INET: clchan_at_nie.edu.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Oct 17 2002 - 04:18:32 CDT

How to get the count of all tables using dbms_sql