"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: sfaroult_at_oriole.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 Thu Oct 17 2002 - 05:39:02 CDT
