Procedure aaa(old_id  number
                   ) is
res varchar2(10);
begin
   for c in (select * from all_tab_columns where column_name = 'SUB_ID')
   loop
      res := bbb(c.owner, c.table_name, old_id);

      --еще много чего...

   end loop;
   dbms_output.put_line('OK');
   --еще много чего...
   exception when others then
      dbms_output.put_line('Error');
      null;
end;

Function bbb(p_owner varchar2,
                      p_table_name varchar2,
                      old_id  number
                      ) return number is
strque  varchar2(4000);
cnt    number;
begin
   strque := 'select count(*) from ' || p_owner || '.' || p_table_name || ' where sub_id = ' || old_id;
   execute immediate strque into cnt;
   
   return cnt;

end;

declare
 res number;
begin
 res := bbb('test','tbl_test', 2);
 dbms_output.put_line(res);
end;