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

Reply via email to