Pages

Tuesday, July 11, 2017

UTL_MAIL error: utl_mail must be declared

=================================
General
=================================
Below error is coming when executing procedure inside UTL_MAIL package:
UTL_MAIL error: utl_mail must be declared

Most likely, the permissions are missing

=================================
UTL_MAIL
=================================
SELECT owner, object_name, object_type 
FROM DBA_OBJECTS 
WHERE object_name = 'UTL_MAIL';

OWNER      OBJECT_NAME                    OBJECT_TYPE
---------- ------------------------------ --------------------
SYS        UTL_MAIL                       PACKAGE BODY
SYS        UTL_MAIL                       PACKAGE
PUBLIC     UTL_MAIL                       SYNONYM

connect as sysdba, and grant missing permission to users:

 sqlplus / as sysdba

SQL> GRANT EXECUTE ON UTL_MAIL TO NEW_USER;

Grant succeeded.

=================================
UTL_MAIL permissions 
=================================
Current status:
SELECT * FROM DBA_TAB_PRIVS WHERE owner = 'SYS' AND table_name LIKE '%MAIL%'


GRANTEE             OWNER      TABLE_NAME        GRANTOR    PRIVILEGE  GRANTABLE HIERARCHY
------------------- ---------- ----------------- ---------- ---------- --------- --
PUBLIC              SYS        UTL_MAIL_INTERNAL SYS        EXECUTE    NO        NO
VIPCREATOR          SYS        UTL_MAIL          SYS        EXECUTE    YES       NO
GBR_VIRGI_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
NGA_MTNNG_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
RWA_TIGOR_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
BGR_MOBIL_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
GNB_MTNGN_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
GHA_TIGOG_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
BFA_TELEC_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
BWA_BTCQQ_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
COG_MTNCO_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
COD_TIGOC_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
GNQ_HITSE_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
COG_AIRTE_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
LBR_LONES_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
MWI_AIRTE_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
KOR_LGTEL_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
BEN_MOOVB_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
BEN_MTNBE_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO
COL_ETBCO_XXXXX     SYS        UTL_MAIL          VIPCREATOR EXECUTE    NO        NO

Generate SQL command to grant this permission to all user who do not have it

SELECT 'GRANT EXECUTE ON UTL_MAIL TO '|| username||';' FROM (
SELECT username as username FROM all_users
MINUS
SELECT grantee as username FROM DBA_TAB_PRIVS 
 WHERE owner = 'SYS' AND table_name LIKE '%MAIL%'
)


GRANT EXECUTE ON UTL_MAIL TO AUT_THREE_XXXXX;
GRANT EXECUTE ON UTL_MAIL TO AUT_THREE_XXXXX;
GRANT EXECUTE ON UTL_MAIL TO AUT_THREE_XXXXX;
GRANT EXECUTE ON UTL_MAIL TO AUT_THREE_XXXXX;
GRANT EXECUTE ON UTL_MAIL TO BDI_ECONE_XXXXX;
GRANT EXECUTE ON UTL_MAIL TO BDI_ECONE_XXXXX;

No comments:

Post a Comment