Skip to Main Content
Feature Request FR-4314
Product Area Page Components
Status CLOSED

1 Voters

Dynamic SQL query having 32767 max limit while PL/SQL Function body returning SQL

chandlerbing0010 Public · Mar 6 2025

Idea Summary
I am creating a classic report where the underlying table contains around 200 columns. Additionally, I am applying a custom filter with more than 4000 characters. To handle this, I used VARCHAR2(32767) for the SQL query variable in a PL/SQL function. The function compiles and saves correctly, but during page rendering, I encounter the error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

My SQL query includes column names as aliases, and when printed, it is approximately 11,500 characters long. Ideally, this should work, but I suspect there might be a hidden limit on the number of columns or characters affecting the UI.

Use Case
For large tables, it is essential to support SQL queries up to 32,767 characters, ensuring that extensive reports with multiple columns and complex filters can run without issues.

Preferred Solution (Optional)
Increase the supported SQL query length to 32,767 characters for proper execution and display in reports.

We reviewed this idea carefully and came to the conclusion that it does not constitute an appropriate APEX feature request.

Comments

Comments

  • fac586 OP 1.1 years ago

    For large tables, it is essential to support SQL queries up to 32,767 characters

    This is already the case.

    The function compiles and saves correctly, but during page rendering, I encounter the error:

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

    Such errors do not relate to the size of the report query but to report row output (consisting of all data and HTML mark-up) exceeding 32KB.

  • vincent morneau Admin OP 1.1 years ago

    @chandlerbing0010 if this is true it would be a bug. If you have a reproducible test case please post it on the forums.