Data trapped inside an application isn’t worth much if users cannot pull it out into the formats they actually use every day. Whether your stakeholders are asking for crisp PDFs, spreadsheets, raw JSON, or classic CSV reports, building custom document generation logic from scratch is a massive time sink. Enter APEX_DATA_EXPORT Oracle APEX’s native engine designed to effortlessly transform query contexts into professional files with minimal code.
Supported Export Formats in APEX_DATA_EXPORT
At its core, APEX_DATA_EXPORT bridges the gap between raw database queries and polished file output. By pairing it with the APEX_EXEC query framework, you can feed any SQL query straight into the engine and instantly render files across a wide spectrum of formats including PDF, XLSX, HTML, CSV, XML, and JSON.
Instead of writing complex loops or manual file assembly scripts, you rely on the core EXPORT function, which handles the heavy data lifting and wraps the result into a clean t_export record object containing your LOB contents, MIME types, and file names. Once generated, shipping the file directly to the user’s browser is as simple as passing that record to the DOWNLOAD procedure.
Customizing Export Columns, Groups, and Highlights
A raw dump of data is rarely presentation-ready. Fortunately, the package gives you granular control over how your output looks through specialized collections. Using ADD_COLUMN, you can select a precise subset of fields, rename headings, apply custom format masks, manage alignments, or freeze specific columns for Excel.
Need to organize massive reports? You can bundle columns under umbrella titles using ADD_COLUMN_GROUP to build multi-level header rows. Furthermore, ADD_HIGHLIGHT allows you to inject conditional styling like turning a row or specific cell red based on threshold values while ADD_AGGREGATE lets you calculate sub-totals, control breaks, and grand totals seamlessly.
Styling Reports with GET_PRINT_CONFIG
When it comes to document layouts like PDFs and Excel sheets, formatting matters. The GET_PRINT_CONFIG function acts as your styling studio, letting you define custom page orientations, dimensions, paper sizes, border widths, and color palettes. You can customize fonts, headers, footers, and body styling down to the exact hex code or point size, ensuring your exported documents look like they were handcrafted by a professional designer.
APEX_DATA_EXPORT Example: Export an HTML Employee Report
Let’s walk through a practical example. Imagine you want to query your local database employees, format specific columns with custom headers and currency masks, and immediately trigger an HTML file download.
Step 1: Open the Query Context and Define Columns
First, you open a query context using APEX_EXEC and set up your column configurations using ADD_COLUMN:
SQL
DECLARE
l_context apex_exec.t_context;
l_export apex_data_export.t_export;
l_columns apex_data_export.t_columns;
BEGIN
-- Open query context for local database employees
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db,
p_sql_query => 'select * from emp'
);
-- Define custom columns and headings
apex_data_export.add_column(
p_columns => l_columns,
p_name => 'ENAME',
p_heading => 'Name'
);
apex_data_export.add_column(
p_columns => l_columns,
p_name => 'JOB',
p_heading => 'Job'
);
apex_data_export.add_column(
p_columns => l_columns,
p_name => 'SAL',
p_heading => 'Salary',
p_format_mask => 'FML999G999G999G999G990D00'
);
Step 2: Export and Download the HTML Report
Next, you pass your context and columns into the EXPORT function specifying the HTML format, and hand the resulting object over to DOWNLOAD:
SQL
-- Perform the export
l_export := apex_data_export.export (
p_context => l_context,
p_format => apex_data_export.c_format_html,
p_columns => l_columns,
p_file_name => 'employees'
);
-- Clean up context and trigger download
apex_exec.close( l_context );
apex_data_export.download( p_export => l_export );
EXCEPTION
when others THEN
apex_exec.close( l_context );
raise;
END;
When this block executes, APEX builds the formatted HTML document on the fly, attaches the correct file name, and streams it straight to the client browser while cleanly managing resource cleanup.
Conclusion: Simplifying Data Exports in Oracle APEX
Exporting data used to require external reporting tools or custom, brittle script files that broke whenever a schema changed. With APEX_DATA_EXPORT, Oracle APEX equips developers with a robust, highly flexible engine that manages layout structures, styling configurations, and multi-format generation natively within the database. Whether you are outputting simple CSVs or styled multi-page PDF reports, mastering this package turns cumbersome reporting tasks into clean, maintainable code.
