%NOTFOUND

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>