declare
  v_score number;
begin
  for cur_r in (select table_name, owner from table1 union
                select table_name, owner from table2)
  loop
    select max(score) 
      into v_score
      from table4 q
      where q.table_name = cur_r.table_name
        and q.owner = cur_r.owner;
  end loop;
end;

create or replace
procedure test_procedure (p_owner          in varchar2,
                       p_table_name     in varchar2) is

  v_owner varchar2(50) := upper(p_owner);
  v_table_name varchar2(50) := upper(p_table_name);
  v_score number := 0;
  v_table_score number := 0;

  begin

      while v_table_score <> 99
      loop
         select nvl(max(score),0)
           into v_score
           from ds.ri_mng_queue q
          where exists (select 'Y'
                          from (select t.dim_table_name tab_name, 
                                       t.dim_owner owner
                                  from ds.ri_relationships t
                                 where t.fact_table_name = v_table_name
                                   and t.fact_owner = v_owner
                                 union
                                select t.fact_table_name tab_name, 
                                       t.fact_owner owner
                                  from ds.ri_relationships t
                                 where t.dim_table_name = v_table_name
                                   and t.dim_owner = v_owner) t
                                 where q.table_name = t.tab_name
                                   and q.owner = t.owner);

         if v_score = 0 and v_table_score = 0 then
         insert into ds.ri_mng_queue values (1, p_table_name, p_owner, sysdate);
         v_table_score := 99;

         else if v_table_score = 0 then
         v_table_score := v_score + 1;
         insert into ds.ri_mng_queue values (v_table_score, p_table_name, p_owner, sysdate);
         dbms_lock.sleep(10);

         else if v_table_score <> 0 and v_score = 0 then
         v_table_score := 99;

         else
         dbms_lock.sleep(10);
         end if; 
         end if; 
         end if;

    commit;
    end loop;

end test_procedure;