Send email from Invantive UniversalSQL using SMTP

[forums]

Using Invantive UniversalSQL you can send email using the following steps:

• Configure SMTP settings.
• Send mail using an insert on smtp@mail.

The configuration of the SMTP settings can be done using separate SQL set statements or using a PSQL block such as below. The full list of available settings is documented online:

begin
  set smtp-host-address@mail "smtp.office365.com";
  set smtp-minimum-deliver-duration-ms@mail 5000;
  set smtp-user-name@mail "john.doe@acme.com";
  set smtp-password@mail "verysecret";
  set [mail-from-name]@mail "ACME System";
  set [mail-from-email]@mail "mailsender@acme.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;
end;

The actual sending of a simple email is done using:

    insert into smtp@mail
    ( toEmail
    , ccEmail
    , bccEmail
    , subject
    , body
    --, attachmentContents
    --, attachmentName
    --, attachmentMimeType
    )
    select 'johnsmother@acme.com'
           toEmail
    ,      cast(null as varchar2)
           ccEmail
    ,      cast(null as varchar2)
           bccEmail
    ,      'Hi hi'
           subject
    ,      'hi,'
           || chr(13) || 'How are you?'
           body

By postfixing the previous SQL with the following you can also include one attachment:

... original code
,      rfe.file_contents
       attachmentContents
,      basename(fle.file_path)
       attachmentName
,      cast(null as varchar2)
       attachmentMimeType
from   files('c:\temp', 'spreadsheet.xlsx', false)@os fle
join   read_file(fle.file_path)@os rfe