Tuesday 19 March 2013

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.



6 comments:

Helpful Advice said...

Thanks for this, really useful info.

Power Cords said...

Thanks for information. What is the standard limitation of SQL Query?

Denes Kubicek said...

4000 characters.

tikah_rasha said...

what about in term of rows?

Denes Kubicek said...

Not sure what you mean by that.

Denes

Scott Wesley said...

Tikah may be referring to this issue, for future readers who may stumble across this https://community.oracle.com/message/14206244