CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
redsoxlost
redsoxlost

Reputation: 1235

Configuring UTL_MAIL Package Prerequisites

I was trying to configure my system to send mail using UTL_MAIL package but no luck .. I read that I need to configure ACL first. hence I did. But even doing so it's not working still.

SELECT * FROM dba_network_acls;

begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_http.xml',
    description => 'HTTP Access',
    principal   => 'HR',
    is_grant    => TRUE,
    privilege   => 'connect',
    start_date  => null,
    end_date    => null
  );
  dbms_network_acl_admin.add_privilege (
    acl        => 'utl_http.xml',
    principal  => 'HR',
    is_grant   => TRUE,
    privilege  => 'resolve',
    start_date => null,
    end_date   => null
  );
  dbms_network_acl_admin.assign_acl (
    acl        => 'utl_http.xml',
    host       => 'smtp.gmail.com',
    lower_port => 25,  --- Gmail SMTP server.
    upper_port => 25
  );
  commit;
end;
/

I am getting error

ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first. ei4sm3659171pbb.42 - gsmtp ORA-06512: at "SYS.UTL_MAIL", line 654 ORA-06512: at "SYS.UTL_MAIL", line 671 ORA-06512: at line 2 29279. 00000 - "SMTP permanent error: %s" *Cause: A SMTP permanent error occurred. *Action: Correct the error and retry the SMTP operation.

I have already executed the following commands

CONN sys/password AS SYSDBA

@$ORACLE_HOME/rdbms/admin/utlmail.sql

@$ORACLE_HOME/rdbms/admin/prvtmail.plb

Please help !

Upvotes: 0

Views: 6736

Answers (1)

Justin Cave
Justin Cave

Reputation: 231831

Your SMTP server appears to require an encrypted connection. UTL_MAIL is designed to simplify the API for sending mail for basic SMTP servers. It doesn't support the full set of options defined by the SMTP protocol. I don't believe it supports TLS.

Assuming that you're using 11.2, you'll need to use the UTL_SMTP package and you'll need to call the STARTTLS function while you're setting up the connection.

Upvotes: 3

Related Questions