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 1000+ times
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
Is this answer out of date? If it is, please let us know via a Comment