Hi,
I want to send email to specific people using pl/sql if my other programs(procedure or package or function) error. but the problem is Relay access denied when sending email to other domain (Example: test@yahoo.co.id).
In other case:
I have setting configuration email with workflow email(EBS r12.2.4) and it's worked perfectly with same mail server. Can send email to other domains too like yahoo, google, etc. So, i think the problem is not my mail server.
Specification:
Email Server: ZIMBRA
domain: ptpv
My Steps:
1. Assign mail server in file host (server)
2. Create and assign ACL with user APPS
3. Send email with PL/SQL
My example code:
CREATE OR REPLACE PROCEDURE APPS.SEND_MAIL (
msg_from varchar2 := 'oracle.ebs@ptpv.co.id',
msg_to varchar2,
msg_subject varchar2 := 'E-Mail message from your database',
msg_text varchar2 := 'xxxx' )
IS
c utl_tcp.connection;
rc integer;
BEGIN
c := utl_tcp.open_connection('103.28.14.227', 25); -- open the SMTP port 25 on local machine
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'HELO localhost');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'DATA'); -- Start message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
-- rc := utl_tcp.write_line(c, );
rc := utl_tcp.write_line(c, msg_text);
rc := utl_tcp.write_line(c, '.'); -- End of message body
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
rc := utl_tcp.write_line(c, 'QUIT');
dbms_output.put_line(utl_tcp.get_line(c, TRUE));
utl_tcp.close_connection(c); -- Close the connection
END;
/
Execute:
exec send_mail(msg_to =>'test@yahoo.co.id'); -- send email failed. Error Message: Relay access denied
exec send_mail(msg_to =>'oracle.ebs@ptpv.co.id'); -- Success send email
is there any step i miss?
Please help!
Regards,
Thery