Hello,
Running APEX 4.2. I have many multi-select drop-down lists on an APEX page, which serve as filters in a large query to display a set of results in a table. When the user makes selections in these lists and clicks on the Search button, the application filters the results and displays an HTML block that lists the selected filter values. To display the selected filter values in an HTML block, the code needs to use the HTMLDB_ITEM.text_from_lov_query to show the display values of these multi-select drop-down lists, instead of the key values.
This worked without a problem, until recently. When a user makes a selection to one multi-select drop-down list, in particular, there is a "numeric or value error: character string buffer too small" exception that occurs internally in APEX:
It occurs on this call:
HTMLDB_ITEM.text_from_lov_query ( selection, lov_definition )
It turns out when a list gets too large, this function can no longer be used to return display values for a selected key. It works for every list in the page, except for one. If I decrease the number of records in that list, it'll work.
Does anyone have a workaround or solution for this problem? These lists are going to get larger, so this issue is not going to go away. I noticed some of the related functions have "_XL" alternatives (like SELECT_LIST_FROM_QUERY_XL and SELECT_LIST_FROM_LOV_XL), but this one does not. I don't have the source for the text_from_lov_query function, but I'm assuming it's -- for some unknown reason -- trying to append all of the values of the list into one big varchar2?
Any help would be appreciated.
Thanks!