How to remove delivery notifications from smtp@mail with 22.0.330

Questions
, , ,
1

I recently upgraded Invantive Query Tool and Data Hub from an unknown previous version to 22.0.330.

I’m using the smtp@mail in a lot of automated scripts.

Since this upgrade, each time my script sends an email, I get mail delivery acknowledgement sent to the from field of my statement.

Capture d’écran 2022-08-22 à 16.51.20
Capture d’écran 2022-08-22 à 16.51.202504×642 70.8 KB

I use for example this kind of statement Tables replication automation with stored procedure giving statistics & error handling by email in the object description, I cannot find a field or option to deactivate this delivery notification.

Capture d’écran 2022-08-22 à 16.53.59
Capture d’écran 2022-08-22 à 16.53.592342×1596 346 KB

3

How can we remove delivery notification?

Can’t find this.

4

It is not a known problem.

On release 24.0 this behaviour can be controlled using the column DispositionNotificationToEmailAddress in smtp@mail. The text value of this column is used as email address in the Disposition-Notification-To SMTP-header.

No Disposition-Notification-To SMTP-header is set when no value is specified for this column.

For more information on the Disposition-Notification-To header please refer to RFC 3798 - Message Disposition Notification.

Please add a reply with a short sample code that reproduces the issue in case no value is specified for the column but a notification is delivered nonetheless on release 24.0.

5

This is a sample code, that reproduce the delivery notification without having DispositionNotificationToEmailAddress in smtp@mail set.

Query Tool 24.0.157 PROD+4749

create or replace procedure sendmail
( email_subject text
, email_body text
, email_recipients text
)
as
declare
  l_error_txt   varchar2;
  l_error_cnt   varchar2;
  l_email_table varchar2;
begin
  l_error_txt := '';
  l_error_cnt := 0;
  set smtp-host-address@mail "smtp.office365.com";
  set smtp-minimum-deliver-duration-ms@mail 5000;
  set smtp-user-name@mail "my_email@domain.com";
  set smtp-password@mail "my_pwd";
  set [mail-from-name]@mail "my_email";
  set [mail-from-email]@mail "my_email@domain.com";
  set [mail-body-html]@mail false;
  set smtp-enable-ssl@mail true;
  set mail-priority@mail -1;
  set smtp-minimum-deliver-duration-ms@mail 1000;
  set mail-body-html@mail true;
  set mail-priority@mail 0;
  begin
    -- sending email
    insert into smtp@mail
    ( toEmail
    , ccEmail
    , bccEmail
    , subject
    , body
    --, attachmentContents -- starting PROD 20.2.112 use Attachment1Contents instead
    --, attachmentName
    --, attachmentMimeType
    )
    select email_recipients
           toEmail
    ,      cast(null as varchar2)
           ccEmail
    ,      cast(null as varchar2)
           bccEmail
    ,      email_subject
           subject
    ,      email_body
           body
    --,      null -- rfe.file_contents
    --       attachmentContents -- starting PROD 20.2.112 use Attachment1Contents instead
    --,      null -- basename('{USER_MULTI_PARTIES_FILE_NAME}')
    --       attachmentName
    --,      null -- cast(null as varchar2)
    --       attachmentMimeType
    ;
  end;
end;

declare
  l_emailrecipients varchar2 := 'to_me@domain.com';
begin
  sendmail
  ( -- email subject here
    'this is a dummy subject.'
     -- email body here
  , 'this is a dummy body.' || '<br>'
      -- recipients here
  , l_emailrecipients
  );
end;
6

We are unable to reproduce the problem. The logic was tested on 24.0.193 as shown below.

It is recommended to get assistance from a network consultant for this issue.

With DispositionNotificationToEmailAddress

begin
  set smtp-host-address@mail "server.com";
  set smtp-user-name@mail "key";
  set smtp-password@mail "secret";
  set [mail-from-name]@mail "John Doe";
  set [mail-from-email]@mail 'john.doe@acme.com';
  set smtp-enable-ssl@mail true;
  set smtp-minimum-deliver-duration-ms@mail 1000;
  set mail-body-html@mail true;
  set mail-priority@mail 0;
  begin
    insert into smtp@mail
    ( toEmail
    , ccEmail
    , bccEmail
    , subject
    , body
    , DispositionNotificationToEmailAddress
    )
    select email_recipients
    ,      null
    ,      null
    ,      email_subject
    ,      email_body
    ,      'john.doe@acme.com'
    ;
  end;
end;

begin
  sendmail
  ( 'this is a dummy subject ' || to_char(sysdateutc, 'DD-MM-YYYY HH24:MI:SS')
  , 'this is a dummy body.' || '<br>'
  , 'john.doe@acme.com'
  );
end;

results in an email with the expected header:

Received: from DU4P189MB2744.EURP189.PROD.OUTLOOK.COM (2603:10a6:10:56b::12)
 by AS8P189MB1512.EURP189.PROD.OUTLOOK.COM with HTTPS; Wed, 22 May 2024
 14:02:43 +0000
Received: from AM6P192CA0028.EURP192.PROD.OUTLOOK.COM (2603:10a6:209:83::41)
 by DU4P189MB2744.EURP189.PROD.OUTLOOK.COM (2603:10a6:10:56b::12) with
 Microsoft SMTP Server (version=TLS1_2,
 cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7611.18; Wed, 22 May
 2024 14:02:43 +0000
Received: from AMS0EPF000001B1.eurprd05.prod.outlook.com
 (2603:10a6:209:83:cafe::8e) by AM6P192CA0028.outlook.office365.com
 (2603:10a6:209:83::41) with Microsoft SMTP Server (version=TLS1_2,
 cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7587.36 via Frontend
 Transport; Wed, 22 May 2024 14:02:42 +0000
Authentication-Results: spf=pass ...
Received-SPF: Pass ...
Received: from ...
X-IncomingTopHeaderMarker:
 OriginalChecksum:FB189EF19E40CEFE8AE77CEC0891BB5415F2C09AF53B40009F48F4E620333F79;UpperCasedChecksum:1B7165EB3DB7C719536778854766EE906A5FE686993D310BE76C1DB0431E4C7A;SizeAsReceived:1694;Count:14
DKIM-Signature: ...
DKIM-Signature: ...
Disposition-Notification-To: john.doe@acme.com
User-Agent: Invantive Mail Provider
STANDARD HEADERS SUCH AS From.

Without DispositionNotificationToEmailAddress

begin
  set smtp-host-address@mail "server.com";
  set smtp-user-name@mail "key";
  set smtp-password@mail "secret";
  set [mail-from-name]@mail "John Doe";
  set [mail-from-email]@mail 'john.doe@acme.com';
  set smtp-enable-ssl@mail true;
  set smtp-minimum-deliver-duration-ms@mail 1000;
  set mail-body-html@mail true;
  set mail-priority@mail 0;
  begin
    insert into smtp@mail
    ( toEmail
    , ccEmail
    , bccEmail
    , subject
    , body
    )
    select email_recipients
    ,      null
    ,      null
    ,      email_subject
    ,      email_body
    ;
  end;
end;

begin
  sendmail
  ( 'this is a dummy subject ' || to_char(sysdateutc, 'DD-MM-YYYY HH24:MI:SS')
  , 'this is a dummy body.' || '<br>'
  , 'john.doe@acme.com'
  );
end;

results in an email without header as expected:

Received: from FRZP189MB2825.EURP189.PROD.OUTLOOK.COM (2603:10a6:d10:138::12)
 by AS8P189MB1512.EURP189.PROD.OUTLOOK.COM with HTTPS; Wed, 22 May 2024
 14:03:58 +0000
Received: from PH7PR17CA0054.namprd17.prod.outlook.com (2603:10b6:510:325::18)
 by FRZP189MB2825.EURP189.PROD.OUTLOOK.COM (2603:10a6:d10:138::12) with
 Microsoft SMTP Server (version=TLS1_2,
 cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7544.55; Wed, 22 May
 2024 14:03:57 +0000
Received: from SA2PEPF000015CB.namprd03.prod.outlook.com
 (2603:10b6:510:325:cafe::91) by PH7PR17CA0054.outlook.office365.com
 (2603:10b6:510:325::18) with Microsoft SMTP Server (version=TLS1_2,
 cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7611.21 via Frontend
 Transport; Wed, 22 May 2024 14:03:56 +0000
Authentication-Results: spf=pass ...
Received-SPF: Pass ...
Received: from ...
X-IncomingTopHeaderMarker:
 OriginalChecksum:0A6184562A855987FEF58CA32F70FC41E89B151A2F9D6108B0B3220E74D1AB77;UpperCasedChecksum:D60643278B73E0FF7A1FFA0D15B42925916F0AAA0140694E496116B77A1FD93E;SizeAsReceived:1635;Count:13
DKIM-Signature: ...
DKIM-Signature: ...
User-Agent: Invantive Mail Provider
STANDARD HEADERS SUCH AS From.