Clearing a Popup Key LOV in Oracle APEX
In the environment of one of our clients, we use Oracle’s Application Express product quite heavily for their identity and access reporting — we’ve got five full blown apps running in that framework at this point. Once in a while we hit a snag in development of an application and post the solution here.
Imagine you’ve got this asset management application for a very large organization, called Conglomo, which has thousands of franchises all over the world. The application holds files that users can see in a portal app. Basically, you upload files, you assign them to organizations (franchises, districts, regions, &c.) and users, and when a user logs in, she can see the files assigned to her or to any orgs in her hierarchy. From there you have a management screen for files, that is basically a report listing them out, and giving you some filters to narrow it down. Say you want to see all files linked to a particular org. The piece of data you need is really the org’s unique ID number, but users need a real way to search for a franchise named “Jack’s Conglomo Outlets Inc,” perhaps and in particular, the one in Bakersfield, California. They don’t want to have to look for org # 5003886, they don’t even know the number.
Well, the ready made Popoup Key LOV form element is just the thing. You drop in some SQL to generate the LOV, and voila you get a nice searchable list. Using a query in this way you can easily concatenate a bunch of fields about the organizations so that when the use is searching for a particular dealer, they have other factors to look at besides the name (and many of them are named quite similarly):
select
id || '-> ' || arnumber ||' '|| name ||' ('|| division ||' '|| type || ')' as d,
id as r
from organization
order by 1
This gives us a Popup Key LOV that looks like this:
When you select an org, the form element drops the display name into that disabled text area, and in a hidden element it drops the key’s value:
<input type="hidden" name="p_t05" value="105" id="P5_ORG_HIDDENVALUE">
So in APEX-land, when we make a report, it’s nothing like what you see in other frameworks, really. We just have one big query that draws up our table. So when we add a new element above, we need to amend the WHERE clause on our query to include that criteria if it was specified. Here’s an example of what I mean:
SELECT
f.id,
f.title,
f.description,
f.file_name,
to_char(f.created_at, 'dd-MON-yyyy HH:MM AM') as created_at
FROM
naap_files f
WHERE
...SNIP...
AND ( :P5_ORG IS NULL OR f.id IN (
SELECT nof.file_id FROM organization_files_join_table nof
WHERE nof.organization_id = :P5_ORG
)
)
So, in this simple case, we’ll get all files if :P5_ORG IS NULL (that’s our Popup Key LOV element in our form). If it’s not null, we limit the list of files to those that are listed in the join table as belonging to the specified Org ID number.
And this really works out great until you need to clear the field because you’re no longer interested in files belonging to Org 105. In the image above you can see a “Clear this field” link in the popup. That’s actually the null option in the elements configuration, you can see it here:
When you click the null option, “Clear this field,” you’ll see the disabled text element get cleared. However, it isn’t really NULL that is sent back up to the host when you submit the form next. The hidden field we saw before is actually set to the string 'undefined'.
<input type="hidden" name="p_t05" value="undefined" id="P5_ORG_HIDDENVALUE">
So, when you submit the form, you’re not sending an empty string, which APEX just turns into a NULL value, you’re literally getting the string undefined, which your query then binds as the org id number! And then no rows come back! FAIL! This may be fixed in later versions of APEX (we’re working in 3.0x), but I did some digging and it doesn’t look like it.
Problems sending an actual NULL value for a select list or some other multi-select element in APEX are nothing new, basically the value that is actually sent is '%null%', even though you specify nothing at all. There are a few workaround, but I usually drop an application computation into each of my apps that zaps these guys and terms them into actual NULL values. I found this solution somewhere inside the APEX forum, I didn’t come up with it myself, but it’s pretty much SOP for anyone doing this kinda work, a PL/SQL block that is run On Submit After Computations and Validation:
BEGIN
FOR rItem IN
( SELECT ITEM_NAME
FROM APEX_APPLICATION_PAGE_ITEMS
WHERE APPLICATION_ID = TO_NUMBER(:APP_ID)
AND PAGE_ID = TO_NUMBER(:APP_PAGE_ID)
AND LOV_DISPLAY_NULL = 'Yes'
AND LOV_DEFINITION IS NOT NULL
AND LOV_NULL_VALUE IS NULL
)
LOOP
IF V(rItem.ITEM_NAME) = '%null' || '%'
THEN
Apex_Util.set_session_state(rItem.ITEM_NAME, NULL);
END IF;
END LOOP;
END;
Since we have this in place, we can just update this bad boy to zap ‘undefined’ values as easily:
BEGIN
FOR rItem IN
( SELECT ITEM_NAME
FROM APEX_APPLICATION_PAGE_ITEMS
WHERE APPLICATION_ID = TO_NUMBER(:APP_ID)
AND PAGE_ID = TO_NUMBER(:APP_PAGE_ID)
AND LOV_DISPLAY_NULL = 'Yes'
AND LOV_DEFINITION IS NOT NULL
AND LOV_NULL_VALUE IS NULL
)
LOOP
IF (V(rItem.ITEM_NAME) = '%null' || '%') OR (V(rItem.ITEM_NAME) = 'undefined')
THEN
Apex_Util.set_session_state(rItem.ITEM_NAME, NULL);
END IF;
END LOOP;
END;
It’s a little odd that even in this fairly recent version of APEX you still have to jump a hoop or two to handle null value submissions, and even weirder that you get '%null%' in some cases and 'undefined' in others. Any web framework has to deal with this kinda thing, but a little consistency would be nice. Hope this is a good primer and introduction to the problem if you’re new to APEX. Although, I have to say that aside from a few weird quirks like this, APEX is absolutely awesome for a situation in which you need to throw together a webapp in a client’s environment and they aren’t interested in any “risky” new fangeled technology like Rails!
It’s hard to convince large companies to work with emerging platforms. There is always a perception of risk that makes tried-and-true platforms like Oracle APEX attractive. I still sometimes yearn to have used Rails for one of these projects, but honestly I can throw together these applications MUCH faster and with way less overhead. I may not have quite the flexibility I get with Rails, but this is all really PL/SQL running on a giant modified mod_plsql, and you can do all kinds of crazy stuff in PL/SQL.
blog comments powered by Disqus