This simple example was tested on Oracle 11.2. It uses SQL*Plus to list the tables owned by a user and counts the rows in each one:
SQL> select table_name
2 from dba_tables
3 where owner = 'ANDREW'
4 /
TABLE_NAME
------------------------------
T1
T2
T3
SQL> select count(*) from andrew.t1
2 /
COUNT(*)
----------
3082
SQL> select count(*) from andrew.t2
2 /
COUNT(*)
----------
4851
SQL> select count(*) from andrew.t3
2 /
COUNT(*)
----------
73942
SQL>
Then it does the same thing in PL/SQL. Note the use of %NOTFOUND to jump out of the loop when there is nothing more to return from the cursor:
SQL> DECLARE
2 CURSOR c1 is
3 SELECT owner||'.'||table_name fqn
4 FROM dba_tables
5 WHERE owner = 'ANDREW';
6 c1_rec c1%ROWTYPE;
7 row_count NUMBER;
8 sql_statement VARCHAR2(200);
9 BEGIN
10 OPEN c1;
11 LOOP
12 FETCH c1 INTO c1_rec;
13 EXIT WHEN c1%NOTFOUND;
14 sql_statement :=
15 'SELECT COUNT(*) FROM '||c1_rec.fqn;
16 DBMS_OUTPUT.PUT_LINE(sql_statement);
17 EXECUTE IMMEDIATE sql_statement
18 INTO row_count;
19 DBMS_OUTPUT.PUT_LINE(c1_rec.fqn||
20 ' has '||row_count||' rows');
21 END LOOP;
22 CLOSE c1;
23 END;
24 /
SELECT COUNT(*) FROM ANDREW.T1
ANDREW.T1 has 3082 rows
SELECT COUNT(*) FROM ANDREW.T2
ANDREW.T2 has 4851 rows
SELECT COUNT(*) FROM ANDREW.T3
ANDREW.T3 has 73942 rows
PL/SQL procedure successfully completed.
SQL>
