Is there any way to mail the Output of a SELECT query to a pre-defined list of email ids in Oracle?

Question

Everyday at my job, i need to fetch results of these queries and send them one by one to these recipients.This is so much time consuming.

What i want to do,is to automate this process.

I want help in coding a script that will automatically mail the results of these queries to the recipients, at a particular time of the day. Is it possible? If Yes, then How?

Any kind of help will be appreciated.

Are you asking how to send an e-mail from a database? Are you asking for Oracle or MySQL??? After e-mailing you need to learn about scheduling... thinking about it this is massively broad but there's plenty of information in the documentation of which ever RDBMS you're actually using to help. — Ben, May 17 '14 at 14:10
Yes this is very possible but not with plain SQL. Tools to look for: Oracle [PL/SQL](http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/toc.htm), [UTL_SMTP](http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_smtp.htm) and [DBMS_SCHEDULER](http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_sched.htm). In fact I recently implemented a configurable (domain specific) email notifier :) — user272735, May 17 '14 at 15:04

score 0 · Answer 1 · edited May 23 '17 at 12:05

You can follow the following steps to configure sending email (results of query) in Oracle Database:

Create owner_schema.sp_send_email stored procedures. Please Refer following links to get PL/SQL scripts for sending email. You need to configure sender, recipients, email server host and email server listening port. You also make sure that you have added oracle server on the ACL list of email server, verify using user@source_oracle_database server $ telnet email_server port.

http://www.dba-oracle.com/t_utl_smtp_utility.htm

http://www.ryanboyer.net/e-mail-query-results-from-orac/

How to sent email in Oracle PL/SQL package to multiple receivers?
Create a Oracle Scheduler if you need to send email regularly(hourly, daily, weekly, monthly, yearly...) and configure scheduler to execute email sending procedure as per the requirements.

Good advice, I'd only add one point: Since the OP is using 11g, he'll also need to configure Oracle's own ACLs to allow connections to the mail server to avoid the famous ORA-24247 — Frank Schmitt, May 19 '14 at 06:43

Is there any way to mail the Output of a SELECT query to a pre-defined list of email ids in Oracle?

1 Answers1