Get counts of all tables in a schema

This can be done with a single statement and some XML magic:

select table_name, 
       to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as count
from all_tables
where owner = 'FOOBAR'

This should do it:

declare
    v_count integer;
begin

    for r in (select table_name, owner from all_tables
              where owner = 'SCHEMA_NAME') 
    loop
        execute immediate 'select count(*) from ' || r.table_name 
            into v_count;
        INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)
        VALUES (r.table_name,r.owner,v_count,SYSDATE);
    end loop;

end;

I removed various bugs from your code.

Note: For the benefit of other readers, Oracle does not provide a table called STATS_TABLE, you would need to create it.