This us related question to: APEX LOV Display value look up where Tony Andrews recommends doing:
select display_value
from apex_application_lov_entries
where application_id = 123
and list_of_values_name = 'DEPT_LOV'
and return_value = :p1_deptno;
This appears to reference the application-level LOV entries; is there any way to query the LOV defined in a page-item?
What I would like to be able to do is use a variable to reference the display value of an LOV, instead of its return value. So if I have an LOV in an item called P2_TERM_SELECT then :P2_TERM_SELECT. will give its return value, but is there another variable for its display value?
Answer
Why not, for the sake of convenience, convert the page item lov to an lov in the shared components? I mean, there might be a way to retrieve it from the item, but wouldn't that be just as messy? Where do you need the display value: in plsql or on the page itself? If on the page itself, there is always javascript.
The query Tony uses is one that will retrieve entries from a static LOV. It would be less clear-cut when an LOV would contain SQL.
You can retrieve the settings for a page item LOV:
SELECT lov_definiation
FROM APEX_APPLICATION_PAGE_ITEMS
WHERE application_id = 123
AND page_id = 1
AND item_name = 'P1_DEPTNO';
However, this will return just a string. A select statement will be a string. If you have defined a list of static values, then the return will also be a string: the same one you'd see in the definition. Example:
STATIC2:SALES;10,RESEARCH;20
I don't know a built-in way of parsing that except for perhaps in apex_plugin_util, but i'd argue this is hardly the place to use it. Unless you want to roll your own (edit: i tried with apex_plugin_util.get_data, but it does not work with the STATIC2 format).
I'd seriously consider what you're doing and how it will add up in complexity. I'd recommend to use the shared component LOV if you're going for static lovs, or consider using a lookup table for your values.