Defining an LOV for a select-list in Oracle APEX is quite easy. The signature for such a query is simple:
How difficult can it be?
So, what options do we have when defining the select-list:
|1||Shared Component||Run-time||No||Central managed within application|
|4||PL/SQL Function Body returning SQL Query||Run-time||No|
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”.
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 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.
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.
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:
The dynamic part here ofcourse is the reference to the packaged function
My package looks like:
And the body that belongs to that:
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 🙂