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

This is a common error faced by many of us and some times it takes hours to find what went wrong.

The reason it happens is

1) The size of the Varchar2 parameter, the maximum allowed size of the Varchar2 datatype is 4000, if the input string exceeds that number you will get this error.

2) Or you have defind your parameter of size 10 and trying to write 12 characters.

Workaround - For first reason, there is no way out you can use the Varchar2 datatype, better use CLOB objects.

For second, try to increase the size of the parameter.

Hope this helps.

Thanks
-Azhar

PL SQL query to create a search query.

Hi,

Often we search for a particular value in the user tables of a database, for each table we have to change the query, if not the column name, at least the table name. The good new is, we can avoid doing that. We can write a query which will create a search query as its result records each for a user table in the database.

select 'select * from '||table_name||'
where upper (to_char(&in_column)) = upper (to_char('||'''&in_value'''||'));'
from user_tab_columns
where column_name = upper ('&in_column');

Run tha above query in PL SQL and see the magic.

Hope that helps you.

Thanks..