3 Replies Latest reply on Dec 14, 2012 3:20 PM by Aman....

    UTL_SMTP Mail

      Hi all,

      I copied this sample Mail stored proc, But I got error :(
      SQL> create or replace
        2  procedure check_lapse_envi
        3  is
        4  CURSOR c1 IS SELECT proj_envi_name,decomission_date FROM mdc_inventory WHERE decomission_date-sysdate<=28
        5  and proj_status='Active';
        6  username VARCHAR2(50);
        7  email VARCHAR2(50) :='jenny@abc.com';
        8  expire_date DATE;
        9  sender VARCHAR2(40) := 'carol@abc.com';
       10  message VARCHAR2(200);
       11  mailhost VARCHAR2(40) := 'internal-mail-router.abc.com';
       12  mailport NUMBER := 25;
       13  mail_conn UTL_SMTP.connection;
       15  BEGIN
       16  message:=', your password will expire on';
       17  OPEN c1;
       18  LOOP
       19  FETCH c1 INTO username,expire_date;
       20  EXIT WHEN c1%NOTFOUND;
       21  mail_conn := UTL_SMTP.open_connection(mailhost, mailport);
       22  UTL_SMTP.helo(mail_conn, mailhost);
       23  UTL_SMTP.mail(mail_conn, sender);
       24  UTL_SMTP.rcpt(mail_conn, email);
       25  UTL_SMTP.data(mail_conn, username||message||' '||expire_date);
       26  UTL_SMTP.quit(mail_conn);
       27  END LOOP;
       28  CLOSE c1;
       29  END;
       30  /
      Procedure created.
      SQL> exec check_lapse_envi;
      BEGIN check_lapse_envi; END;
      ERROR at line 1:
      ORA-24247: network access denied by access control list (ACL)
      ORA-06512: at "SYS.UTL_TCP", line 17
      ORA-06512: at "SYS.UTL_TCP", line 246
      ORA-06512: at "SYS.UTL_SMTP", line 127
      ORA-06512: at "SYS.UTL_SMTP", line 150
      ORA-06512: at "MDC_SC.CHECK_LAPSE_ENVI", line 20
      ORA-06512: at line 1
      Please help me correct this error.

      Thanks a lot,
        • 1. Re: UTL_SMTP Mail
          google it's your friend:

          1 person found this helpful
          • 2. Re: UTL_SMTP Mail
            As I said in your other post on this issue (UTL_SMTP Mail you would be well served to forget utl_smpt and use utl_mail instead. It hides a lot of the ugly details you will stumble over if you use utl_smtp.
            1 person found this helpful
            • 3. Re: UTL_SMTP Mail
              I would just echo what Ed has said. You would be better of using utl_mail rather than using utl_smtp . It's much simpler and all you need is just a simple parameter to be set in the database for that. Try it and you would realize the difference.