RE: 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 06:28:34 -0800
Message-ID: <F001.004EBF5B.20021017062834@fatcity.com>

Hi,

I've found the answer.

DECLARE
countval NUMBER;
curid BINARY_INTEGER;
retval NUMBER;
BEGIN for i in (select OWNER,TABLE_NAME from DBA_TABLES WHERE ROWNUM) loop

  curid:= dbms_sql.open_cursor;
  dbms_sql.parse( curid, 'BEGIN SELECT count(*) INTO :cntval FROM '||

     i.owner||'.'||i.table_name||'; END;', dbms_sql.v7 );   dbms_sql.bind_variable( curid, 'cntval', countval );   retval:= dbms_sql.execute( curid );

  dbms_sql.variable_value( curid, 'cntval', countval );
  dbms_sql.close_cursor( curid );
  dbms_output.put_line( 'Count is:= ' || countval );

end loop;

END;
/

Regds,
New Bee

		-----Original Message-----
		From:	CHAN Chor Ling Catherine (CSC) 
		Sent:	Thursday, October 17, 2002 4:12 PM
		To:	'ORACLE-L_at_fatcity.com'
		Subject:	How to get the count of all tables using

dbms_sql

                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 - 09:28:34 CDT