Skip to Main Content
  • Questions
  • How to send a sql query result as an attachment in Oracle apex_mail.send procedure.

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Vinay.

Asked: April 28, 2021 - 5:59 pm UTC

Last updated: April 30, 2021 - 11:59 am UTC

Version: 20.1.0.00.13

Viewed 100+ times

You Asked


I have a Oracle sql query which needs to be run and need to send the data returned by the query as an attachment to a mail. Could you please guide how can i do it using apex_mail.send procedure. I am calling apex_mail from database. I have already configured apex mail. I can call the apex_mail.send to send the mail. But i am not sure how can i attach the result returned by my oracle sql query in apex_mail.add attachment.


and we said...

Save the output of the query into a clob variable, then add this as the attachment

e.g. something along the lines of:

begin
  select ...
  bulk collect into attach_recs
  from ...
  
  for 1 in 1 .. attach_recs.count loop
    attachment := attachment || attach_recs ( i );
  end loop;

  mail_id := apex_mail.send ...
  apex_mail.add_attachment ( mail_id, attachment, 'filename' );
end;

More to Explore

APEX

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