Advanced Customization of LOV Items in Oracle Apex

Overview :

Oracle Apex is a powerful platform for rapid application development that enables developers to create dynamic web applications effortlessly. The advanced customization of LOV items in Oracle Apex involves utilizing diverse methods and techniques to improve the drop-down list’s functionality and usability. One of the advanced customization techniques is the creation of Enhanced LOV s, which are drop-down lists equipped with added functionalities such as searching, filtering, and sorting capabilities. This is achieved through the integration of JavaScript libraries, custom PL/SQL functions, or alternative methods.

Technologies and Tools Used :

The following technology has been used to achieve the same.

SQLDeveloper
Oracle Apex

Use Case :

A developer is creating a web application for a HR department. The application needs a drop-down list of employees to select from when assigning tasks. The employee list is large and needs to be searchable, sort-able by last name, and filterable by department. The developer can use advanced customization of LOV items in Oracle Apex to create an Enhanced LOV that provides these added functionalities, improving the usability and efficiency of the application.

Architecture :

Steps :

First, we need to install the plugin in oracle apex schema.(save this plugin as SQL extension and then install) plugin-for-lov
Next, we can run the following package specification and package body

Package Specification :

CREATE OR REPLACE PACKAGE APEX_ENHANCED_LOV_ITEM

PROCEDURE RENDER (

P_ITEM IN APEX_PLUGIN.T_ITEM,

P_PLUGIN IN APEX_PLUGIN.T_PLUGIN,

P_PARAM IN APEX_PLUGIN.T_ITEM_RENDER_PARAM,

P_RESULT IN OUT NOCOPY APEX_PLUGIN.T_ITEM_RENDER_RESULT);

PROCEDURE AJAX (P_ITEM IN APEX_PLUGIN.T_ITEM,

P_PLUGIN IN APEX_PLUGIN.T_PLUGIN,

P_PARAM IN APEX_PLUGIN.T_ITEM_AJAX_PARAM,

P_RESULT IN OUT NOCOPY APEX_PLUGIN.T_ITEM_AJAX_RESULT);

PROCEDURE META_DATA (

P_ITEM IN APEX_PLUGIN.T_ITEM,

P_PLUGIN IN APEX_PLUGIN.T_PLUGIN,

P_PARAM IN APEX_PLUGIN.T_ITEM_META_DATA_PARAM,

P_RESULT IN OUT NOCOPY APEX_PLUGIN.T_ITEM_META_DATA_RESULT);

END;

Package Body : apex_enhanced_lov_item_body

3. Next, create a page item and choose our plugin name. After making the selection,

the following features will be displayed.

Auto complete :

Default settings for auto complete mode. Developer can define what type of filtering will be used. In this example end-user is able to:

Select singular / multiple value(s) from list

Popup report :

Default settings for popup report.

All columns from SQL query are visible
All columns are filterable via column heading
All columns are searchable via column heading
Clicking on row selects value
Popup report title is set as item label text
Popup report is re-sizable
Popup report is drag-gable
Pressing Escapekey closes popup
Number of rows is available to end-user
Global search (in all columns) is available to end-user
Popup size is static (width set to 720px, height set to 541px)
Display (D) and return (R) column are displayed

Custom settings for popup report.

Only described columns (by JSON object) are visible and its attributes are defined by developer (heading, Thalignment, TD alignment, sorting and filtering)
Selecting value is possible only by clicking on checkbox / radio input
Popup report title is defined via the plugin attribute
Popup report is not re-sizable
Popup report is not drag-gable
Pressing Escapekey has no effect
Changing number of rows is not available to end-user
Global search (in all columns) is not available to end-user
Popup height is adjusting to its content
Popup width is set to 480px
Default sorting is set to column “Salary” with direction “descending”