DECLARE
     lv_count_num     PLS_INTEGER:=0;
     lv_sql_txt      VARCHAR2(200);

     CURSOR cur_counts 
     IS
          SELECT table_name
          FROM user_tables
          ORDER BY table_name;
BEGIN

      FOR cur_counts_rec IN cur_counts 
      LOOP
        lv_sql_txt := 'SELECT count(*) INTO 
                      lv_count_num  FROM ' ||
                      cur_counts_rec.table_name;

        dbms_output.put_line (lv_sql_txt);
        EXECUTE IMMEDIATE lv_sql_txt; 

        DBMS_OUTPUT.put_line (' Table Name: ' ||
             RPAD(cur_counts_rec.table_name, 15) ||
             'Rows: ' || lv_count_num);
      END LOOP;
END;
/

  1  DECLARE
  2  lv_count_num PLS_INTEGER:=0;
  3  lv_sql_txt VARCHAR2(200);
  4  -- NUMBER(10) :=0;
  5  CURSOR cur_counts IS
  6  SELECT table_name
  7  FROM user_tables
  8  ORDER BY table_name;
  9  BEGIN
 10  FOR cur_counts_rec IN cur_counts LOOP
 11  dbms_output.put_line (cur_counts_rec.table_name);
 12  lv_sql_txt := 'SELECT count(*) into lv_count_num FROM '||
 13  cur_counts_rec.table_name;
 14  dbms_output.put_line (lv_sql_txt);
 15  EXECUTE IMMEDIATE lv_sql_txt;
 16  DBMS_OUTPUT.put_line (' Table Name: ' ||
 17  RPAD(cur_counts_rec.table_name, 15) || 'Rows: ' ||
 18  lv_count_num);
 19  END LOOP;
 20* END;
SQL> /
TEST
SELECT count(*) into lv_count_num FROM TEST
DECLARE
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 15

 12  lv_sql_txt := 'SELECT count(*) into lv_count_num FROM '||
 13  cur_counts_rec.table_name;
 14  dbms_output.put_line (lv_sql_txt);
 15  EXECUTE IMMEDIATE lv_sql_txt into lv_count_num;

 to

 12  lv_sql_txt := 'SELECT count(*) FROM '||
 13  cur_counts_rec.table_name;
 14  dbms_output.put_line (lv_sql_txt);
 15  EXECUTE IMMEDIATE lv_sql_txt into lv_count_num;