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.