Sending email with UTL_MAIL fails(ORA-24247) when call is made from a procedure.

 

Sending email with UTL_MAIL fails(ORA-24247) when call is made from a procedure.


24247. 00000 -  "network access denied by access control list (ACL)"



SHOW parameter smtp

NAME            TYPE   VALUE                                       

--------------- ------ --------------------------------------------

smtp_out_server string ficticious.com

 

sending emails with UTL_MAIL.send_attach_varchar2 is working perfectly fine, but wrapping UTL_MAIL.send_attach_varchar2 in a procedure compiles successfully but executing the procedure ends up in ORA-24247 error.

Wrapper procedure for UTL_MAIL.send_attach_varchar2

create or replace procedure       snd_email_atch

is

begin

  UTL_MAIL.send_attach_varchar2 (

    sender       => 'sendemail@ficticious.com',

    recipients   => 'sendemail@ficticious.com',

    cc           => 'scandata@ficticious.com',

    bcc          => 'sendemail@ficticious.com',

    subject      => 'email from procedure',

    message      => 'email from a procedure works',

    attachment   => 'attachment content goes here.',

    att_filename => 'test_attachment.txt'   

  );

END;

 

exec send_email_attach

 

error starting at line : 3 in command -

BEGIN snd_email_atch; END;

Error report -

ORA-24247: network access denied by access control list (ACL)

ORA-06512: at "SYS.UTL_MAIL", line 662

ORA-06512: at "SYS.UTL_MAIL", line 713

ORA-06512: at "TEST. SND_EMAIL_ATCH ", line 4

ORA-06512: at line 1

24247. 00000 -  "network access denied by access control list (ACL)"

*Cause:    No access control list (ACL) has been assigned to the target

           host or the privilege necessary to access the target host has not

           been granted to the user in the access control list.

*Action:   Ensure that an access control list (ACL) has been assigned to

           the target host and the privilege necessary to access the target

           host has been granted to the user.

 

 

Check existing host_aces

 

select host,principal,privilege,grant_type from dba_host_aces;  

 

HOST     PRINCIPAL           PRIVILEGE           GRANT_TYPE

*             GSMADMIN_INTERNAL  RESOLVE              GRANT

 

Granting the Access Control List privilege directly to the user instead of granting through the role will solve the issue

 

BEGIN

DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE

(

  HOST       => 'ficticious-com’,

  LOWER_PORT => NULL,

  UPPER_PORT => NULL,

  ACE        => XS$ACE_TYPE(

                             PRIVILEGE_LIST => xs$name_list('smtp'),

                             PRINCIPAL_NAME => 'TEST',

                             PRINCIPAL_TYPE => xs_acl.ptype_db

                           )

);

EXCEPTION WHEN OTHERS THEN

  DBMS_OUTPUT.PUT_LINE('Error while granting ACL :'|| SQLERRM);

END;

/

 after adding new ACE

select host,principal,privilege,grant_type from dba_host_aces;  

 

HOST                         PRINCIPAL                           PRIVILEGE           GRANT_TYPE

*                                 GSMADMIN_INTERNAL  RESOLVE                  GRANT

ficticious-com          TEST                                      SMTP                         GRANT

 

Now the wrapper procedure should execute successfully sending email

exec snd_email_atch

 

Reference:

ORA-24247 Calling UTL_SMTP or UTL_HTTP or UTL_TCP in a Stored Procedure when ACL Assigned to a Role (Doc ID 754909.1)

ACL creation fails with ORA-24245: invalid network privilege and ORA-06512: at "SYS.DBMS_NETWORK_ACL_ADMIN" (Doc ID 2631322.1)


Note: this is how i have resoled the issue, Always welcome to comment better solution or corrections.



Comments

Post a Comment

Popular posts from this blog

Error processing request - Contact your application administrator apex 20.1

Image
After upgrading 11.2.0.4 to 19c with apex 20.1 accessing apex_admin/apex pages was throwing “Error processing request” Contact your application administrator. There were three invalid objects for APEX_200100 WWV_FLOW_SESSION_RAS WWV_FLOW_WIZARD_API WWV_FLOW_DATA_LOADER Tried compiling these objects but compile was completing with PL/SQL: ORA-00942: table or view does not exist To resolve the issue following rant needs to be given then compile (ran both grant and compile as sysdba) grant select on SYS.DBA_XS_DYNAMIC_ROLES to APEX_200100; grant select on SYS.DBA_TAB_IDENTITY_COLS to APEX_200100; alter package APEX_200100.WWV_FLOW_SESSION_RAS compile body; alter package APEX_200100.WWV_FLOW_WIZARD_API compile body; alter package APEX_200100.WWV_FLOW_DATA_LOADER compile body; Returned 0 invalid object for APEX_200100  select * from dba_objects where status = 'INVALID' and owner = 'APEX_200100' ; Now both admin and apex pages ar
Read more

APP-FND-01436: List of Values cannot find any values for you to choose error from concurrent request program parameter (R12 12.1.3)

Image
APP-FND-01436: List of Values cannot find any values for you to choose error from concurrent request program parameter After upgrading oracle application 11i(11.5.10.2) to R12(12.1.3) running concurrent program request caused this issue When concurrent program is run for a concurrent request after upgrading to R12 12.1.3 the lov, which is attached to a parameter, gives APP-FND-01436: “List of Values cannot find any values for you to choose”. When the same request is run from oracle applications 11i 11.5.10.2 the same value set is working fine. The value set is based on a table. If you go and test the value set query from the value set definition screen it returns complete successful message yet if you go to the parameter of the concurrent request program form where the particular value set is used it gives you the above error. After searching google and oracle support I found metalink note 1400963.1, EBS R12.1: Parameter for Concurrent Request Using Table Based LOV G
Read more

WEB ADI - issues encountered during initial access

Image
On accessing create document link under Desktop Integrator responsibility browser was showing following error Set BNE UIX Physical Directory profile value to proper directory at site level Location = $COMMON_TOP/html/cabo/  (11i) Location = $COMMON_TOP/webapps/oacore/html/cabo/  (R12) As this was a cloned instance the value of this profile setting was pointing to a wrong location. set as BNE UIX Physical Directory /u03/oracle/prodcomn/html/cabo/ Access to functions are controlled by function security so to access and work with web adi the user should have proper access. You should have access to particular integrator to access and work with it Find out responsibility which is created to work with web adi check the main menu for this responsibility. Modify the responsibility or Menu if default is not fitting to your needs. For instance Desktop Integration Menu has create document, Define Lay out, Define Mapping, Manage Documents, Setup options Note : you must add
Read more