"CHAN Chor Ling Catherine (CSC)" wrote: > > 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 > --
Read DBMSQL.SQL (under $ORACLE_HOME/rdbms/admin), you have examples in the comments. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).