Skip to Main Content
  • Questions

  • Generate excel file from oracle auery and send to email on monthly basics

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Nagarajan.

Asked: December 11, 2014 - 11:29 am UTC

Last updated: March 22, 2017 - 6:39 am UTC

Version: 10.6.3

Viewed 10K+ times! This question is

You Asked

Hi ,

1. I need to genereate excel file from oracle query(like...spool option etc),then i need to attach this file and send a mail to user for monthly basics through oracle procedure using oracle scheduling jobs.
can u any one know the proceduere and coding .kindly share with me.

Thanks:
nagarajan

and Tom said...

easiest approach is to generate CSV/XML/HTML and then send as an attachment.

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_mail.htm#ARPLS71202


simply run a query and concatenate into a varchar2(32765), and send the lob as an attachment. Building a csv string from a query is simple:

for x in (selecct c1||','||c2||...||cn from t where...)
loop
   l_string := l_string || chr(13) || l_string;
end loop;

utl_mail......


Alternatively, a better approach (in my opinion) would be to email a LINK to a program that would return a CSV file using application express (apex). That way, the email is very very very small - and the returned output can be as large as you like.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

APEX Options

Mark Wooldridge, December 16, 2014 - 5:47 pm UTC

APEX Interactive Reports provided the ability to download to a csv.

If the file needs to be attached to an email, APEX also provides a subscription capability.

The only coding required would be the query for the report itself. This could even be a view and tested using sql*plus, the interactive report would simply be:

select *
from your_view

Email CSV

A reader, December 18, 2014 - 12:28 am UTC


Interactive reports Email subscription

Irfan Ahmed, February 02, 2016 - 6:38 pm UTC

Hello,
We are using Oracle Apex 4.2.4.00.08.
I am trying to set up Interactive reports Email subscription feature, so that users can subscribe to Email attachments and receive the results on a Bi-weekly basis.
However I am unable to configure the Emails subscription features by logging into the Administration panel.When I run a report as interactive, ICan see the data getting displayed in the browser and can save it in any format I like but here also if I manually use the Interactive reports feature on an adhoc basis to email me once only , I am unable to receive the emails.
Is there something I need to set up for me to be able to use this feature ?

Please advise

Thanks
IQ
Chris Saxon
February 03, 2016 - 12:14 am UTC

Have you performed the setup correctly, as documented in

https://docs.oracle.com/database/121/AEEUG/int_rpt.htm#AEEUG490

https://docs.oracle.com/database/121/AEADM/aadm_mg_service.htm#CHDGDBAD


Subscriptions

Mpho Tsanwani, March 15, 2017 - 3:27 pm UTC

Subscriptions is only sending email attachment file of the report as .html , how do i change it to pdf, csv or other format ?
Connor McDonald
March 22, 2017 - 6:39 am UTC

I asked one of the product managers.

Subscriptions currently are html only.

vinay, April 28, 2021 - 6:04 pm UTC

How can i send CSV file as attachment in apex_mail.add attachement.?
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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library