I need to set the next date and time format for the next session, something like this:
alter session set nls_date_format='"Madrid day "DD" of "month" of "YYYY" time: "HH:MI"';
But I get the error: "ORA-01801 date format is too long for internal buffer" ...
Is there any other way to apply this format?
nls_date_format is not intended for such a verbose use, and you must consider that it apply also to coversion from char to date. It's a tool for addressing i18n/l10n, not for user output formatting.