Oracle Application Express offers an easy to use and effective way to control the list of values (LOVs) within your application. This module, which can be found under Shared Components, enables you to create two different types of LOVs: static and dynamic. Reusability and maintainability are the key principles here: create once, use many. However, I always feel like there is one single shortcoming every time I work intensively with LOVs: The inability to parameterize.
I’ll demonstrate this statement through an example. My example application includes three tables: AUTHORS, BOOKS and LANGUAGES. Check out the database diagram below to visualize the relations between the tables. It’s quite easy to understand as you will see. The LANGUAGES table holds all languages that are being used within my fictitious application. The tables AUTHORS and BOOKS contain a foreign key to the LANGUAGES table. Notice the ACTIVE_IND (active indicator) column to indicate whether a language is active or not.
The problem
Imagine I’d be creating a form based on the AUTHORS table to create and edit authors. This would require me to define a dynamic LOV in Shared Components when creating a select list item for the FK_LANGUAGE_CODE column. I named the LOV LOV_LANGUAGES. The source query for this LOV looks as follows:
SELECT language_name as d , language_code as r FROM languages WHERE active_ind = 'Y' UNION SELECT language_name as d , language_code as r FROM languages WHERE UPPER(language_code) = UPPER(:P10_FK_LANGUAGE_CODE)
I first select all active records, followed by a UNION part to retrieve the possible inactive language of an author while viewing or editing an existing record. This means that when you create a new author, only the active languages are displayed in the select list (P10_FK_LANGUAGE_CODE would be null).
My next task is the creation of a form based on the table BOOKS to create and edit books. Another select list item is needed for the FK_LANGUAGE_CODE column. I would love to reuse the LOV_LANGUAGES list of values for reusability and maintainability reasons. But, let’s face some facts:
- I can’t reuse the LOV_LANGUAGES list of values since its source query contains a reference to P10_FK_LANGUAGE_CODE
- I could define a new LOV for languages, based on LOV_LANGUAGES in which I replace P10_FK_LANGUAGE_CODE (authors) by P20_FK_LANGUAGE_CODE (books). This just doesn’t feel the right thing to do, especially when you keep in mind our key principles: reusability and maintainability
- Another solution would be to define the LOVs on page level. However, this would have a bad effect on the maintainability of my LOVs
The solution
I was unable to come up with a decent solution until I found out that it is possible to define an LOV by referencing a PL/SQL function. Such a function simply returns the source query for the LOV as a string (VARCHAR2). Below an example on how I’d define the LOV_LANGUAGES list of values:
FUNCTION fun_get_languages(p_language_code IN VARCHAR2) RETURN VARCHAR2 AS BEGIN RETURN 'SELECT language_name as d , language_code as r FROM languages WHERE active_ind = ''Y'' UNION SELECT language_name as d , language_code as r FROM languages WHERE UPPER(language_code) = ''' || UPPER(p_language_code) || ''''; END fun_get_languages;
By using PL/SQL functions, I no longer need to define my list of values under Shared Components. I simply call the FUN_GET_LANGUAGES function on page level for each select list item to dynamically construct the source query. The list of values definition will look like this:
-- LOV definition on page 10 (authors) RETURN pck_my_package.fun_get_languages(:P10_FK_LANGUAGE_CODE); -- LOV definition on page 20 (books) RETURN pck_my_package.fun_get_languages(:P20_FK_LANGUAGE_CODE);
Managing your LOVs in PL/SQL follow a similar approach as in Shared Components, with the advantage that PL/SQL functions are more reusable since they can be passed parameters. Your LOVs will therefore be more maintainable because all your logic is in one place, and not being duplicated in different places.
I’m sorry. I defined a SHUTTLE on the page…which is just a high octane LOV. I created a function which returns an SQL query based on LIST and VALUES and now I’m just unsure how to call that for the shuttle to return the query and thus the lis of values…
LikeLike
I’m wondering where to reference the LOV function goes. I have the LOV function and I’m not sure how to wedge it into my “shuttle” widget on the page. could you provide instructions on how to do so…thanks
LikeLike
Hello there,
I’m not completely sure what your question exactly is, but I think you are referring to the “List of values definition” in the “List of Values” section of your shuttle item. You can call the function which generates the SQL query like this:
RETURN schema.function;
LikeLike
Great information. Got this to work for a Standard List. But APEX seems to not like this for a POP LOV. Is there a trick to get this to work for this?
LikeLike
Not sure what is going on, but was making some additional changes and now it appears to be working. Will keep testing further. Maybe should also do a clean up as well next time around.
LikeLike
This approach seems to work for the most part. But I’m experiencing a few strange results. From The LOV, I need to pass the ID value to another ITEMS condition clause. This works if I use a “standard” defined LOV. But not when I use a LOV from a function like your example. Also, if I just key in the SQL to the LOV, it also does not work. I need to base the LOV based of some conditional logic, so really need to get this working. Does anyone have any suggestions?
LikeLike
Hey Brian,
I gave this a try but I couldn’t identify abnormal behaviour. I don’t think there’s any difference between a “standard” defined LOV or an LOV defined as described in the article. Using the value of an LOV in a condition might be tricky however. The condition on a page item is evaluated only on page load and uses the LOV value from session state. Does that cover your needs? I typically use a dynamic action to show or hide a page item based on the value of the LOV item.
Hope that helps,
Nick
LikeLike
Nick,
Thanks for getting back to me. Yes, I think the condition did not process as I thought and you clarified this. I changed to use dynamic actions and it’s now working as I expected. Thanks for your help!
Brian
LikeLike
Nick,
I am trying to follow this methodology for a Select List in each row of a Tabular form. I need to send a value of a column from each row of the form as a parameter to my function. How do I send the APEX collection value such as HTMLDB_APPLICATION.G_F02(i) from the tabular form to the function?
Thanks,
AJ
LikeLike
Hey AJ,
I gave this a try, but I haven’t found a way to reference a row value from within the definition of a LOV. This is actually a restriction imposed by tabular forms. Too bad.
I found the following thread on OTN, but no luck as well: https://community.oracle.com/thread/1057424?tstart=0
Kind regards,
Nick
LikeLike
I am using Apex 5.0. I have a field, model, on a form where I would like to allow the user to select the vehicle models from a drop-down based on a vehicle make, make, a previous field selected by the user from a drop-down on the form. I have the Makes drop-created and working thru LOV.
I have set up the function in the HTML header based on the function you wrote. I have a parameter defined and am passing in the make in the current form field, however, I am unclear on how to call the function. I assume it is at some point when the user clicks the arrow for the drop-down when selecting the model.
I am also unclear where to put the RETURN as you describe it in your article.
In HTML header of the page:
FUNCTION fun_get_models(p_make_code IN VARCHAR2)
RETURN VARCHAR2 AS
BEGIN
RETURN ‘SELECT vehicle_model as d
, vehicle_model as r
FROM vehicles
WHERE UPPER(vehicle_make) = ”’ || UPPER(p_vehicle_make) || ””;
END fun_get_languages;
The call as I understand it. Tried to add to Source Type as a PLSQL Function body but that was not working:
RETURN fun_get_models(:P27_VEHICLE_MAKE);
If you have an example of this in Apex 5.0 your help would be appreciated.
LikeLike
That’s not the right place to put the PL/SQL function. Create a standalone function or package to define the FUN_GET_MODELS function.
You should also be using the Cascading LOV feature:
http://www.inside-oracle-apex.com/oracle-apex-4-0-cascading-lovsselect-lists/
It allows you to link two select list fields. So if you change the value of select list A, the LOV for select list B will automatically refresh.
Feel free to create a test case on apex.oracle.com if you’re still having problems. That way, I can help you further by providing you a working example.
LikeLike