set serveroutput on;
DECLARE

str_tab_name               VARCHAR2(100);
str_tab_owner               VARCHAR2(20);
  num_contain_QT_loc        number;
  str_exec_grant  varchar2(200);
  
   cursor cur is
         SELECT distinct(tb_pri.table_name) as TB_NAME,tb_pri.owner as TB_OWNER FROM dba_tab_privs tb_pri inner join 
                  (select owner,table_name from dba_tables where owner in ('BUAT_Meta','BUAT_AUDIT','BUAT_MAINT','BUAT_METRICS','BUAT_TRANS')) tb_info 
                    on tb_pri.table_name = tb_info.table_name and tb_pri.owner = tb_info.owner;
         
BEGIN

  open cur;
   loop
     fetch cur into str_tab_name,str_tab_owner;
     exit when cur%notfound;
     
      select length(str_tab_name) - INSTR(str_tab_name,'_QT') into num_contain_QT_loc from dual;
      if num_contain_QT_loc > 2 then
        str_exec_grant := 'GRANT DELETE,INSERT,SELECT,UPDATE ON '|| str_tab_owner ||'.'||str_tab_name||' TO GCE1_DMM_UPDATE_ROLE';
         dbms_output.put_line('str_tab_name :'||str_tab_name || ',str_tab_owner : ' || str_tab_owner || ',grant 4.' );
         
      else  
        str_exec_grant := 'GRANT SELECT ON '|| str_tab_owner ||'.'||str_tab_name||' TO R2_DMM_READONLY_ROLE';
         dbms_output.put_line('str_tab_name :'||str_tab_name || ',str_tab_owner : ' || str_tab_owner  || ',grant select.');
      end if;
      dbms_output.put_line('sql :' || str_exec_grant);
      execute immediate str_exec_grant;
     
      
   end loop;
   
   close cur;
   
 exception
   when others
   then
     if cur%isopen
     then 
       close cur;
     end if;
 
END;
/