Introduction
LOV stands for List Of Values. In Oracle APEX, you have the ability to create shared LOVs using intuitive UI.
Records inside the list can be either static (hard-coded) or dynamic.
You can define static data through the user interface provided by Oracle APEX engine. But if you want to display dynamic content then you have to setup some data source. It can be a local database, Web Source or even REST Enabled SQL.
Each record must have a display and return value. Basically “display value” is something that user sees on the frontend while “return value” is used by Oracle APEX to determine which record is selected by user.
There are several advantages of using shared LOVs, but for the sake of this article let me mention few of them.
- You can create one list using Shared Components and then use it on any page you like.
- Data of each list will be stored in a single place which makes it so easy to manage and modify it.
New Capabilities in Version 19.2
With the release of Oracle APEX 19.2 on 1st November, it has introduced some really interesting capabilities for shared LOVs. They even named it as “Expanded Shared LOVs”.
In this release, you will get access to new data sources for creating dynamic lists. These include REST-enabled SQL, Web Source, and even a local application database.
In Oracle APEX 19.2, they have provided a simple interface through which you can easily select display as well as return columns for shared LOVs. Previously you need to write an SQL query manually. So, this is a significant step towards making APEX a really low-code development solution.
Do you want to display an icon near each value of the list, just to make it look nicer? This is absolutely possible, as there is a dedicated input field through which you can select the column of a database table that holds CSS classes. It makes sense to insert CSS classes of FontAwesome in this column. Isn’t it?
Some other enhancements include the ability to sort our lists and group records based on the values of a particular column. You can define separate columns for each of them.
Possible Use of LOVs
LOV is like a generic list of records. So, you can make use of these lists using different types of page items. These page items may include a group of radio buttons or checkboxes, a select dropdown menu, and an enhanced popup LOV (list of values).
How to Create Static or Dynamic Shared LOVs?
You can create as many lists of Values as you like by going to the Shared Components section of your app.
On shared components page, you will see a link labeled as “List of Values”. Press the link and it will move you to a page from where you can view existing LOVs, edit them, or even create new ones.
From this screen, click the Create button. A new popup will appear to enter details for the new LOV. In the first step, I’ll select “From Scratch” and press Next.
In the second step, you will be asked for the name and type of your list. Write anything in the name input field and for type I’ll select dynamic because my data is inside a local database.
At this point, you can select a table from your database, set up a REST-enabled SQL Service, or even retrieve data from Web Source.
Our last step is to specify columns for return and display values. You can use the same column for both if you like, after that press the Create button.
Now you might be wondering how to set the columns for default sort, group values, and the icon. The answer is pretty simple and straightforward, just go to the main List of Values page and select your LOV.
On this page, you will find a section called “Column Mappings” which has all the input fields for customizing any LOV.
Upgrade Existing LOV
By default, these new enhancements are only available if you create a new shared LOV. But there is a way through which you can easily upgrade your existing LOVs.
There is a button labeled as “Convert Legacy SQL”, simply press it. Now APEX will automatically upgrade your existing SQL commands and the new features will be available after that.
