APEXblog.nl - Tips and Tricks

About This Blog:
I (Richard Weug) started this blog primary for myself to save all kinds of Apex tips and tricks in one place. To use it as a place to write down how I used some coding in my own projects, but also to copy and paste all kinds of articles I find on the Internet. (So I never have to wonder on what website did I read about??? When I see something interesting I collect the content so I have my own archive/knowlegde base.

View my profile on LinkedIn

 

Tips & Tricks

Print
04
Feb

Manage your LOVs in PL/SQL

Written by Richard Weug. Posted in Tips and Tricks

Oracle APEX offers an easy to use, yet 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 times“. However, I always feel like there is one single shortcoming every time I work intensively with LOVs: The inability to parameterize.

Below an illustration:

I’ll demonstrate my point of view with an example. My example application includes three tables: authors, books and languages. Check out the database diagram below to visualize the relations between the entities. It’s quite easy to understand as you will see. The “languages” table holds all languages that are being used within my fictitious app. The entities “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  "d"
     , language_code  "r"
  FROM languages
 WHERE active_ind = 'Y'
 UNION
SELECT language_name  "d"
     , language_code  "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 on an edit. 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 list some facts:

I can not 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 is to define the LOVs on page level. However, this would have a dramatically 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  "d"
               , language_code  "r"
            FROM languages
           WHERE active_ind = ''Y''
           UNION
          SELECT language_name  "d"
               , language_code  "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 take this form:

-- 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.

Original Article:  http://apexplained.wordpress.com/2012/04/05/manage-your-lovs-in-plsql/