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

Hello Friends,

I have the following program

-- Equivalent Oracle SQL for retrieving the maximum date and storing it in a PL/SQL variable
max_date DATE;
SELECT MAX(pay_period_end_date) INTO max_date FROM MySChema.pay_ssnchk07;
:max_date := max_date;
The variable Max_date is declaimed as date. Is there anything or expand the buffer size of Max:Date?

Thank you in advance

If you are running your SQL with the "Execute Script (F5)" button, this is expected because DATE is not a SQL*Plus variable datatype. You can see that here if I run the SQL with SQL*Plus. SQL*Plus doesn't recognize the 'DATE' variable type so it doesn't see the variable at all. Toad creates it as a varchar and throws the error you see (which, admittedly, may be a bug because we should give the same error as SQL*Plus)

This "Execute Script" method of query/script execution in Toad simulates SQL*Plus.


Declare :MAX_DATE as a VARCHAR2, and use TO_CHAR when setting its value.
Run the SQL in Toad with "Execute/Compile" instead.

The differences between F5 and F9 are detailed in my rant here. Short version: F9 is almost always the way you should really be running SQL in Toad.

Thank you John,

I appreciated it. I keep learning.


1 Like