Forum Stats

  • 3,734,448 Users
  • 2,246,973 Discussions
  • 7,857,295 Comments

Discussions

ORA-24247

Hi all,

When I run the script below, it runs successfully:

DECLARE

 v_From   VARCHAR2(80) := '[email protected]';

 v_Recipient VARCHAR2(80) := '[email protected];[email protected]';

 v_Subject  VARCHAR2(80) := 'test subject';

 v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';

 v_Mail_Conn utl_smtp.Connection;

 crlf    VARCHAR2(2) := chr(13)||chr(10);

BEGIN

 v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);

 utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);

 utl_smtp.Mail(v_Mail_Conn, v_From);

 utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);

 utl_smtp.Data(v_Mail_Conn,

  'Date: '  || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||

  'From: '  || v_From || crlf ||

  'Subject: '|| v_Subject || crlf ||

  'To: '   || v_Recipient || crlf ||

  crlf ||

  'some message text'|| crlf || -- Message body

  'more message text'|| crlf

 );

 utl_smtp.Quit(v_mail_conn);

EXCEPTION

 WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then

  raise_application_error(-20000, 'Unable to send mail', TRUE);

END;

However, when I convert this to a stored procedure and execute it, I get an ORA-24247. If this is ACL issue, isn't the PL?SQL block should have gotten the error also?

Thanks in advance

Answers

  • EdStevens
    EdStevens Member Posts: 28,143 Gold Crown

    Just as an aside, code is FAR easier t read when you format it as such:


    DECLARE
     v_From   VARCHAR2(80) := '[email protected]';
     v_Recipient VARCHAR2(80) := '[email protected];[email protected]';
     v_Subject  VARCHAR2(80) := 'test subject';
     v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';
     v_Mail_Conn utl_smtp.Connection;
     crlf    VARCHAR2(2) := chr(13)||chr(10);
    BEGIN
     v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
     utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
     utl_smtp.Mail(v_Mail_Conn, v_From);
     utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
     utl_smtp.Data(v_Mail_Conn,
      'Date: '  || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
      'From: '  || v_From || crlf ||
      'Subject: '|| v_Subject || crlf ||
      'To: '   || v_Recipient || crlf ||
      crlf ||
      'some message text'|| crlf || -- Message body
      'more message text'|| crlf
     );
     utl_smtp.Quit(v_mail_conn);
    EXCEPTION
     WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
      raise_application_error(-20000, 'Unable to send mail', TRUE);
    END;
    

    Just select the text to be formatted, click the 'paragraph' icon in the left margin, click the double-quote icon in the pop-up, and select 'code'.

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,018 Silver Trophy

    You left off relevant details as to what user you are using when running the PL/SQL block vs the user the CREATE PROCEDURE was ran how, whether the PROCEDURE uses AUTHID DEFINER or AUTHID CURRENT_USER, and what user you ran the procedure as.

    All things the same, if you are hitting "ORA-24247: network access denied by access control list (ACL)", Oracle will always throw that error when that user is trying to establish an external connection and no privs are granted in the ACE/ACL (depending upon version).

Sign In or Register to comment.