vineri, 24 martie 2017

Grant select on all tables and views (except the invalid views) of a schema to another user

begin
 

for i in (select object_name from user_objects where object_type in ('TABLE','VIEW')) LOOP
 

  begin
   execute immediate 'grant select on '||i.object_name||' to    

   grantee_user';
   EXCEPTION

   WHEN OTHERS THEN 
    dbms_output.put_line('err_num: '||SQLCODE);
    dbms_output.put_line('err_msg: '||SUBSTR(SQLERRM, 1, 100));
    dbms_output.put_line('object_name: '||i.object_name);
    continue;
   END;
 end loop;
end;
/