TOAD 13 Upgrade: Select into causes “numeric or value error: character string buffer too small” error

Question

After upgrading from TOAD v12 to v13 the execute as script (F5) functionality throws "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" on the following script:

VAR v_analyzed VARCHAR2(10); 

EXEC SELECT TO_CHAR(MAX( LAST_ANALYZED) ) INTO : v_analyzed FROM ALL_TAB_COLUMNS;

TTITLE ALL_TABS
SELECT *
FROM ALL_TAB_COLUMNS
WHERE TRUNC(LAST_ANALYZED) =  TO_DATE (:v_analyzed);

TTITLE ANALYZED_DATE
SELECT :v_analyzed AS ANALYZED FROM DUAL;

v12 gives me nice tabs below using the TTITLE as the tab label.

Sam M Sam M · Accepted Answer · 2020-12-23T18:10:43

PL/SQL: numeric or value error: character string buffer too small occurs anywhere you declare a string variable to be a fixed length and your code tries to assign a value whose length exceeds the declared length.

The only place you have declared a string variable is v_analyzed and you declared it to have a length of 10 characters. What happens if you change it to a larger size, say 32 characters? I think you'll see the problem go away.

What's happened in this case is that most likely the upgrade from Toad 12 to 13 also changed your Oracle client version or default settings. When you call to_char() on a date and do not specify a format, Oracle uses your client's default date format settings. Without seeing your specific setup, I am guessing that the Toad 13 default format for dates is different from Toad 12 and is producing a string longer than 10 characters.

In my experience it is best to always specify the date format when calling to_char() to prevent any weirdness introduced by varying default format settings.

TOAD 13 Upgrade: Select into causes “numeric or value error: character string buffer too small” error

1 Answers