How to Automate Reports in Oracle

Galaxy Glossary

How can I automatically run an Oracle query and export the results?

Use DBMS_SCHEDULER to run SQL on a schedule and export results automatically.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why automate reports in Oracle?

Automation frees analysts from manual exports, guarantees timely delivery, and creates audit-ready, reproducible output.

How do I schedule a SQL report?

Create a scheduler job that runs a stored procedure or SQL script, then write the result set to a file or table.

Prerequisites: what privileges are required?

You need CREATE JOB, CREATE PROCEDURE, and WRITE access on an Oracle DIRECTORY object that points to the file system path where the report will be written.

What is the exact syntax?

Use DBMS_SCHEDULER.CREATE_JOB with parameters for job name, schedule, job action, and destination.Optionally, use DBMS_SCHEDULER.ENABLE to activate the job.

How do I export query results to CSV?

Inside the job action, open a UTL_FILE handle to the DIRECTORY, LOOP through the cursor that selects your data, and write each row as comma-separated text.

Example: daily revenue report

The example below creates a procedure export_daily_revenue that selects yesterday’s revenue from Orders and writes it to daily_revenue_YYYYMMDD.csv.A scheduler job then runs the procedure at 02:00 every day.

Best practices for automated Oracle reports

Log errors with DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE, keep procedures idempotent, and store generated files in a version-controlled bucket or table for traceability.

Common mistakes and how to fix them

See the section below for pitfalls such as wrong job_type or missing directory grants.

Need to change the schedule later?

Use DBMS_SCHEDULER.SET_ATTRIBUTE to update repeat_interval or start_date without recreating the job.

Why How to Automate Reports in Oracle is important

How to Automate Reports in Oracle Example Usage


-- Run the procedure once to verify output
BEGIN
  export_daily_revenue;
END;
/

-- Query yesterday's revenue directly
SELECT SUM(total_amount) AS revenue_yesterday
FROM Orders
WHERE order_date BETWEEN TRUNC(SYSDATE)-1 AND TRUNC(SYSDATE);

How to Automate Reports in Oracle Syntax


-- Create a directory for file output
CREATE OR REPLACE DIRECTORY report_dir AS '/u01/reports';
GRANT WRITE ON DIRECTORY report_dir TO reporting_user;

-- Stored procedure that generates the CSV
CREATE OR REPLACE PROCEDURE export_daily_revenue IS
  v_file   UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN('REPORT_DIR',
            'daily_revenue_'||TO_CHAR(SYSDATE-1,'YYYYMMDD')||'.csv',
            'w');

  FOR rec IN (
      SELECT o.id,
             o.total_amount,
             o.order_date,
             c.email
      FROM Orders o
      JOIN Customers c ON c.id = o.customer_id
      WHERE TRUNC(o.order_date) = TRUNC(SYSDATE) - 1)
  LOOP
    UTL_FILE.PUT_LINE(v_file,
      rec.id||','||rec.total_amount||','||TO_CHAR(rec.order_date,'YYYY-MM-DD')||','||rec.email);
  END LOOP;
  UTL_FILE.FCLOSE(v_file);
END;
/

-- Scheduler job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'daily_revenue_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN export_daily_revenue; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
    enabled         => TRUE);
END;
/

Common Mistakes

Using job_type => 'STORED_PROCEDURE' while passing an anonymous block in job_action. Fix: set job_type to 'PLSQL_BLOCK' or supply a named procedure.
Granting CREATE JOB but forgetting WRITE privilege on the DIRECTORY, causing UTL_FILE write errors. Fix: GRANT WRITE ON DIRECTORY dir TO user.