Oracle raises ORA-06502 when a PL/SQL operation writes data into a string variable that is declared with a length smaller than the incoming value.
ORA-06502: character string buffer too small is raised when Oracle tries to store or concatenate a string longer than the target variable allows. Increase the variable size, use SUBSTR, or switch to CLOB/VARCHAR2 with adequate length to fix the error.
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06502 is a PL/SQL runtime exception that signals a numeric or value error caused by writing data into a string variable that cannot hold it. Oracle aborts the current block and returns control to the caller with this message.
The error fires during assignments, concatenations, implicit conversions, or when returned values exceed the destination size.
Fixing it is critical because it halts stored procedures, functions, and triggers that many applications rely on.
The root cause is a size mismatch: the destination variables declaration is shorter than the actual data length.
Common triggers include fixed-length VARCHAR2 declarations, poorly chosen %TYPE anchors, concatenation growth, and implicit numbertochar conversions.
The problem appears instantly at runtime, so unit tests or IDEs like Galaxy can highlight it during development before production jobs fail.
First, locate the offending assignment in the stack trace. Enlarge the variable length, cast or trim the input, or migrate to CLOB when dealing with very large strings.
Recompile the object and rerun the code to confirm the fix.
Guard assignments with LENGTH checks, use SUBSTR to enforce limits, and rely on VARCHAR2(32767) in PL/SQL when feasible.
Procedure parameters based on a 30char table column fail when concatenated with extra text; declaring the param as the table columns%TYPE plus margin resolves the issue.
Functions that concatenate numbers into a status message blow past 255char defaults; switching to VARCHAR2(4000) fixes them.
Bulk inserts with INSERT ALL stumble when a default value exceeds a small columns length; widening the column or trimming the default eliminates the error.
Use VARCHAR2(32767) for PL/SQL variables unless a smaller, welljustified limit exists. Validate external inputs early, and cap concatenations with LENGTH checks.
Enable compiletime warnings (PLSQL_WARNINGS = ENABLE:ALL) to surface size issues.
Galaxys typeaware autocomplete shows column sizes inline and flags suspicious assignments, helping teams prevent ORA06502 before code is merged.
.
Declaring a local variable as VARCHAR2(10) and assigning an 11char value throws ORA-06502 immediately.
Anchoring a parameter to a VARCHAR2(30) column and later concatenating extra text silently exceeds 30 bytes at runtime.
Repeated string concatenation inside loops grows the variable past its original size declaration.
Converting NUMBER values to VARCHAR2 automatically can produce longer strings than anticipated, overflowing the target buffer.
.
Mostlyyes. The full text includes "character string buffer too small" but ORA-06502 can also arise from numeric overflows; inspect the message tail.
No. Lengths above 4000 are allowed only in PL/SQL. In SQL you still need CLOB for larger text.
If the PL/SQL variable is based on that column via %TYPE, yes. Otherwise you must enlarge the variable itself.
Galaxy displays column sizes in autocomplete and warns when assignments risk overflow, preventing ORA-06502 before code runs.
.png)
.avif){kind=logo}
