一般来说设置会话级的状态都是通过ALTER SESSION语句，也有个别的例外，比如角色的设置是通过SET语句实现的。Oracle除了提供SQL的方法外，还提供了PL/SQL的接口，DBMS_SESSION包，将会话状态的设置和查询集成在这个包中。

简单介绍一下SET_NLS过程，使用这个过程可以设置会话级的NLS参数设置：

SQL> SELECT * FROM V$NLS_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   SIMPLIFIED CHINESE
NLS_TERRITORY                  CHINA
NLS_CURRENCY                   ￥
NLS_ISO_CURRENCY               CHINA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              SIMPLIFIED CHINESE
NLS_CHARACTERSET               ZHS16GBK
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              ￥
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

已选择19行。

下面利用SET_NLS过程修改NLS_DATE_FORMAT变量：

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
--------------
20-3月 -10

SQL> EXEC DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT', 'YYYY-MM-DD HH24:MI:SS')
BEGIN DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT', 'YYYY-MM-DD HH24:MI:SS'); END;

*
第 1 行出现错误:
ORA-00922: 选项缺失或无效
ORA-06512: 在 "SYS.DBMS_SESSION", line 164
ORA-06512: 在 line 1

SQL> EXEC DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT', '''YYYY-MM-DD HH24:MI:SS''')

PL/SQL 过程已成功完成。

SQL> SELECT SYSDATE FROM DUAL;

SYSDATE
-------------------
2010-03-20 01:44:22

需要注意，SET_NLS的第二个参数VALUE输入的值除了需要的格式外，还需要包含引号，否则会引发错误

其他的会话级NLS参数也可以修改：

SQL> SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL;

TO_CHAR(S
---------
星期六

SQL> EXEC DBMS_SESSION.SET_NLS('NLS_DATE_LANGUAGE', '''ENGLISH''')

PL/SQL 过程已成功完成。

SQL> SELECT TO_CHAR(SYSDATE, 'DAY') FROM DUAL;

TO_CHAR(SYSDA
-------------
SATURDAY

SQL> EXEC DBMS_SESSION.SET_NLS('NLS_LANGUAGE', '''AMERICAN''')

PL/SQL procedure successfully completed.

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
20-MAR-10 01.59.26.075978 AM +08:00

SQL> EXEC DBMS_SESSION.SET_NLS('NLS_TIMESTAMP_TZ_FORMAT', '''SYYYY-MM-DD HH24:MI:SS.FF TZR''')

PL/SQL procedure successfully completed.

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
---------------------------------------------------------------------------
 2010-03-20 02:00:27.000487 +08:00

再次查询V$NLS_PARAMETERS视图，检查修改后的参数设置：

SQL> SELECT * FROM V$NLS_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  CHINA
NLS_CURRENCY                   ￥
NLS_ISO_CURRENCY               CHINA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               ZHS16GBK
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        SYYYY-MM-DD HH24:MI:SS.FF TZR
NLS_DUAL_CURRENCY              ￥
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

19 rows selected.