How to send email from oracle query

Doing your data SELECT is the easy part.

Sending email, that has a chance of being deliverable, that's the big hurdle.

Unless your are an Email Deliverability Guru (you do this all day, every day), likely best to relay your mail through https://MailGun.com to ensure high deliverability email.

How exactly you plumb MailGun into your email system, depends on many factors.

Likely best you hire someone to setup all this for you.

Tip: For low volume mail you can use SWAKS in a script to relay through MailGun, to have high deliverability email up + running in a few minutes.

easiest method is construct a csv clob (not an actual excel file, but still easily read by excel)

then send the clob as an attachment

https://www.experts-exchange.com/articles/7749/How-to-Send-Email-Attachments-with-Oracle.html

The comment sdstuber made actually suggests you slice your project into chunks of work.

1) Data SELECT.

2) Message construction - what sdstuber covers.

3) Message send + for any type of deliverability, you'll use a relay service like MailGun.

The "chunks" are sort of implicit, you can't send if you don't have the data, and you can't send if you don't have an smtp service ("relay" or otherwise)

In terms of the database query/procedure it would likely be implemented as a single oracle stored procedure that queries the data and sends it.
The 3rd code block in the article shows pretty much everything that should be needed to do the extract, format, and send.

I'm generally opposed to forwarding mail through somebody else's mail server (that's nothing against MailGun).

There are quite a few email clients that have a command line interface.  I've used bmail for years, but there many more.

With it, you'll need an email server that will accept and forward SMTP mail.  

Then when the process is ready to send the email it can be done directly from PL/SQL or in command line (batch) script.

@sdstuber: Let me try your solution sir

Sending mail through a third party relay would also likely to be against auditing and security policies of most companies.  Companies tend to have their own mail servers that would handle sending the message as well as the data security guidelines.

These solutions and suggestions are all well and good, but I'd urge caution on this front. Typically, using an SMTP service on a DB box should NOT be considered as "enterprise level" solution.
Email can be useful, but dangerous for mission critical systems as it can be unreliable (and many places for it to go wrong).
Better to integrate with a monitoring application like BMC Patrol and let the Comms be distributed from there (as it integrates with enterprise mail system).

All the best!
JT

I think there is a misunderstanding here that somehow some local mail service on the database server is being used in the linked article.
While it is "possible" to do such a thing, it's neither recommended nor even implied.

When you use the utl_smtp package as shown, you establish a connection to the desired smtp service, which "could" be local but will more likely be your enterprise mail system, whatever that system may be.

Just to elaborate, it's commonplace today that SMTP relay is enabled on a server only under specific conditions.  (The days of "trust everyone" and mail servers installed with SMTP Relay enabled by default are long past.)  In this case, the relay would be accepted from 1 or more specific IP addresses or, depending on the network topography, a local subnet.  There's not much risk if done correctly.