Beyond the Grid: Export Selected Rows to Excel and PDF in Oracle APEX
A little while back I got a task that sounded simple on paper: let the user tick a few rows in an Interactive Grid and export only those rows, not the whole report. It took longer than expected, and I learned a few things the hard way. Writing this down for future reference — sharing it here in case it saves someone a few hours.
The Requirement
The ask was straightforward: an Interactive Grid showing employee data, the user checks the rows they care about, clicks Export Excel or Export PDF, and only the checked rows end up in the file. No full grid dump, no separate export page.
My first thought was that this must already exist in APEX. Interactive Reports and Interactive Grids both ship a built-in export — but both export whatever is currently visible or filtered, never just the checked rows. There's no native "export selection only" switch. So I had to build it myself.
First Attempt That Did Not Work
My first idea: read the selected rows straight out of the Interactive Grid with JavaScript, build a JSON array client-side, then push it into an Excel file using SheetJS in the browser.
It technically produced a file, but the formatting was rough compared to what APEX_DATA_EXPORT gives for free, and PDF wasn't realistically possible without another heavy library. Pulling exact model row data out of the grid reliably after sorting or filtering was fiddlier than expected too. I dropped this after half a day of back and forth.
What Finally Worked
I switched approach entirely. JavaScript now only grabs the selected row IDs and stuffs them into a hidden item, P19_SELECTED_IDS. A PL/SQL process on button click does the real work — APEX_EXEC builds a query context filtered to those IDs, and APEX_DATA_EXPORT streams the xlsx or pdf straight to the browser.
Less code than my first attempt, and far better output quality — APEX handles all the formatting, headers, and file structure for you.
Button click JavaScript
Each button checks something is actually selected, then submits the page with a custom request so the matching PL/SQL process fires.
if (!$v("P19_SELECTED_IDS")) {
apex.message.alert("Please select at least one row to export.");
return;
}
apex.page.submit({
request: "EXPORT_XLSX"
});if (!$v("P19_SELECTED_IDS")) {
apex.message.alert("Please select at least one row to export.");
return;
}
apex.page.submit({
request: "EXPORT_PDF"
});PL/SQL process — Excel
Runs on the EXPORT_XLSX request and filters the export query to the IDs in the hidden item.
DECLARE
l_context APEX_EXEC.T_CONTEXT;
l_export APEX_DATA_EXPORT.T_EXPORT;
l_parameters APEX_EXEC.T_PARAMETERS;
BEGIN
APEX_EXEC.ADD_PARAMETER(
p_parameters => l_parameters,
p_name => 'SELECTED_IDS',
p_value => :P19_SELECTED_IDS
);
l_context := APEX_EXEC.OPEN_QUERY_CONTEXT(
p_location => APEX_EXEC.C_LOCATION_LOCAL_DB,
p_sql_query => q'[
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE empno IN (
SELECT TO_NUMBER(TRIM(COLUMN_VALUE))
FROM TABLE(APEX_STRING.SPLIT(:SELECTED_IDS, ':'))
)
]',
p_sql_parameters => l_parameters
);
l_export := APEX_DATA_EXPORT.EXPORT(
p_context => l_context,
p_format => APEX_DATA_EXPORT.C_FORMAT_XLSX,
p_file_name => 'Selected_Employees',
p_as_clob => FALSE
);
APEX_EXEC.CLOSE(l_context);
APEX_DATA_EXPORT.DOWNLOAD(p_export => l_export);
EXCEPTION
WHEN OTHERS THEN
APEX_EXEC.CLOSE(l_context);
RAISE;
END;PL/SQL process — PDF
Almost identical, just pointed at the PDF format constant.
DECLARE
l_context APEX_EXEC.T_CONTEXT;
l_export APEX_DATA_EXPORT.T_EXPORT;
l_parameters APEX_EXEC.T_PARAMETERS;
BEGIN
APEX_EXEC.ADD_PARAMETER(
p_parameters => l_parameters,
p_name => 'SELECTED_IDS',
p_value => :P19_SELECTED_IDS
);
l_context := APEX_EXEC.OPEN_QUERY_CONTEXT(
p_location => APEX_EXEC.C_LOCATION_LOCAL_DB,
p_sql_query => q'[
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp
WHERE empno IN (
SELECT TO_NUMBER(TRIM(COLUMN_VALUE))
FROM TABLE(APEX_STRING.SPLIT(:SELECTED_IDS, ':'))
)
]',
p_sql_parameters => l_parameters
);
l_export := APEX_DATA_EXPORT.EXPORT(
p_context => l_context,
p_format => APEX_DATA_EXPORT.C_FORMAT_PDF,
p_file_name => 'Selected_Employees'
);
APEX_EXEC.CLOSE(l_context);
APEX_DATA_EXPORT.DOWNLOAD(p_export => l_export);
EXCEPTION
WHEN OTHERS THEN
APEX_EXEC.CLOSE(l_context);
RAISE;
END;One Issue I Faced
Even after switching to APEX_DATA_EXPORT, I hit a wall almost immediately. SELECTED_IDS stores checked rows as a colon-separated string, something like 7839:7698:7902. Splitting this with APEX_STRING.SPLIT and running it straight through TO_NUMBER kept throwing an invalid number error — but only sometimes, which made it confusing to debug.
A trailing colon or stray space around a value made the split return an empty entry, and TO_NUMBER can't convert an empty string. Wrapping every value in TRIM before the conversion fixed it completely. Small fix, cost a good hour of staring at the log wondering why it worked for three rows and failed for four.
One more thing: this is a true Interactive Grid, not an Interactive Report, and it virtualizes rows for performance. Adding CSS row animations with transform on selected rows broke rendering — rows would randomly show up blank. The grid uses transform internally to position virtualized rows, so mine was fighting with it. Switching to background and box-shadow instead fixed it.
Wrapping Up
The working version isn't a lot of code: a couple of JavaScript checks on the button, two nearly-identical PL/SQL processes, and one hidden item holding selected IDs. The time sink wasn't the coding — it was figuring out which approach to use, then chasing down the trim issue.
If you're building something similar: skip client-side export libraries unless you have a real reason, and go straight to APEX_EXEC plus APEX_DATA_EXPORT. Less code, and it works better with how APEX already renders reports and grids.
Thanks for reading this far. If you spot a better way to handle the selected rows part, I'd genuinely like to hear it.
Comments
Post a Comment