Description of the issue
Some users of the report server on data.davidson.edu have reported an issue with certain finance reports like the Monthly Budget Report and Pay History Report. The error typically happens when the user who runs the report selects a large number of options from one of the dropdown filters (parameters), and they use the "Select All" feature when they run the report.
The screenshot below shows an example: the user has permissions to hundreds of funds in Banner, and every single fund is selected from the parameter.
Under these circumstances, the report returns an error like this:
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'nameOfDataset'. (rsErrorExecutingCommand)
ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at "nameOfQuery", line 39 ORA-06512: at line 1
_______________
How do I fix this?
Fortunately, if you're getting this error, there is an easy work-around to avoid this error message. When you run the report again:
- Click the "Select All" option at the top of the list to DE-select all entries in the list.
- Select the "ALL" option that is directly below it (see screenshot).
- Re-run the report.
The report should now run correctly, with no errors.
_______________
Why am I getting this error?
This is a quirk of how the report server communicates with the Banner ODS (operational data store) where the report data originates. When you click the “Select All” option, the report is taking every item you selected, bundling it into a massive list with commas separating each item, and then shipping that list over to the ODS to query the data you requested. Most databases can handle this fine, but in Banner's case this list can become so long that the request exceeds the maximum number of allowable characters, and the ODS will forcibly truncate the list and cause the error to occur.
While it may seem redundant for an "ALL" option to appear literally right below a "Select All" option as shown in the screenshot above, it's actually a method for circumventing this common issue.