APEX

I'm attempting to use htmldb_item.select_list_from_query_xl to obtain a select list for an LOV query which might return too much data for htmldb_item.select_list_from_query. I assume that the former returns a clob because the latter, returning a varchar2, would only be able to return 32k characters worth of select list HTML (or 4k, if you're using it in a query), thus making it capable of handling queries which return large amounts of data without overflowing a varchar2. Oddly enough, though, I get a character or string buffer too small error when calling the procedure with my LOV.

At first I assumed that the LOV, which had a where clause on it, might, for some reason, be too long. So, to test, I aliased the whole thing as a simple view, and tried passing that ("select display_value, return_value from my_schema.make_it_shorter_lov"), with the same error. I then thought that somehow, the query was returning too much data, or that a particular column was too long. So I cut the view query down to a single row, which worked. The data returned by the original query can be summarized as follows: 497 rows, longest display_value of 39 characters, longest return_value of three digits, 19167 total characters in the display value column, 1491 in the return_value column.

In an effort to determine what was causing the query to break (oversized column versus too much data returned versus ???), I set up an artificial query to test the bounds. It was essentially this: select my_schema.make_string(number) as display_value, 1 as return_value from my_schema.table_with_a_lot_of_rows where rownum <= x. The function returns a string of repeating "a"s of the length that I provide. And the table with a lot of rows and the rownum clause allowed me to control how many rows of that string I got back. Unfortunately, I could not narrow the source of my problem down to anything particular that I was passing in. To demonstrate the extremes, here are two queries that worked, and two that didnt':

Working Queries
select my_schema.make_string(1) as display_value, 1 as return_value from my_schema.table_with_a_lot_of_rows where rownum <= 1106;
select my_schema.make_string(4000) as display_value, 1 as return_value from my_schema.table_with_a_lot_of_rows where rownum <= 7;

Failing Queries
select my_schema.make_string(1) as display_value, 1 as return_value from my_schema.table_with_a_lot_of_rows where rownum <= 1107;
select my_schema.make_string(4000) as display_value, 1 as return_value from my_schema.table_with_a_lot_of_rows where rownum <= 8;

As you can see, 1106 rows of 1 character display and return values worked, but 1107 rows did not. And 7 rows of 4000 character display values and 1 character return values worked, but 8 rows of the same did not. Long columns don't seem to do it, and neither do large numbers of rows, either. My two functioning queries exceed both of the maximum column size and row numbers returned by my query that doesn't work. And one of my non-functioning queries doesn't even have 4k worth of data in it total, while the other doesn't even exceed the max varchar2 length of 32767 characters.

As a last resort, I tried switching back to htmldb_item.select_list_from query (the non-clob version), in order to see how that would respond. It also received a character or string buffer too small error. Interestingly, I captured the error backtrace for both calls and the error occurs on the exact same line for both calls:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "FLOWS_030100.WWV_FLOW_ITEM", line 586
ORA-06512: at "FLOWS_030100.WWV_FLOW_ITEM", line 697
ORA-06512: at "FLOWS_030100.HTMLDB_ITEM", line 220

Does anyone know what causes this error, or if there's anything I can do to avoid it? And if anyone on the APEX development team reads this, could you look into the code and see what's going on, or might you know if this can be fixed?