Oracle 12c, Application Express 5.1.4.00.08
I have a data mining application that has two tables. One table has 2 million rows and the other has 3 million rows of data. I created 10 views to parse the data into 10 regions. These views have up to 200,000-300,000 rows of data. In the application I have an interactive report that pulls the data data in from a view. Currently I have a start row and end row feature so the user can download the data in 50,000 row increments. Trying to download all the data causes a timeout on our servers and the Admins will not up the time out. The 50K pull can take 3-5 minutes to process and then provide a file save window. I am trying to speed this up. I had a directory (CREATE DIRECTORY) created on the server and have a procedure that creates a csv file and saves the file to the directory. The process takes about a minute. The issue I am having is being able to grab the completed file and send it to the user to save on their computer. I Googled and tried a couple of solutions that use an htp.p, setting the mime type but I get errors that I'm using a number greater than 99 (ORA-06512: at line 18 06502. 00000 - "PL/SQL: numeric or value error%s) or the solution doesn't work. Does anyone have an example or a link to an example to create a csv file and then download send it to the user to download? Or can this not be done the way I am thinking? I know of places where we can create a report and then download it but I believe that uses Cold Fusion which I don't have access to.
Thank you in advance for all assistance.
Lawrence