A client ran into an error on one of their Application Express reports this week. At first they thought it was caused by some changes they made to data in a table. Upon investigation, that wasn’t necessarily the case.

The error encountered:

report error:
ORA-20001: Error fetching column value: ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Let’s start at the beginning. I was contacted by a long-time client the other day. I was part of a team that helped create an application in Oracle Application Express that collects information from organizations and gives out grants to the ones selected. There are deadline dates for each quarter and if applications are submitted within a certain quarter their application is marked with the specified deadline date. This makes it easy to pull the information when the deadline hits. Recently, they went from four deadlines in a fiscal year to three. The client updated the table to put this into effect. The errors on the report seemed to have appeared after the changes were made.

First thing was first. I had to narrow down if this data actually caused the error or if it was something else by coincidence that caused it around the same time. So, I took to Google. Most of my reading gave the same cause. Trying to fit too much of something into a space that wasn’t big enough. Some people said it was a VARCHAR2 variable that wasn’t large enough to contain the information in some PL/SQL code, and some said it was an list of values (LOV) trying to pull too much information from a table using a query. It actually ended up being the latter.

We had a view setup that was being queried to give the report result set. One of the columns returned was ORG_ID, which is just a number identifier for each organization. Under the column attributes for this column in the report, ‘Display As’ was set to ‘Display As Text (based on LOV, does not save state)’. We had a LOV setup to display the organization name, and the return value was the organization ID. This is where the problem arose. This LOV was returning too much information. Not necessarily too many rows, but too many bytes. Changing the ‘Display As’ back to ‘Standard Report Column’ remedied the issue.

Now that I narrowed down the problem, I just had to add the organization’s name back into the report. I updated the view to include the new column and voila! No more error.

I’m not sure if this will help anyone else running into the problem, but I hope that it at least saves someone a bit of time. I never really found a straight answer searching through Google and the Oracle forums. All of the answers I came across were very cryptic, so I hope this is exactly the opposite!

UPDATE: I was contacted by another company that ran into the same error. They ended up with another issue causing the same codes. The cause of their problem was actually returning too many records to the LOV. Dropping the number of records returned down to a lower number fixed the issue. Because we were just displaying ours based on an LOV and not actually giving the user a dropdown to choose from it was easily remedied. With the issue of too many records in an LOV dropdown, a question you may want to ask yourself is, “Does having this many records in a dropdown make this cumbersome for the user?”. In my opinion, yes. There are other ways that allow the user to select from a list that doesn’t require them to scroll… and scroll.. and scroll… through hundreds of options in a list. An alternative, could be the Popup LOV which provides a search box as part of its functionality.