Oracle: Temporarily change an NLS setting in a session
The following simple anonymous PL/SQL block tries to demonstrate how an NLS setting can be temporarily changed and then reset to the previously active one.
declare
nls_date_format varchar2(30);
begin
--
-- save the current nls_date_format.
--
nls_date_format := sys_context('userenv','nls_date_format');
--
-- Change the nls_date_format setting.
-- Note that the format needs to be enclosed in double-quotes.
--
dbms_session.set_nls('nls_date_format', '"dd.mm.rrrr"');
--
-- Print the current date in the active date format:
--
dbms_output.put_line(sysdate);
--
-- Reset the nls_date_format setting
-- Note again the double-quotes.
--
dbms_session.set_nls('nls_date_format', '"' || nls_date_format || '"');
end;
/