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'

If you want simple SQL for Oracle (e.g. have XE with no XmlGen) go for a simple 2-step:

select ('(SELECT ''' || table_name || ''' as Tablename,COUNT(*) FROM "' || table_name || '") UNION') from USER_TABLES;

Copy the entire result and replace the last UNION with a semi-colon (';'). Then as the 2nd step execute the resulting SQL.

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.

select owner, table_name, num_rows, sample_size, last_analyzed from all_tables;

This is the fastest way to retrieve the row counts but there are a few important caveats:

1. NUM_ROWS is only 100% accurate if statistics were gathered in 11g and above with ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE (the default), or in earlier versions with ESTIMATE_PERCENT => 100. See this post for an explanation of how the AUTO_SAMPLE_SIZE algorithm works in 11g.
2. Results were generated as of LAST_ANALYZED, the current results may be different.