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

oracle

I got an email from a user when he sees the following error output when he's using our web site.

java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "WEB_OWNER.SSFP_GET_WE_OBJ", line 300
ORA-06512: at line 1

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:137)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:315)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:281)

This is error from oracle webconnect, Oracle Application Server Containers for J2EE 10g (10.1.2.3.0).

Any idea?

Best Answer

The problem is in the error and is common in all programming languages. Your variable is too small for the value you are putting in it. This is a common pl/sql exception. The easy way to keep this from happening is to anchor your pl/sql variables to columns. Do NOT do this

variableName variableType(Variable length)

If you change a column, to make it longer you need to change code to make the variable wider. Do this:

variableName column.table%TYPE

So if you want to base a variable off of the employees table and the customer name field do this

vCustomerName employees.customerName%type;

This has been available in pl/sql for 15-20 years.

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