oracle – apply query to multiple tables

Let’s say you want to grant rights on a set of tables to a given user, here is how to do it:

begin
for c in (
select 'grant select on '||owner||'.'||table_name||' to newuser' cmd
from all_tables where owner = 'myowner' and table_name like 'TABLE_NAME_%'
)
loop
execute immediate c.cmd;
end loop;
end;
/

Note: same logic can be extended to apply any type of query to multiple tables

Post a Comment

Your email is never published nor shared. You're allow to say what you want...