ITPub博客

首页 > 数据库 > Oracle > NLS_DATE_LANGUAGE修改

NLS_DATE_LANGUAGE修改

原创 Oracle 作者:studyingeveryday 时间:2018-04-10 11:57:01 0 删除 编辑

一、 NLS_DATE_LANGUAGE

下面是临时修改nls_date_language参数为中文,以方便当前会话导入'08-12月-13 03.16.19.119000 下午'之类的数据。

报错ORA-01843: not a valid month解决

1、查看NLS_TIMESTAMP_FORMAT和NLS_DATE_LANGUAGE参数和SYSTIMESTAMP 数据格式

SQL> select * from v$nls_parameters;

PARAMETER                                                        VALUE

---------------------------------------------------------------- ----------------------------------------------------------------

NLS_LANGUAGE                                                     AMERICAN

NLS_TERRITORY                                                    AMERICA

NLS_CURRENCY                                                     $

NLS_ISO_CURRENCY                                                 AMERICA

NLS_NUMERIC_CHARACTERS                                           .,

NLS_CALENDAR                                                     GREGORIAN

NLS_DATE_FORMAT                                                  DD-MON-RR

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                                          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 rows selected

 

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP

--------------------------------------------------------------------------------

11-FEB-18 09.59.08.147761 AM +08:00

 

2、确定NLS_TIMESTAMP_FORMAT格式

如果参数NLS_TIMESTAMP_FORMAT格式不是DD-MON-RR HH.MI.SSXFF AM首先要调整该参数

     调整该参数的命令:alter session set NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH.MI.SS.FF8 AM';

    注:该命令只是当前会话有用。

3、确定NLS_DATE_LANGUAGE格式

查看NLS_DATE_LANGUAGE是SIMPLIFIED CHINESE还是AMERICA,如果是AMERICA需要修改成SIMPLIFIED CHINESE,

     才能显示中文上下午和月份,当前会话修改方法

SQL> ALTER SESSION SET nls_date_language='SIMPLIFIED CHINESE';

    注:该命令只是当前会话有用。

4、修改后参数

SQL> select * from v$nls_parameters;

/

PARAMETER                                                        VALUE

---------------------------------------------------------------- ----------------------------------------------------------------

NLS_LANGUAGE                                                     AMERICAN

NLS_TERRITORY                                                    AMERICA

NLS_CURRENCY                                                     $

NLS_ISO_CURRENCY                                                 AMERICA

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

 

5、查看类型:

SQL> select systimestamp from dual;

/

SYSTIMESTAMP

--------------------------------------------------------------------------------

11-2月 -18 09.38.29.190818 上午 +08:00

 

6、导入数据:

SQL> @

Importing table SYS_AREA...

 


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29668428/viewspace-2152753/,如需转载,请注明出处,否则将追究法律责任。

下一篇: DB2常用
请登录后发表评论 登录
全部评论
努力学习,并分享工作中的知识,欢迎阅读一起进步!

注册时间:2014-05-20

  • 博文量
    18
  • 访问量
    68263