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.

database diagram
database diagram

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.