Skip to Main Content
  • Questions
  • Apex 21.2 Automations - Send Email with Attachment


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 100+ times

You Asked


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,
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

More to Explore


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