APEX 5 brought new functionality which allowed us to significantly improve our users workflow. Providing modal popup functionality out of the box is just one of many. Users can now take an action on a report via a popup and still maintain the context of the report in the underlying window. APEX 5.1 is taking this a step further with the interactive grid, which allows users to make updates directly in the report.
There comes a time, however, when you just want to perform a pre-destined action on a report row without having to open a modal, update a value or navigate to a detail page. You just want to click on the row and perform the action.

I am currently working on a replacement dash boarding system which pulls data from Oracle e-Business Suite. My client has over 100 pages built on a Microsoft technology that I am replacing with an APEX 5 App. The system has hundreds of users and the client needs to be able to authorize and de-authorize users quickly and easily. Although users would be authenticated using Active Directory we still needed a customized Authorization scheme and the maintenance pages that come with it. As users come and go, the admin needs a way to quickly de-authorize. We will accomplish this by providing a trash icon on each row of a report. When the user clicks the trash icon, it should delete the row and refresh the table.

The demo above is based on a read only interactive grid on the table XX_APXDB_SECURITY_AUTH. My approach is to capture the click on the trash icon, call a JavaScript function to update a page item with the record id, create a Dynamic Action on the page item which in turn performs the actual delete and a refresh of the report region. I know that sounds like a lot of steps, but it is simple to setup.

Note: All the code except for a few items (which I will highlight) will also work in APEX 5.0.X

Page Source
We are going to start by adding some JS and CSS at the page level:

Notes:

• We are changing the column to be a Link
• Using <span class... to display a trash can in the column header and for each row
• The link Target tells APEX to call a JavaScript function called 'xxDeleteAuth' passing the AUTH_ID of the current row. FYI, in APEX 5.0.4 using #AUTH_ID# worked but in 5.1 only &AUTH_ID appears to work.

New Page Item P65_AUTH_ID_TO_DELETE
The JavaScript function (at the Page Level) is going to populate this page item with the value of the AITH_ID clicked on by the user.

Thanks to an update from Scott, to be on the safe side you should also add a step to the end of the Dynamic Action to clear the value of P65_AUTH_ID_DELETE. Just in case you are using it somewhere else.

BTW, I really can't say enough how nice it is to have 'apex.message' functionality to show nice looking messages to my users without a page refresh!

As you can see, this is a powerful user interaction model. Using the approach, we can significantly improve the workflow of users who need to make a decision and perform a single action repetitively to many records.

Some other examples where this technique could be used:

• User clicks on a Tick or X icon to Approve or Reject a series of records in their 'Inbox'
• User marks a record a read
• User closes out a service request record

Let's face it, Binary Large Object (or BLOB) columns in the Oracle database are convenient. You can easily create, fetch and delete files of all sizes using PL/SQL and APEX. This ease of use does come with a few downsides, however, namely:
​

• They increase the size of your database considerably. This makes backing up and restoring your database a more time consuming process.
• It is costlier; the fast disk your database is running on is more expensive than Cloud Object Storage.
• APEX must deal with fetching these files across the same connection as the rest of your APEX page thus increasing the load on the database and ORDS.

The answer is to use abundantly available (and cheap) cloud based object storage to manage files used in your APEX Applications. Until recently, I have primarily used Amazon's S3 for storing files. On my latest project, however, I have been working with Oracle's Storage Cloud Service (OSCS). This post describes a general approach for storing files in the cloud and a specific sample APEX application that utilizes OSCS to store files for an APEX application.

This section describes a general approach for implementing a cloud based storage solution for APEX. This approach could be used for any cloud storage service including AWS S3, Google Drive, Drop Box and of course Oracle's Storage Cloud Service.

• Create a 'File Details' table to maintain a local store of file reference information:
  • File ID (Generated by a Sequence)
  • File Related Object Name (e.g. Customer)
  • File Related Object ID (e.g. the ID of the Customer Record)
  • Visible File Name
  • File Mime Type
  • File Size
  • Storage File Name
  • Storage REST End Point URL
  • Status (EXISTS, DELETED)
• Create a new Container/Bucket in your Cloud Storage service to store files used for your APEX Application
• When the user uploads a file in APEX:
  • Store file temporarily in APEX_APPLICATION_TEMP_FILES
  • Use APEX_WEB_SERVICE.MAKE_REST_REQUEST to call a REST API to Upload those Files to Cloud Storage
  • Save a record for each file stored in the 'File Details' table mentioned above
  • Make sure the file name in the Storage Service 'Storage File Name' is unique by prefixing the filename with a unique sequence. Users will see 'Visible File Name'.
  • Update the related entity table (e.g. Customer Table) with the File ID
• When the user wants to view available files:
  • Query file information from the local File Details table
  • Avoid doing this from the Cloud Storage Service unless you must, as it will be much slower than pulling it straight from your local table
• When user wants to download a file:
  • Use APEX_WEB_SERVICE.MAKE_REST_REQUEST to call the REST Service to retrieve file from Cloud Storage into a local BLOB or collection
  • Download the file for the user
• When user wants to delete a file:
  • Use APEX_WEB_SERVICE.MAKE_REST_REQUEST to call the REST Service to delete a file
  • Mark file as deleted in the local file details table
• Other considerations:
  • Consider limiting the file size at time of upload. Don't take this too far, however, as this can get annoying for your end users who know storage is cheap
  • Consider compressing the file before uploading it to reduce file sizes
  • Consider storing non-proprietary, frequently used logos and image files in Cloud Storage and have a publicly available URL. You can reference these files directly via the URL and there will be no round trip to the database. This will act like a Content Delivery Network of sorts. Of course, another option is to store these kinds of files on the Application Server that ORDS is running on.

Before you can start calling the OSCS REST services, you will need to perform some preparatory steps:

Storage Cloud

• Make sure you have an Oracle Cloud account setup with the 'Storage_ReadWriteGroup' role
• Make sure you can login to the Storage Console and upload files from the console
• Create a new container to store your files
• Gather the following information about your OSCS account:
  • REST Service END Point e.g. https://a123456.storage.oraclecloud.com/v1/Storage-a123456
  • Identity Domain Name e.g. a123456
  • Region e.g. US2
  • Oracle Documentation Reference Link

Upload the Cloud Storage SSL Certificate to your Database

• Get the top level (PEM) certificate from the following URL:
  • https://storageconsole.us2.oraclecloud.com/
• Import the certificate into your database wallet
• e.g.
  • orapki wallet create -wallet /home/oracle/mywallet -pwd <<Wallet Password>>
  • orapki wallet add -wallet /home/oracle/mywallet -trusted_cert -cert DownloadedCert.crt -pwd <<Wallet Password>>

As with most Cloud Services, Authentication is handled via a token. Every time you call a web service you will need to pass an active token to prove you have access to the resource. To get a token, you need to call the authentication service. Here is some sample code to do this, using the APEX Web Service API. You will notice some global variables, make sure you set these appropriately with your specific values. The token is active for 30 minutes so I suggest creating a PL/SQL function you can call to get the token before calls to other web services.

Suggestion: Use Postman (or a similar tool) to call the service first. Try it out in PL/SQL once you have it working there. Oracle Authentication REST API Documentation Link​

I built a sample APEX application that allows a user to upload file(s) to and view files in a given container on OSCS. In this section I will provide sample PL/SQL code to allow you to put files in a container and list the files in a container. For the sake of simplicity, this App strays from the approach described in the Approach section of this post (i.e. I am not creating a local File Details table).

Here is the PL/SQL for Adding a file. I assuming that you have already used a 'File Browse' item in APEX and uploaded a file or files to the APEX_APPLICATION_TEMP_FILES table. The code below will loop through these files and PUT them into Cloud Storage. Oracle Documentation REST API Link

The code below will call a REST service to fetch a list of files for a container. Oracle Documentation REST API Link
In our case we are going to ask for the list pf files in JSON, which will look like the following:

While not comprehensive, (I did not cover deleting files, adding file metadata, generating temporary links to files) I hope I have at least got you thinking about alternate ways to store BLOB data. Storage is cheap and you will be doing your DBA and your infrastructure a favor by keeping BLOBS out of your database wherever possible.

Other Reference Information

• A-Team Article on Calling the Storage Cloud REST APIs
• Oracle Storage Cloud Service (OSCS) API Reference Manual