解决ORA-01861 literal does not match format string

最新推荐文章于 2022-02-23 15:05:28 发布

weixin_34362991

最新推荐文章于 2022-02-23 15:05:28 发布

阅读量326

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/u/2822116/blog/712098

版权

2019独角兽企业重金招聘Python工程师标准>>>

数据脚本里面的时间是'2010-12-12'，结果导进去说

ORA-01861 literal does not match format string

数据库里面的字段是date型的，然后linux安装的oracel时间格式，根据：

select * from nls_database_parameters;

SQL> select * from nls_database_parameters;

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CURRENCY
$

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICA

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET
ZHS32GB18030

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CALENDAR
GREGORIAN

NLS_DATE_FORMAT
DD-MON-RR

NLS_DATE_LANGUAGE
AMERICAN

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_SORT
BINARY

NLS_TIME_FORMAT
HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR

NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR

NLS_DUAL_CURRENCY
$

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_COMP
BINARY

NLS_LENGTH_SEMANTICS
BYTE

NLS_NCHAR_CONV_EXCP
FALSE

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_RDBMS_VERSION
11.2.0.4.0

NLS_TIME_FORMAT就是日期格式，这里为 HH.MI.SSXFF AM ，

NLS_LANGUAGE 为 AMERICA ，字符集也不对，导致生成的一堆数据库表有很多乱码。

在当前用户下修改 .bash_profile，增加三个：

export LANG=C
export NLS_DATE_FORMAT=YYYY-MM-DD
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS32GB18030"

[test@rhel675771 bb]$

Last login: Thu Jul 14 20:09:17 2016 from 10.187.191.55

test

[test@rhel675771 ~]$

[test@rhel675771 ~]$ test

[test@rhel675771 ~]$ pwd
/datafs/fs

[test@rhel675771 ~]$ vi .bash_profile;

PATH=$PATH:$HOME/bin

export PATH

export PATH

export LANG=C
export NLS_DATE_FORMAT=YYYY-MM-DD
export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS32GB18030"

保存修改后退出，另起一个会话，登进SQL

[test@rhel675771 ~]$ test
[test@rhel675771 ~]$ pwd
/datafs/fs
[test@rhel675771 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on 星期五 7月 15 08:45:16 2016

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS32GB18030

字符集已经改变，数据也可以成功导入。

转载于:https://my.oschina.net/u/2822116/blog/712098