ORACLE使用INSERT INTO 插入日期并TO_DATE转换的问题

54 阅读 0 评论 36 点赞

我是靠谱客的博主任性外套，最近开发中收集的这篇文章主要介绍ORACLE使用INSERT INTO 插入日期并TO_DATE转换的问题，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

遇到了一个问题，在使用Sql developer时向表中INSERT 日期格式的数据时报出了错误：

into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10)
SELECT 1 FROM dual;
错误报告 -
ORA-01843: 无效的月份

提示的时无效的月份，我查看了一下关于我要插入的这个表的数据类型发现数据类型和插入的数据方式是没有错误的：

SQL> DESC EMP;

名称       空值? 类型           
-------- --- ------------ 
EMPNO        NUMBER(4)    
ENAME        VARCHAR2(10) 
JOB          VARCHAR2(9)  
MGR          NUMBER(4)    
HIREDATE     DATE         
SAL          NUMBER(7,2)  
COMM         NUMBER(7,2)  
DEPTNO       NUMBER(2)

查看了一下网上说的貌似是因为使用的中文客户端环境，
查看一下所使用的语言参数：

SQL> SELECT * FROM v$parameter WHERE name = 'nls_date_language';

       NUM NAME                                                                                   TYPE
---------- -------------------------------------------------------------------------------- ----------
VALUE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DISPLAY_VALUE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEFAULT_VALUE                                                                                                                                                                                                                                                   ISDEFAULT ISSES ISSYS_MOD ISPDB ISINS ISMODIFIED ISADJ ISDEP ISBAS DESCRIPTION                                                                                                                                                                                                                                                     UPDATE_COMMENT                                                                                                                                                                                                                                                        HASH     CON_ID
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ----- --------- ----- ----- ---------- ----- ----- ----- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
       329 nls_date_language                                                                         2 
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
NONE

发现它没有显示，是空的。
更改掉语言环境：

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';

Session已变更。

再查看环境值：

SQL> SELECT * FROM v$parameter WHERE name = 'nls_date_language';

       NUM NAME                                                                                   TYPE
---------- -------------------------------------------------------------------------------- ----------
VALUE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DISPLAY_VALUE
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEFAULT_VALUE                                                                                                                                                                                                                                                   ISDEFAULT ISSES ISSYS_MOD ISPDB ISINS ISMODIFIED ISADJ ISDEP ISBAS DESCRIPTION                                                                                                                                                                                                                                                     UPDATE_COMMENT                                                                                                                                                                                                                                                        HASH     CON_ID
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------- ----- --------- ----- ----- ---------- ----- ----- ----- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ----------
       329 nls_date_language                                                                         2 
AMERICAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
AMERICAN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
NONE

显示了AMERICAN参数环境。
我们此时再插入数据看一下：

into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10)
SELECT 1 FROM du 14   15  al;
Insert ALL into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20)
                                                                                                                   *
第 1 行出现错误:
ORA-01843: 无效的月份

还是依然会显示无效的月份。那么我试一下单独插入修改一下月份，把中间的MON从英文换成中文试一下：

SQL> Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-12月-80','DD-MON-RR'),800,null,20);

1行已插入。

将’DECEMBER’缩写的英文换成了’12月’之后试着插入一行提示成功了，代表我插入中文是没有问题的，所以还是和语言有关。那么继续尝试修改模式：

SQL> alter session set NLS_DATE_FORMAT='DD-MON-RR';

Session已变更。

再尝试插入数据：

Insert into SCOTT.EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20)
错误报告 -
ORA-01843: 无效的月份