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...


Warning: fsockopen() [function.fsockopen]: php_network_getaddresses: getaddrinfo failed: Name or service not known in /home/remydamo/websites/qc4blog/www/wp-content/plugins/sweetcaptcha-revolutionary-free-captcha-service/library/sweetcaptcha.php on line 81

Warning: fsockopen() [function.fsockopen]: unable to connect to www.sweetcaptcha.com:80 (php_network_getaddresses: getaddrinfo failed: Name or service not known) in /home/remydamo/websites/qc4blog/www/wp-content/plugins/sweetcaptcha-revolutionary-free-captcha-service/library/sweetcaptcha.php on line 81