On APEX LOV’s and how to define their queries

Defining an LOV for a select-list in Oracle APEX is quite easy. The signature for such a query is simple:

select e.ename as display_value
      ,e.empno as return_value
from   emp e

How difficult can it be?

So, what options do we have when defining the select-list:

Shared Components

I really like the option to store all my LOV definitions centralized, using the Shared Components. Within the Shared components, again I have the option for dynamic or static LOV-definitions. The dynamic option however does only allow me to enter a query, not a “function returning query”.

SQL query

Storing my LOV-queries within APEX does not allow me to centrally manage my code. In smaller applications, where I use application-exports this is not a problem whatsoever, however I tend to work in bigger projects where we store each individual APEX component separately and use GIT and Jenkins to roll-out specific versions of the software.

Static Values

Static Values are great for smaller lists that do not change. But do they really not change? Do we store Yes/No values as Y/N or might we decide in the future to switch to T/F.

Hardcoding is (alomost) never a good idea.

PL/SQL Function returning Query

Finally an option I can manage. I can sore my queries within a package and refer to that packaged function from within APEX. The package on its turn neatly lands in our GIT repository so we can version it and roll it out within our DTAP (Dev, Test, Acc, Prod) environment.

On top of that I can use parameters in those functions to further refine the returned query, win-win situation.

Recap

We tend to use the PL/SQL function returning query to increase the managability of our software. It however has one major disadvantage (as with all options described until now):

The query is stored as text and will not be compiled, nor will it use any performace boost from Oracle’s SQL engine when thinking about bind-variables etc. When changing anything in the underlying tables, Oracle cannot warn me about dependencies. I have to find this out at runtime, or by doing a search for a specific word within my code.

That does not sound the way to go.

I want to store my queries in the database, so I can make use of the dependencies and therefore added a feature request, have you voted?

But there is a very good workaround: pipelined functions:

Back in 2013 Denes Kubicek wrote about a problem when he had quite a big query: “Select List with Dynamic LOV and ORA-06502: PL/SQL: numeric or value error: character string buffer too small“. There seems to be (or at least seemed to be at that time) a 4000 character limit on what the LOV could manage.

Conclusion

I now suggest to use the pipelined function straight in APEX, without the use of any collections, which over-complicate things in my opinion.

Within APEX I can use shared components or I store the query straight into the LOV like such:

select display_value
,      return_value
from   table(gb_select_pck.tasks)

The dynamic part here ofcourse is the reference to the packaged function

My package looks like:

create or replace package gb_select_pck is

  -- Author  : RMARTENS
  -- Created : 01-10-18 23:10:00
  -- Purpose : application select lists

  -- Public type declarations
  type display_value_pair is record(
     display_value varchar2(255)
    ,return_value  varchar2(255));
  type tt_dvp is table of display_value_pair;

/********************************************************************************/

  function tasks return tt_dvp
    pipelined;

end gb_select_pck;

And the body that belongs to that:

create or replace package body gb_select_pck is

  /********************************************************************************/

  function tasks return tt_dvp pipelined 
  is
    t_retval display_value_pair;
    cursor c_lov is
      select task.name      as display_value
      ,      task.task_code as return_value
      from   gb_tasks task
      order  by display_value;
      
  begin
    
    for ii in c_lov
    loop
      t_retval.display_value := ii.display_value;
      t_retval.return_value  := ii.return_value;
      pipe row(t_retval);
    end loop;
    
  end tasks;

end gb_select_pck;

I now have:

• my code store in the database
  • for versioning using GIT, SVN or whatever
  • for using the databases dependencies
  • for central management of my code
• an easy way to extend my LOV’s using parameters etc.
• sql that is compiled and can use the SQL optimizer

I could withdraw my feature request 🙂

Happy APEXING!