Oracle Application Express (APEX) allows developers numerous of ways to make use of List of Values (LOVs). These LOVs are often defined in the moment while the form is being built within the APEX.
As your enterprise applications list grows, it becomes difficult to manage the LOVs by multiple developers. The goal is to allow a centralized management approach in defining, designing, and fetching the associated LOVs.
[su_spacer size=”30″]List of Values Definition Example Query
APEX page items and report columns can store a key-value pair from the LOVs. For example, if the user has to choose a country name field, the country code or country id value would be stored in the field. The associated LOVs SQL query is shown as a simple example below:
SELECT country_name d, country_id r
FROM countries c
WHERE active_flag = ‘Y’
ORDER by country_name;
In the above LOVs definition SQL query, the ‘d’ is the displayed value and ‘r’ is the returned value.
[su_spacer size=”30″]Defining List of Value Queries in Apex
Within Oracle APEX, if you navigate to “Shared Components -> List of Values,” you can define the LOVs.
Two types of LOVs, Static or Dynamic, can be defined. While the static LOVs require values hard-coded for the display/return values, dynamic LOVs are defined using multiple approaches. For maintainability of your APEX applications, it is recommended to use Dynamic LOVs whenever possible.
- Static LOVs – name/value pairs for displayed/returned values – example: Yes;Y,No;N
- Define a SQL Query, such as the example shown in the country names LOV in the previous section
- Invoke a PL/SQL package function call
PL/SQL Function Call
You can associate and invoke a PL/SQL Function Call as shown below:
GET_APP_LOV_LOOKUP(
p_filter_1_in IN VARCHAR2 DEFAULT NULL,
p_filter_2_in IN VARCHAR2 DEFAULT NULL, -- Note: extend LOV return function with all your filters/sorts/conditions
p_lookup_code => ‘COUNTRY’
)
RETURN VARCHAR2;
The above example call, returns the same SQL string that drives the Countries Lookup. However, note that the SQL string will be defined in a custom lookup table structure shown below.
The main advantage of centralizing all LOVs in this way is to allow the APEX application administrators to manage the LOVs in a centralized table structure and provide access to various APEX applications throughout the enterprise portfolio. Furthermore, various security layers or authorizations can be applied on the centralized scheme to allow specific users/developers access.
[su_spacer size=”30″]LOV Storage Table Definition – APP_LOV_LOOKUP
APP_LOV_LOOKUP
lookup_id NUMBER NOT NULL PRIMARY KEY
lookup_code VARCHAR2(40) NOT NULL
lookup_meaning VARCHAR2(200)
lookup_description VARCHAR2(4000)
lookup_query VARCHAR2(4000)
lookup_filter1 VARCHAR2(100)
lookup_filter2 VARCHAR2(100)
lookup_sort_condition VARCHAR2(100)
lookup_comments VARCHAR2(1000
active_flag VARCHAR2(1) DEFAULT ‘Y’
created_date DATE
created_by VARCHAR2(30)
Note: For the simple demonstration of the above approach, the entire query string column – lookup_query – is defined in the above table as a single column. However, you can choose to extend this and add additional columns for specific values such as: display_col, return_col, from_object, order_by_col, etc…
While building the dynamic SQL for the LOV, your PL/SQL API call has to build the select query from each of the column values.
Example record Ddfined for Countries LOV – The INSERT statement below sets up the dynamic SQL query into the APP_LOV_LOOKUP table. You should write SQL Scripts to INSERT your LOV queries into the APP_LOV_LOOKUP table.
INSERT INTO APP_LOV_LOOKUP(
lookup_id, lookup_code, lookup_meaning, lookup_description, lookup_query, lookup_comments
)
SELECT lookup_seq.nextval, ‘COUNTRY’,’COUNTRY’,’Country Name/ID Lookup Definition’,
’SELECT country_name d, country_id r FROM countries c WHERE active = ‘||’’’’||’Y’||’’’’||’ ORDER BY country_name’,
‘Country Lookup Definition’
FROM DUAL;
Extract LOV for a Lookup Code
The code below shows a simple example of how the SQL is extracted and returned to the LOV from the SQL defined in the APP_LOV_LOOKUP table.
Note: You can alter this function signature to match any complex SQL logic, query filters, join conditions, sort order, etc… and apply those onto building the dynamic SQL string that’ll be finally returned as the LOV driving query. This gives maximum flexibility to control the data that is being returned for the LOV and any future maintenance/modifications can be done on the LOV.
This allows the development team, to manage the centralized LOVs via SQL DML management, instead of having to log into APEX.
[su_spacer size=”30″]CREATE OR REPLACE FUNCTION GET_APP_LOV_LOOKUP( p_lookup_code IN VARCHAR2 DEFAULT ‘NA’ ) RETURN VARCHAR2 IS l_default_query VARCHAR2(4000) DEFAULT ‘SELECT ‘||’’’’||’LOV Undefined’||’’’’||’ as d ’||’-1 as r ’||’ from dual’; l_query VARCHAR2(32767); BEGIN SELECT lookup_query INTO l_query FROM APP_LOV_LOOKUP WHERE lookup_code = UPPER(p_lookup_code) AND active_flag = ‘Y’; --// IF l_query IS NOT NULL THEN RETURN l_query; ELSE RETURN l_default_query; END IF; --// EXCEPTION --//above select raises this when no query is defined for the lookup_code WHEN NO_DATA_FOUND THEN RETURN l_default_query; WHEN OTHERS THEN RETURN l_default_query; END; /
List of Values – Page Item Settings
When you define the page item on the APEX Form page, the item type should be set as “Select List” or “PopUp LOV” list item type. Once the item type is defined, the settings for LOVs will be available in the APEX item property. Set the item LOVs as shown in screenshot example below:
APEX 20.x New Features of LOVs
APEX 20.x allows quite powerful and additional facilities to the LOVs Functionality. Please see the Help Section on the LOV definition by clicking on the “Settings -> Multiple Values.”
Please refer to the screenshot below for further details. Also, there are many other facilities available to control the search features of the LOV when it pops up on the page item. If you choose the Multiple Values options, your main stored function that returns the dynamic SQL, can be extended to return the expected values – and not just the displayed and returned key-values.
The PL/SQL stored function based approach is a good choice to control what type and how many fields can be returned for a Multiple Value type of LOV definition thus, once again, centralizing the LOV definition and data management of your LOV queries into a centralized common area.
Cascading LOVs
The LOV building stored function can be extended to accept IN parameters into the function call. For any cascading LOV requirements, pass in the parent page item into the Stored Function call, and apply that item value into your Dynamic SQL string to return a final SQL for your Child LOV’s result set.
[su_spacer size=”30″]Navigation Lists
Using the above dynamic SQL approach, you can build the entire navigation scheme of your APEX Application and manage the LOVs that build your application navigation scheme. You can create a centralized APEX application that contains all your navigation scheme definitions, and use the centralized APEX application to reference across the portfolio of APEX applications within the enterprise. This simplified data access management approach to one point of control renders itself into better security of the application data management and release process.
Conclusion
- Centralizing and building the LOVs queries throughout your enterprise APEX Applications portfolio enables secure, flexible, and lower maintenance costs of your APEX Applications
- This architecture allows the business users or data stewards to manage the enterprise LOVs and provide access to the centralized master data
- Additional flexibility to modify such data queries is within the control of a central data management team who can provide the right information to the applications data sets
- Security of data access is further enhanced by this centralized approach, where only authorized parties can be given access to manage/modify/add the LOVs queries
- Irrespective of the number of APEX Applications, the above approach shall allow easier maintenance of your APEX Applications within the enterprise
Learn More
- You can learn more helpful tips like the ones above by visiting the Traust Consulting Blog.
