APEX 4.2 Report Exports and Column Limitations

Larry R

I have an APEX 4.2 application where we want to give users the ability to download a very large report into Excel.  The report has 325 columns and over 20k rows.  I've tried both Interactive and Classic with the following results:

When creating a Classic Report with all of the columns,  an error is generated = ORA-01461: can bind a LONG value only for insert into a LONG column.

When creating an Interactive Report with all of the columns, an error is generated = ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

I do not need to display the report on the page, but require only a way to download the results of the report's query to an Excel file, much like the reports' built-in Download feature.  I would prefer, in fact, to not display the oversized query results.

Scott Wesley

Could you just use some PL/SQL to generate a text file that happens to be comma delimited?

TexasApexDeveloper

Is the query from a single table or multiple tables?  There are multiple routines out on the web that will take a query and return it as am xlsx file.  You just need to search google for them: http://letmegooglethat.com/?q=oracle+apex+export+to+xlsx

Thank you,

Tony Miller

Los Alamos, NM

Larry R

The query joins a materialized view to several tables.  The app sits on a secure network with no internet access, so we are not able to download any extensions, etc.  I need something out of the box.  I looked into using APEX_DATA_PARSER, but it only supports up to 300 columns.

Scott Wesley

Could you just use some PL/SQL to generate a text file that happens to be comma delimited?

Larry R

Created a PL/SQL script to load data into a CURSOR, then used OWA_UTIL.MIME_HEADER, HTP.P, OWA_UTIL.HTTP_HEADER_CLOSE, and HTP.PRN to enable writing to a file.