sys@ORA92> alter session set nls_date_format = 'day-month-yyyy'
  2  /

Session altered.

sys@ORA92> set serveroutput on
sys@ORA92> declare
  2    initial_date_lang varchar2(64);
  3    current_date_lang varchar2(64);
  4  begin
  5    -- store value of initial nls_date_language to variable and display it:
  6    select value
  7    into   initial_date_lang
  8    from   v$nls_parameters
  9    where  parameter = 'NLS_DATE_LANGUAGE';
 10    dbms_output.put_line ('initial nls_date_language: ' || initial_date_lang);
 11    dbms_output.put_line (sysdate);
 12    --
 13    -- change the value of the nls_date_language and display it:
 14    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''FRENCH''';
 15    select value
 16    into   current_date_lang
 17    from   v$nls_parameters
 18    where  parameter = 'NLS_DATE_LANGUAGE';
 19    dbms_output.put_line ('current nls_date_language: ' || current_date_lang);
 20    dbms_output.put_line (sysdate);
 21    --
 22    --
 23    -- change the value of the nls_date_language and display it:
 24    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''ITALIAN''';
 25    select value
 26    into   current_date_lang
 27    from   v$nls_parameters
 28    where  parameter = 'NLS_DATE_LANGUAGE';
 29    dbms_output.put_line ('current nls_date_language: ' || current_date_lang);
 30    dbms_output.put_line (sysdate);
 31    --
 32    -- change the value of the nls_date_language and display it:
 33    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''GERMAN''';
 34    select value
 35    into   current_date_lang
 36    from   v$nls_parameters
 37    where  parameter = 'NLS_DATE_LANGUAGE';
 38    dbms_output.put_line ('current nls_date_language: ' || current_date_lang);
 39    dbms_output.put_line (sysdate);
 40    --
 41    -- change the value back to the stored initial value and dispaly it:
 42    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE = ''' || initial_date_lang || '''';
 43    select value
 44    into   current_date_lang
 45    from   v$nls_parameters
 46    where  parameter = 'NLS_DATE_LANGUAGE';
 47    dbms_output.put_line ('current nls_date_language: ' || current_date_lang);
 48    dbms_output.put_line (sysdate);
 49  end;
 50  /
initial nls_date_language: AMERICAN
saturday -august   -2004
current nls_date_language: FRENCH
samedi  -ao�t     -2004
current nls_date_language: ITALIAN
sabato   -agosto   -2004
current nls_date_language: GERMAN
samstag   -august   -2004
current nls_date_language: AMERICAN
saturday -august   -2004

PL/SQL procedure successfully completed.