Wednesday, October 07, 2015

About downloading files from an Apex page without using download links

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Yesterday I needed to download a file as the final step in processing a user request involving a tabular form. For some reason this requirement is somewhat unusual if you look at the most typical scenarios where you either download files stored in a table by means of report links or a static link where the developer can leverage Apex's built-in features for this purpose.
The main difference with my need lies in that there is no file at all to download until the user has selected one or more checkboxes in a tabular form and I need to process the tabular form after the submit in order to create the file.
In a case like this you cannot simply put the file download logic inside an after submit process because the browser will reload the page after the submit, so the file download request won't be caught by the browser. An alternative would be to create the file and then reload the page showing the download link somewhere, but I preferred to avoid the unnecessary page reload.

Luckily enough I stumbled upon Joel Kallman's posting about the more generic "file downloading" requirement and more specifically a comment he made gave me this idea.

As Joel says one can invoke the file download by means of an on-demand process that can be triggered from an Apex standard URL, so what if I try to set up the parameters in the branch section as needed?

The simple answer is: it can be done and it works well, the browser won't reload the page but it will begin the file download instead.

configuring a branch to trigger a file download request

The prerequisites for the branch are as follows:
  • a DOWNLOAD button performing a normal page submit.
  • a process that handles the tabular form data and gathers the rows involved, creating a file and storing it temporarily or permanently in a table of your own choosing, running conditionally on the pressing of the DOWNLOAD button.
  • An application item called FILEID containing the key to retrieve the file from the table above or alternatively a page item if the file download application process doesn't need to be invoked elsewhere.
  • An application process named DOWNLOAD that is invoked as shown above which reads the blob from the table using the provided key value.
A final note about this method: as I am storing the file(s) in a table I wanted to delete them automatically right away once the download was finished.
Initially I thought it would be smart to delete the relevant row from the table after the call to WPG_DOCLOAD.DOWNLOAD_FILE but to my surprise rows are not deleted from the table even if I explicitly commit the operation just after the delete statement.

It looks like as if any code after WPG_DOCLOAD.DOWNLOAD_FILE doesn't get executed at all.

Failing to find a solution for this issue, I fell back to the simple workaround of deleting the rows older than 1 day when invoking the code for creating a new file, but one may opt for a batch procedure that does the housekeeping as well.

Thursday, July 23, 2015

About using APEX_COLLECTIONS in a tabular form with INSTEAD OF triggers

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
In case you dreamed of updating an Apex tabular form built on top of a view based on APEX_COLLECTIONS by means of INSTEAD OF triggers, you must be ready to grant the following rights to the schema user attached to the workspace, otherwise you'll get an ORA-01031: Insufficient privileges run-time error.
Each privilege is only required for the corresponding action, if you don't need to delete from the view you can safely omit GRANT DELETE for instance.

This DDL can only be executed by the DBA or by the APEX_xxxxxx user.
Probably the DBA won't like to do it for the usual security issues.

Note also that this kind of permissions are not carried over during subsequent Apex upgrades, which means that tabular forms will stop working if you forget to re-execute the grants after a major upgrade.

See message translations for ORA-01031 and search additional resources.

Tuesday, June 09, 2015

In case you wish to download Apex 5.0 documentation as a zip file

For some reason the official page for Oracle Application Express release 5.0 doesn't include a download link for its documentation library, while archived releases are all available for download from the following page:


Fortunately for us the consistency in Oracle's internal documentation library organization provides us with the following valid link for downloading the whole document set for Apex 5.0.


yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles