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: '[EMAIL PROTECTED]'
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
