Skip to Main Content
  • Questions

  • Apex 21.2 Automations - Send Email with Attachment

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Connor McDonald

Thanks for the question, Donna.

Asked: May 11, 2023 - 4:56 pm UTC

Last updated: May 16, 2023 - 3:33 am UTC

Version: 21.2

Viewed 10K+ times! This question is

You Asked

Hello

I am trying to use Automations to set up a weekly e-mail with a csv attachment that is a simple select from a table.

I am getting“ORA-20987….. has a wrong data type in SQL statement. Column# 1 is VARCHAR2 but BLOB expected” error. It seems that I need to modify my query to convert to BLOB.

Can you provide an example of how this should look?

Under Automations, in "Actions", I select Type = "Send E-mail".

Under "Attachement SQL" I entered

select column1, column2 from data_table



and Connor said...

As per the help text

Enter a SQL statement which returns one or more BLOBs that are attached as files to the e-mail.

If the content_id column contains a null value then the associated files will be regular attachments. Files such as documents or zip archives are generally attached in this way.

If the value of the content_id is non-null then the associated file will be marked as being attached inline. The value of the content_id column will be used as the CID (Content ID) of the attachment. This CID value can be used in the HTML code of the email in order to embed images directly within the email content. Note that some email clients may not automatically display inline attachments. Also note that email standards dictate various requirements for the format of content identifiers. Oracle Oracle APEX does not enforce compliance with these standards.

The SQL statement must be in the following format:

select blob_column,
file_name,
mime_type,
content_id
from table

If you don't readily have the data in a blob already, what you can do is use the "Initialization Procedure Name" to run a procedure at the commencement of the automation to

- fetch the dat
- build a csv blob
- store it in a table

which you would then reference in your attachment SQL

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.