​One minor drawback of the Popup LOV (introduced in APEX 19.2) is that it seems to be impossible to quickly clear an existing value via the keyboard. This is not a big deal normally as you could set a ‘NULL value Display’ in the LOV and the user can just pick that. When you are looking to save a few clicks in a high-volume data entry application, however, reaching for the mouse is a problem. In this post I will review an alternative where we can clear an existing Popup LOV value using the Delete key by creating a simple Dynamic Action.

You have selected a value from a Popup LOV, and now you want to clear it using one key. You have 100 more orders to enter this morning and you don’t want to have to reach for the mouse or tab to the NULL value display value and select that. Try as you might Delete, Shift Delete, Ctrl Delete, Space Bar (oops that popped up the LOV again) you just wont clear the field out.

​How to allow the user to clear the Product code they selected and leave the field empty with just one keystroke? Of course, you could set a ‘Null Value Display’ in the LOV definition. But this can be a little confusing to users. For starters what value do you set the NULL Value Display field to? ‘Select me To Clear this Field’?

​There are a number of ways to tackle this. One that has worked well for me is to create a simple Dynamic Action which sets the field value to NULL when the user is focused on the field and hits the Delete key.
 
Here is the definition of the Dynamic Action:

The Dynamic Action is waiting for the user to press either the Delete key (Keycode 46) or Backspace key (Keycode 8), while they are focused on the P2_POPUP_LOV field. This is done by creating a ‘Key Down’ event and using a ‘JavaScript expression’ Client-side Condition to determine whether the True action should be fired.
 
When this condition is met, the True action is setting the value of the field P2_POPUP_LOV to nothing/blank (Set Type ‘Static Assignment’, Value <empty>).
 
Another option that works quite well (but is a little less intuitive) is to use the ‘Double Click’ event to trigger the Dynamic Action when the user Double clicks on the Popup LOV field.

​FYI, the site https://keycode.info/ comes in handy if you ever need to know what the JavaScript keyCode is for a given key (or combination of keys). As you press keys on your keyboard, the site shows you the associated KeyCode in real time.

​I would not recommend the Dynamic Action approach normally as there is a decent workaround in setting the NULL Value Display on the LOV. Having said that, every App has those one or two requirements that make the App feasible. If speedy data entry is the killer feature for your App then this might be worth a try.

​Since the introduction of the new and improved Popup LOV in APEX 19.2, I have been working with our clients to migrate them from various Plugin LOV solutions. During this effort I encountered an issue with setting a default value for a Popup LOV column in an Interactive Grid. Specifically, the return value of the LOV was being set by the defaulting code but the display value was not refreshed to show the newly defaulted value. 

​At one of our clients, we are building an Order Capture application in APEX. This application allows users to enter orders through APEX and then invoices those orders in Oracle e-Business Suite (EBS). Using APEX, we were able to build a custom fit User Interface which significantly increases the number of orders a user can enter in a day. We were also able to leverage standard EBS views and APIs to interact directly with other EBS data (customers, items, purchase orders etc.). This is just one of the many scenarios where APEX can be used to extend standard EBS functionality.
 
In the order capture APEX page, the user selects a Part Number and the system should default a 'Finish' for the selected Part. Nine times out of ten the default finish is correct, so applying the default saves a significant amount of time for the user. Using the new Popup LOV for the Part and Finish fields also means the user can enter an entire order line without having to use their mouse (which they would have to if we used Select Lists).
 
Here is a simplified view of the Interactive Grid where the user selects a Part and Finish.

The problem is when the user selected a Part, the code to set the default Finish was setting the hidden return value OK but the display value was not getting refreshed/changed. What was even more strange was that in about 1 in 10 cases it would refresh the display value.
 
This is how the two columns on the Interactive Grid were configured before we implemented the work around (described later in the post). This screen shot shows you the overall structure of the grid, with the two columns for Part (CUST_ITEM_ID) and Finish (FINISH_CODE).

​The Part field (CUST_ITEM_ID) is setup as a Popup LOV. it has a Dynamic Action which calls PL/SQL code to fetch the default Finish (if there is one). Here is the definition of the Dynamic Action.

The default finish was being set in column FINISH_CODE via the ‘Items to Return’ setting seen in the screen shot below.

​The Finish field is setup as a Cascading LOV, so that when the Parent LOV (CUST_ITEM_ID) is changed, the Finish LOV is also refreshed.

After several attempts at implementing a workaround myself (and lots of Googling), I reached out to the APEX development team. Before doing this, however, I put together a simplified example demonstrating the issue on apex.oracle.com. I can’t stress the importance of this step enough. The APEX development team's time is valuable, I wanted them to focus on finding a solution to the issue, not trying to re-create the issue.
 
At around 10:30pm his time, Joel Kallman received my email and passed it on to Anthony Rayner (based in the UK). When I woke up the next morning, I had an email from Anthony with my sample App updated with a workaround (described below). By 10am the same day, the workaround was implemented in the client's application. If that is not an advertisement for a highly competent, community led development team that cares about their customers then I do not know what is.

So, what did Anthony come up with? It ended up being just a couple of minor changes to what I described above. Firstly, create two new page items P12_FINISH_CODE and P12_FINISH_NAME. These will temporarily store the derived default Finish (return and display value) when the Part field is changed in the grid. These should be hidden fields with ‘Value Protected’ set off.

​Change the ‘Set Default Finish’ Dynamic Action PL/SQL code to set the two new page items with the default Finish return and display values. Make sure to include these in the ‘Items to Return’ setting.

​The next change is reliant on the FINISH_CODE column being assigned a ‘Static ID’ so that it can be referenced more easily from JavaScript.

Finally, add a step to the Dynamic Action with an ‘Action’ of ‘Execute JavaScript Code’. The JavaScript code below sets the display and return value of the FINISH_CODE column in the Interactive Grid (identified using the static ID ‘finishCode’) to the values in the two page items (P12_FINISH_CODE and P12_FINISH_NAME).

​After doing the above, both the display and return values for Finish are set every time the Part number field is changed.

​First of all, I hope this issue gets addressed in the standard APEX product soon. Workarounds like this are great but the additional page items and JavaScript complicate and bloat the page un-necessarily. Apart from that, it also looks hokey.
 
Having said that, I should say that the APEX development team never cease to impress me with their commitment to the customer. I worked for Oracle for nearly 20 years and never encountered this kind of willingness to engage directly with customers from a product development team. Bravo!

​Since APEX 18.1, there has been a new way to call OAuth2 Client Credentials secured web services using APEX_WEB_SERVICE. This new method alleviates the need for developers to store OAuth2 credentials, manage token expiration and also has potential performance benefits. In this post I will describe the new approach and its benefits.

In the OAuth2 client credentials flow you must first call a token web service, passing a client ID and client secret. If these credentials are valid, the token service will then pass back a token. The token can then be used to call certain secured web services covered by the token. The token returned by an OAuth2 client credentials service looks something like the below.

I must confess that I can get stuck in my ways. It often takes a weekend and a few hours buried in the APEX API guide for me to get caught up with the latest and greatest. Case in point, I have been using APEX_WEB_SERVICE for several years and most of my code to call web services secured by Ouath2 Client Credentials looks like this:

1. Fetch token service URL, Client ID and Client Secret from a custom table.
2. Call the OAuth token service to get token using APEX_WEB_SERVICE.
3. Optionally store the token for use again (within its expiration window).
4. Call the main web service using APEX_WEB_SERVICE, passing the token.
5. Repeat as many times as the user calls the web service.

 
Don’t get me wrong, this is still much more convenient than using UTL_HTTP. As a developer, however, I am still having to manage the following:

1. Storing the token end point and the client credentials (most likely not very securely).
   
2. Deciding when the token expires and fetching another token (more likely not bothering to do this and just getting a new token every time I call the main web service).

Of course, there are drawback to this.
 
Storing the Credentials
​I have to build an APEX page to store the token URL and credentials and also some APIs to set and get these values. I then have to build APIs to manage token expiration and decide when to get a new token. Finally, I have to maintain this code and nurse it through upgrades etc. All this was fun the first time but I would much rather have APEX handle this for me so I can focus on delivering business logic.
 
Securing the Credentials
This approach also introduces the obvious security concern which is that I may not be encrypting the credentials when I store them.
 
Performance
Unless you build code to store the expiration time for the token, you are most likely going to call the token service every time you call the main web service. While token services are usually pretty light weight, you are incurring an extra round trip from your database to the endpoint every time you call the main web service. This can add up to a second to every web service call which is noticeable to your end users.

Ever since the APEX development team introduced APEX 18.1 you could sense a shift toward making it easier for developers to talk to the outside world using APEX. This all hinges around the APEX_EXEC package and the drive toward making every APEX component equally as happy sourcing it’s data from a web service as it is from a SQL statement on the local database.
 
Since APEX 18.1 we can make calls to APEX_WEB_SERVICE without having to deal with first calling the associated token service or having to manage token expiration. This is made possible with the addition of 2 parameters p_credential_static_id and p_token_url. These parameters allow you to pass a reference to a set of ‘Web Credentials’ and the token URL to APEX_WEB_SERVICE and it will handle the REST (pun intended).
 
APEX_EXEC For all use cases that fit, I strongly encourage you to look into APEX_EXEC, instead of APEX_WEB_SERVICE. APEX_EXEC takes even more of the load off the developer and has a number of other advantages which I don’t have time to get into in this post. Carsten Czarski wrote a great overview in this post.

​As always, examples speak a thousand words. Before we can reference a set of Web Credentials in our code, we need to define them in APEX. Web Credentials are stored at the Workspace level. Once logged into your APEX Workspace, navigate to App Builder > Workspace Utilities > All Workspace Utilities > Web Credentials.
 
In addition to creating and editing Web Credentials in the APEX UI, you can also set them programmatically using the APEX_CREDENTIAL API (available since APEX 18.2). This API has a number of uses, one of which is to help when migrating new credentials to TEST and PROD instances.

​Note: When you edit Web Credentials APEX does not show you the client secret. It is there, you just can’t see them (for security reasons).

I now want to look back on the three drawbacks from the old five steps approach and see how the two extra parameters above have helped me. The main benefit of course is that we now have just two steps instead of the 5 steps in the original approach:

1. Call the main web service using APEX_WEB_SERVICE, passing Web Credentials Static ID and Token Web Service URL.
2. Repeat as many times as the user calls the web service (APEX handles fetching new tokens etc.).

 
Storing the Credentials
The OAuth2 Client Credentials can now be stored natively in APEX using ‘Web Credentials’. APEX provides a UI to manage these credentials and APIs to set these credentials programmatically.
 
Securing the Credentials
Using Web Credentials, even the Workspace Administrator is not able to view the client secret for a given client. In addition, although there are APIs to set the credentials there are none to get credentials. The only way to use them is via APEX APIs.
 
Performance
​When you pass values for p_credential_static_id and p_token_url you are essentially handing over management of the OAuth2 token to APEX. It will store the expiration for the token, and it will only get a new token when it needs to.
 
All this leaves you as a developer to focus on calling the main web service and parsing the results.

​The APEX development team continues to make it easier for APEX developers to work with REST based web services. This makes it easier and easier for developers to use cloud-based services like SMS, Address Verification, Machine Learning, Object Storage etc. etc. Having easy access to these cloud services allows developers to provide game changing features in their APEX apps.
 
Enhancement Request: It would be great if instead of having to pass p_token_url, we could pass the static id of the remote server plus a suffix for the token service end point. This would prevent us from having to store the URL of the token service in a custom table. Remote Servers are defined under ‘Remote Servers’ in the Workspace Utilities area (right above Web Credentials).