gen_grant_exec.sql

set echo off feedback off verify off pagesize 0 linesize 120

define v_grantee                 = &1

define v_grant_exec_command_file = .\log\grant_exec_&v_grantee..sql

define v_grant_exec_log_file     = .\log\grant_exec_&v_grantee..log

 

spool &v_grant_exec_command_file.
prompt spool &v_grant_exec_log_file.
prompt set echo on feedback on
prompt show user
select
  'grant execute on ' || o.object_name || ' to &v_grantee;'
from     user_objects o
where object_type in ('PACKAGE','FUNCTION','PROCEDURE')
and   not exists
  (select null
   from   user_tab_privs p
   where  p.owner      = user
   and    p.table_name = o.object_name
   and    p.grantee    = upper('&v_grantee'))
and user != upper('&v_grantee')
order by o.object_name
/
prompt set echo off feedback off
prompt spool off
spool off

@&v_grant_exec_command_file.