可以参考下这个

create table TAB_COUNT_DM(OWNER varchar(100),tablename varchar(100),tab_count int,c_time TIMESTAMP DEFAULT sysdate);

create or REPLACE PROCEDURE P_TABLE_COUNT(OWN IN VARCHAR(100)) is 
v_count int;
cursor v_tab_count is select owner,table_name,'SELECT COUNT(*) FROM '||OWNER||'."'||table_name||'";'  s_sql from all_tables where owner = OWN; 
begin
for cnt_rec in v_tab_count 
LOOP
    execute immediate cnt_rec.s_sql into v_count;
    insert into TAB_COUNT_DM(OWNER,tablename,tab_count) values(cnt_rec.owner,cnt_rec.table_name,v_count);
    commit;
END LOOP;
end;
/

call P_TABLE_COUNT('DBO');

试下把表名拼接上双引号：v_sql:='select count(*) from DBO."'||a.table_name||'"';