How a cup of Nescafé and one procedure transformed our export nightmare in Oracle APEX!

A Real Story: When PDF fails and the client starts grumbling...

It was 11:00 AM. Outside, the temperature hit about 35°C. The office AC? Just a gentle promise:

"I'll send a cool breeze soon… just be patient."

Inside the office, the dev team was preparing the new release of the system, which was due for a demo next week. The client had what seemed like a simple request:

"I want a PDF report of all employees, in Persian font, nicely formatted—and also give me an Excel version on the side. Simple, right?"

And that’s where the chaos began:
The PDF output had messed-up characters, the Excel had broken date formats, and the CSV missed several columns. The team was tired, frustrated, and out of ideas.

That’s when one of the teammates, smiling, said:

“I just had a cup of Nescafé—got an idea. Let’s try APEX_DATA_EXPORT.DOWNLOAD.”

And just like that, the magic began.

---

What is APEX_DATA_EXPORT.DOWNLOAD?

This procedure is part of Oracle APEX’s modern and powerful APEX_DATA_EXPORT package. It is designed to generate and stream export files to the browser—perfect for reports in PDF, Excel, CSV, or JSON formats.

In short, if you want to create a clean, downloadable export file in APEX with full control over the output, this is your best friend.

---

Procedure Syntax and Parameters

APEX_DATA_EXPORT.DOWNLOAD (
   p_data       IN APEX_DATA_EXPORT.t_export,
   p_filename   IN VARCHAR2 DEFAULT NULL,
   p_mime_type  IN VARCHAR2 DEFAULT NULL,
   p_inline     IN BOOLEAN DEFAULT FALSE
);

Parameters:

---

How to generate export data: using get_data

Before calling DOWNLOAD, you need to prepare your export data. The easiest way is via APEX_DATA_EXPORT.get_data, which can:

• Extract data from an Interactive Report (IR) by ID

• Or generate export data from any custom SQL query

---

Basic Example: Export PDF from Page 7

DECLARE
   l_data APEX_DATA_EXPORT.t_export;
BEGIN
   l_data := APEX_DATA_EXPORT.get_data (
      p_application_id => :APP_ID,
      p_page_id        => 7,
      p_report_id      => 'employee_report',
      p_format         => 'PDF'
   );

   APEX_DATA_EXPORT.DOWNLOAD(
      p_data     => l_data,
      p_filename => 'Employees.pdf'
   );
END;

---

Advanced Example 1: Excel Output for Finance Management

DECLARE
   l_data APEX_DATA_EXPORT.t_export;
BEGIN
   l_data := APEX_DATA_EXPORT.get_data (
      p_application_id => :APP_ID,
      p_page_id        => 15,
      p_report_id      => 'finance_report',
      p_format         => 'XLSX'
   );

   APEX_DATA_EXPORT.DOWNLOAD(
      p_data     => l_data,
      p_filename => 'Finance_Quarter.xlsx'
   );
END;

Perfect for reports that require advanced financial filters, Persian dates, and multi-column formatting.

---

Advanced Example 2: CSV Export from a Dynamic Query

DECLARE
   l_data APEX_DATA_EXPORT.t_export;
BEGIN
   l_data := APEX_DATA_EXPORT.get_data (
      p_query => q'[
         SELECT full_name, national_id, hire_date
         FROM hr_employees
         WHERE department_id = :P10_DEPT
      ]',
      p_format => 'CSV'
   );

   APEX_DATA_EXPORT.DOWNLOAD(
      p_data     => l_data,
      p_filename => 'Filtered_Employees.csv',
      p_mime_type => 'text/csv'
   );
END;

Great for dynamic button-based exports filtered by user selection.

---

Advanced Example 3: Generate JSON File for DevOps/API

DECLARE
   l_data APEX_DATA_EXPORT.t_export;
BEGIN
   l_data := APEX_DATA_EXPORT.get_data (
      p_query  => 'SELECT id, status, created_at FROM tickets',
      p_format => 'JSON'
   );

   APEX_DATA_EXPORT.DOWNLOAD(
      p_data     => l_data,
      p_filename => 'Tickets.json',
      p_mime_type => 'application/json'
   );
END;

Ideal for microservices, logs, or DevOps exports.

---

Why This Procedure Rocks

---

The Final Result: A Small Change, A Big Win

At the end of the day, the client opened the PDF and said:

“Finally, a proper report. Thank you!”
And us? We sat under that still-weak AC, but with peace of mind—and a smile powered by export success.

---

Final Words

APEX_DATA_EXPORT.DOWNLOAD is a small but mighty tool. Once you use it, you won’t go back to the old ways of file exports.

Still exporting reports with clunky methods? Time for a Nescafé and a fresh look at this simple, powerful procedure.

---

“When APEX works well, nobody realizes how much effort went into making it invisible.”