Scheduled Oracle report emailed as a CSV attachment

Turn a daily Oracle Database query into an automated email report without writing glue code or babysitting a cron job. This blueprint runs an aggregate SQL query against Oracle on a schedule, anchors the reporting window to UTC so the numbers are correct regardless of the database server clock, converts the result to CSV, and emails it to your stakeholders as an attachment. It skips the send entirely when a day has no data and posts a Slack alert if anything fails, so a missed morning report is never silent. It also stays fully backfillable: re-run any past day on demand without editing the flow.

How it works

1. A io.kestra.plugin.core.trigger.Schedule trigger fires every morning at 06:00 UTC (cron: 0 6 * * *).
2. The daily_sales_report task (io.kestra.plugin.jdbc.oracle.Query) aggregates the report day's orders by region and stores the full result set in internal storage (fetchType: STORE). The day is passed as a bound :day parameter and the window is built with FROM_TZ(... 'UTC'), not the server clock.
3. The email_when_data task (io.kestra.plugin.core.flow.If) checks outputs.daily_sales_report.size > 0.
4. When there is data, report_to_csv (io.kestra.plugin.serdes.csv.IonToCsv) converts the stored ION result to CSV and email_report (io.kestra.plugin.email.MailSend) emails it over SMTP with TLS as an attachment.
5. When there is no data, log_no_data (io.kestra.plugin.core.log.Log) records that the day was empty instead of sending an empty attachment.
6. If the run fails, an errors block sends a io.kestra.plugin.slack.notifications.SlackIncomingWebhook alert with the execution id.

The report day is derived from inputs.report_date (else the trigger or execution date minus one day) and rendered once into vars.report_day, which drives the SQL window, the email subject, and the log line in lockstep.

What you get

• A daily, UTC-correct sales report delivered straight to inboxes as a CSV attachment.
• No empty-attachment noise: the email is sent only when the day produced rows.
• Backfill any past day by running the flow with report_date set, no code changes.
• Built-in resilience: retries on the query and the email send, plus a Slack failure alert.

Who it's for

• Data engineers who need scheduled operational reporting off an Oracle warehouse.
• Analysts and ops teams who want a daily metrics email without a custom script.
• Platform teams replacing brittle shell-plus-cron report jobs with declarative pipelines.

Why orchestrate this with Kestra

Oracle's own scheduler (DBMS_SCHEDULER) can run SQL on a timer, but it cannot convert results to CSV, send authenticated SMTP email with attachments, branch on whether the result was empty, or alert Slack when the job fails. Kestra ties all of that together in one declarative YAML flow: an event or time trigger starts the run, retry blocks ride out transient Oracle and SMTP blips, the errors block guarantees failures surface, and every execution is captured with full logs and lineage you can replay. Because the date is parameterized, the same flow that runs nightly also backfills history.

Prerequisites

• A reachable Oracle Database instance. The JDBC URL uses the thin driver service form: jdbc:oracle:thin:@//host:1521/service.
• An orders table (with created_at stored in or comparable against UTC) and a customers table to join and aggregate.
• An SMTP server reachable from the Kestra workers (this flow uses SMTP over TLS on port 587).

Secrets

Set these as Kestra secrets, never inline:

• ORACLE_USERNAME: the Oracle database user that runs the report query.
• ORACLE_PASSWORD: the password for that Oracle database user.
• SMTP_HOST: the hostname of the SMTP server used to send the report.
• SMTP_USERNAME: the username used to authenticate to the SMTP server.
• SMTP_PASSWORD: the password used to authenticate to the SMTP server.
• REPORT_RECIPIENTS: recipient email address(es) for the report.
• SLACK_WEBHOOK: the Slack Incoming Webhook URL the failure alert is posted to.

Quick start

1. Create the ORACLE_USERNAME, ORACLE_PASSWORD, SMTP_HOST, SMTP_USERNAME, SMTP_PASSWORD, REPORT_RECIPIENTS, and SLACK_WEBHOOK secrets.
2. Update the oracle_url variable and, if needed, the from_address input default to match your environment.
3. Adjust the report SQL or the cron expression as needed; confirm created_at is stored in or comparable against UTC.
4. Enable the flow. It runs on the schedule; to backfill a day, run it manually with report_date set.

How to extend

• Swap the SQL for any other Oracle aggregate, or template the table and grouping columns through inputs.
• Change the cadence by editing the cron, or add a second schedule for a weekly rollup.
• For large result sets that a mail relay would reject, write the CSV to object storage and email a download link instead of an attachment.
• Add more notification channels (Microsoft Teams, PagerDuty) alongside the Slack alert.
• Branch the report by region and send each segment to a different distribution list.

Links

• Oracle JDBC plugin: https://kestra.io/plugins/plugin-jdbc-oracle
• IonToCsv task: https://kestra.io/plugins/plugin-serdes/csv/io.kestra.plugin.serdes.csv.iontocsv
• MailSend task: https://kestra.io/plugins/plugin-notifications/io.kestra.plugin.email.mailsend