RE: How to get the count of all tables using dbms_sql

From: Charu Joshi <joshic_at_mahindrabt.com>
Date: Mon, 21 Oct 2002 08:53:29 -0800
Message-ID: <F001.004EED40.20021021085329@fatcity.com>

If the database is 8i+, you can try 'EXECUTE IMMEDIATE' command. Does make life somewhat simpler.

Regards,
Charu

-----Original Message-----
Ling Catherine (CSC)
Sent: Thursday, October 17, 2002 3:25 PM To: Multiple recipients of list ORACLE-L

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).

*********************************************************
Disclaimer

This message (including any attachments) contains 
confidential information intended for a specific 
individual and purpose, and is protected by law. 
If you are not the intended recipient, you should 
delete this message and are hereby notified that 
any disclosure, copying, or distribution of this
message, or the taking of any action based on it, 
is strictly prohibited.

*********************************************************
Visit us at http://www.mahindrabt.com


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Charu Joshi
  INET: joshic_at_mahindrabt.com

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 Mon Oct 21 2002 - 11:53:29 CDT