I have the following program
-- Equivalent Oracle SQL for retrieving the maximum date and storing it in a PL/SQL variable
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.
: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.