Select List with Dynamic LOV and ORA-06502: PL/SQL: numeric or value error: character string buffer too small

If you need to create a dynamic SQL for a select list or any other type of a LOV, your query should not exceed 4000 characters. In case it does, you will get the "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" error. That is the internal limitation of the APEX table storing the item attribute information. In case you can't rewrite your SQL to satisfy this limitation, you may use the package I created for that purpose. It is quite simple and it does the following:

1. It will use the apex_collection package to parse the query and crate a collection

2. It will query the collection and use a pipelined function to return a simple LOV (SELECT d, r FROM table)

You can also use the get_long_dynamic_query function in the package to generate your dynamic SQL. The example at apex.oracle.com I provided extends the query with "dummy" conditions in order to make it long enough and prove the concept.