What the error really means
ORA-06502 is raised when PL/SQL tries to put more characters into a variable than the variable can hold. In a tabular form the culprit is often a page item, an APEX collection column, or a hidden column that receives data longer than its declared length. The message "character string buffer too small" tells you the mismatch is on the character side, not a numeric overflow.
Typical sources in an APEX tabular form
A tabular form builds an array of values (f01‑f50) from the HTML inputs. Each array element is stored in a PL/SQL collection (APEX_APPLICATION.G_F01, etc.) that defaults to VARCHAR2(4000). If a column in the underlying table is defined as VARCHAR2(30) but the form allows 100 characters, the bulk‑bind insert will raise ORA-06502. Other hidden columns used for row‑ids, status flags, or computed values can also be too short.
Debugging steps you can run in production
Even when you cannot attach a debugger, you can capture the offending value with APEX debug messages. Add a before‑process PL/SQL block: ```plsql apex_debug.message('G_F01 length=%s, first value=%s', apex_application.g_f01.count, apex_application.g_f01(1)); ``` Enable "Debug Mode" for the session (run the page with `p_debug=YES`). The debug log will show the exact string that overflows the target column. If the log is too large, limit the output with `substr(...,1,200)`.
Another quick check is to query the session state after submit: ```sql select name, length(value) len, substr(value,1,30) sample from apex_application_page_items where application_id = :APP_ID and page_id = :APP_PAGE_ID; ```
Practical fixes you can apply
1. **Resize the database column** – If the business rule permits, alter the table column to a larger size or to CLOB. ```sql alter table my_table modify (description varchar2(4000)); ``` 2. **Trim the input before the DML** – Add a `before submit` process that truncates values to the column length. ```plsql for i in 1..apex_application.g_f01.count loop apex_application.g_f01(i) := substr(apex_application.g_f01(i),1,30); end loop; ``` 3. **Use APEX_ITEM with explicit size** – When you generate the tabular form manually, specify the `p_size` attribute to match the database column. ```plsql apex_item.text(p_idx => 1, p_value => col_name, p_size => 30); ``` 4. **Switch to APEX collections with CLOB fields** – If you need to store very long text, move the data into a collection that uses `CLOB` and insert it later with `DBMS_LOB.SUBSTR`. 5. **Enable length‑semantics** – Ensure the database and the application use the same semantics (BYTE vs CHAR) to avoid hidden truncation when multibyte characters are entered.
Preventive best practices
• Keep the page item length (`maxlength` attribute) in sync with the column definition. • Add a validation rule that checks `length(:Pxx_ITEM) <= column_max_length` before the DML process runs. • Turn on APEX's "Maximum Length" property for every column in a tabular form; APEX will automatically truncate or raise a user‑friendly error. • Periodically review the APEX debug logs after a release to catch new mismatches early. • Document any custom processes that manipulate `APEX_APPLICATION.G_Fxx` arrays, because they bypass the automatic length checks.
Takeaway: Align the size of form items, PL/SQL variables, and database columns to eliminate ORA‑06502 in tabular forms.
People also ask
Why does the error appear only in production?
Production often has real‑world data (longer strings, multibyte characters) that test environments don't replicate, exposing size mismatches.
Can I disable the error by turning off bulk binding?
No; bulk binding is required for tabular forms. The correct fix is to adjust lengths or truncate data, not to disable binding.
Inspired by a public discussion on Stack Overflow. This article is an original explanation for learners.