title: Execute For Each Table in PLSQL
body: <p>I want to the the number of records in all tables that match a specific name criteria. Here is the SQL I built</p>
  
  <pre><code>Declare SQLStatement VARCHAR (8000) :='';
  BEGIN
    SELECT 'SELECT COUNT (*) FROM ' || Table_Name || ';'
    INTO SQLStatement
    FROM All_Tables
    WHERE 1=1
      AND UPPER (Table_Name) LIKE UPPER ('MSRS%');
  
    IF SQLStatement &lt;&gt; '' THEN
      EXECUTE IMMEDIATE SQLStatement;
    END IF;
  END;
  /
  </code></pre>
  
  <p>But I get the following error:</p>
  
  <pre><code>Error at line 1
  ORA-01422: exact fetch returns more than requested number of rows
  ORA-06512: at line 3
  Script Terminated on line 1.
  </code></pre>
  
  <p>How do I modify this so that it runs for all matching tables?</p>
  
  <p><strong>Update:</strong> </p>
  
  <p>Based on an answer received, I tried the following but I do not get anything in the DBMS_OUTPUT</p>
  
  <pre><code>declare 
    cnt number;
  begin
    for r in (select table_name from all_tables) loop
      dbms_output.put_line('select count(*) from CDR.' || r.table_name);
    end loop;
  end;
  /
  </code></pre>