2

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?

Community
  • 1
  • 1
Nick
  • 73
  • 1
  • 2
  • 7

2 Answers2

0

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.

Tom
  • 6,988
  • 1
  • 26
  • 40
  • In the page itself unfortunately, I was hoping to not have to get into javascript. Fortunately I have solved my issue by just switching the Pop-Up LOV to a Select List. – Nick Nov 07 '12 at 19:32
0

  1. There is nothing like page-level lov in apex. When you create lov it is always part of shared components and is application level.

  2. P2_TERM_SELECT lov item always returns what you give in the return value.

    So, in your scenariou if you want the description value from :P2_TERM_SELECT then one idea is to have a LOV query as like the below example:

    select display_value disp, return_value || ':' || display_value ret from some_table.

    then to get he display value from P2_TERM_SELECT item then do substr(:P2_TERM_SELECT,INSTR(:P2_TERM_SELECT,':') + 1,LENGTH(:P2_TERM_SELECT))

Thnx

MK

user989729
  • 56
  • 6
  • 1. Yes, there is no page-level lov. But from Nick's post i think it was clear he meant page-item level. There is NOT only the application level LOV, you can define LOVs on items too. I edited Nick's post to reflect the page-item level better. If you feel the need to stumble over this, rather comment this, and take the time to take care of your own post too. 2. Ok that -might- work, but that is just fiddly. That means you will need to manipulate the value substituted to session state too before it is processed into your table. And no, you do not want such a value in your table. – Tom Nov 06 '12 at 22:19
  • I did not saw your comment on Nick's post when i answered the question. Sorry for overstepping on your response. – user989729 Nov 07 '12 at 16:01
  • No problem, you did not overstep. I have no issue with multiple answers, totally fine! I just don't agree with your answer, and don't think it is the right approach. Ultimately, the verdict is up to the community and the OP. – Tom Nov 07 '12 at 16:23