Oracle DB Dynamic SQL Statements and Temporary Table.sql

Oracle DB Dynamic SQL Statements and Temporary Table.sql

--===========================================================
-- To count all tables rows of current database
-- with: Dynamic SQL Statements, Temporary Table, Cursor
--===========================================================

-- create temporary table
CREATE GLOBAL TEMPORARY TABLE t_TableRowCnt  (
  TableName VARCHAR2(50)
, RowCnt NUMBER
) ON COMMIT DELETE ROWS;

DECLARE 
    vRowCnt NUMBER;
    vTableName VARCHAR2(100);
    vDbCmd VARCHAR2(500);
    CURSOR c_Tables IS 
        SELECT TABLE_NAME FROM user_tables WHERE TEMPORARY <> 'Y';
BEGIN
    DELETE FROM t_TableRowCnt;

    FOR rec IN c_Tables
    LOOP
        vTableName := rec.TABLE_NAME;
        vDbCmd := 'SELECT COUNT(*) FROM ' || vTableName;
        
        EXECUTE IMMEDIATE vDbCmd INTO vRowCnt;
        
        INSERT INTO t_TableRowCnt VALUES(vTableName, vRowCnt);
    END LOOP;
END;

-- show result
SET LINESIZE 2000;
SELECT * FROM t_TableRowCnt;

-- drop temporary table
DROP TABLE t_TableRowCnt;