mkdir /home/oracle/wallet/Office365
orapki wallet create -wallet /home/oracle/wallet/Office365 -pwd xx -auto_login
orapki wallet add -wallet /home/oracle/wallet/Office365 -trusted_cert -cert m365_root_certs_20220331.p7b
orapki wallet display -wallet /home/oracle/wallet/Office365 -pwd xx
BEGIN
-- Allow SMTP access for user APPS
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'smtp.office365.com',
lower_port => 587,
upper_port => 587,
ace => xs$ace_type(privilege_list => xs$name_list('SMTP'),
principal_name => 'APPS',
principal_type => xs_acl.ptype_db));
END;
/
DECLARE
crlf varchar2(2) := UTL_TCP.crlf;
connection UTL_SMTP.connection;
smtpServer varchar2(100) := 'smtp.office365.com';
emailSubject varchar2(100) := 'Test Generic Email Subject';
-- Set sender email
smtpUsername varchar2(100) := 'wfmoitest@tahaluf.ae';
-- Set sender email password
smtpPassword varchar2(100) := 'xx';
-- Set destination email
emailDestination varchar2(100) := '';
-- Set Oracle Wallet path
walletPath varchar(100) := 'file:/home/oracle/wallet/Office365';
-- set Oracle Wallet password
walletPassword varchar2(100) := 'xx';
BEGIN
connection := UTL_SMTP.open_connection(
host => smtpServer,
port => 587,
wallet_path => walletPath,
wallet_password => walletPassword,
secure_connection_before_smtp => FALSE);
UTL_SMTP.ehlo(connection, smtpServer);
UTL_SMTP.starttls(connection);
UTL_SMTP.ehlo(connection, smtpServer);
UTL_SMTP.auth(
c => connection,
username => smtpUsername,
password => smtpPassword,
schemes => utl_smtp.all_schemes);
UTL_SMTP.mail(connection, smtpUsername);
UTL_SMTP.rcpt(connection, emailDestination);
UTL_SMTP.open_data(connection);
UTL_SMTP.write_data(connection, 'Date: ' || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || crlf);
UTL_SMTP.write_data(connection, 'From: ' || smtpUsername || crlf);
UTL_SMTP.write_data(connection, 'Subject: ' || emailSubject || crlf);
UTL_SMTP.write_data(connection, 'To: ' || emailDestination || crlf);
UTL_SMTP.write_data(connection, '' || crlf);
FOR i IN 1 .. 10
LOOP
UTL_SMTP.write_data(connection, 'This is only a test line. ' || TO_CHAR (i) || crlf);
END LOOP;
UTL_SMTP.close_data(connection);
UTL_SMTP.quit(connection);
END;
/
