3 Replies Latest reply: Dec 14, 2012 9:20 AM by Aman.... RSS

    UTL_SMTP Mail

    846231
      Hi all,

      11.2.0.1

      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;
       14
       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
      
      SQL>
      Please help me correct this error.


      Thanks a lot,
      Kinz