While working with some large scripts I encountered errors related with the 4000 character limit that VARCHAR2 has in SQL but not in PL/SQL.
As explained here the VARCHAR2 datatype has a 4000 character limited, while the PL/SQL has a much larger limit of 32,767.
Why does that difference exist? Wouldn't have been better to have both VARCHAR2 with the same limit? It seems a rather confusing and prone to error decision.
I updated my question to add ansible's comment. Oracle VARCHAR2 and PL/SQL VARCHAR2 limit are on bytes, not in characters. This can have significant impact in multibyte characters system.