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

Home -> Community -> Mailing Lists -> Oracle-L -> How to get the count of all tables using dbms_sql

How to get the count of all tables using dbms_sql

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

   /* 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

Original text of this message

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