ORACLE ORA-01861: literal does not match format string

2020.03.12 16:43

졸리운_곰 조회 수:1417

ORA-01861: literal does not match format string

ORA-01861: literal does not match format string

ORA-01861: 리터럴이 형식 문자열과 일치하지 않음

 

가끔 개발부서에서 이런 에러로 질문을 합니다.

JDBC로 접근하는 일부 웹서버에서는 발생 안하는데,  특정 웹서버에서만 발생한다면서 궁금증을 호소합니다.

 

OS의 LANG 설정이 달라서 문자열을 묵시적으로 날짜로 변환을 못해서 발생하는 이슈로

보통 어플리케이션 서버를 교체하고 나서 LANG에 대해 설정을 하지 않아서 이런 이슈들이 종종 발생하곤 합니다.

하지만 모든 어플리케이션 서버의 LANG 설정을 맞추는 것은 좋은 해결 방법이 아닙니다.

 

가장 좋은 해결 방법은 to_date(날짜값,'포멧')을 명시적으로 프로그래밍하는 것입니다.

그래도 꼭 묵시적으로 변환하고 싶으면 모든 클라이언트의 환경변수 LANG or NLS_LANG 설정을 맞춰야합니다.

 

 

 

클라이언트 세션레벨의 NLS_DATE_FORMAT 설정을 

SQL*PLUS로 확인을 해보겠습니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
--임시테이블을 만들고
SQL> create table t1(c1 date);
 
Table created.
 
--현재 세션의 NLS_DATE_FORMAT을 확인해봅니다.
SQL> select sys_context('USERENV','NLS_DATE_FORMAT') ndf from dual;
 
NDF
--------------------------------------------------------------------------------
DD-MON-RR
 
--위 포멧에 맞게 데이터를 넣어주면 자동으로 형변환이 되어 DATE값이 입력이됩니다.
SQL> insert into t1 values('25-APR-17');
 
1 row created.
 
--하지만 포멧이 달라지면 ORA-01861 에러가 발생하게 됩니다.
SQL> insert into t1 values('2016-04-25');
insert into t1 values('2016-04-25')
                      *
ERROR at line 1:
ORA-01861: literal does not match format string
 
--하지만 세션레벨에서 nls_date_format을 변경해주면 입력이 가능해집니다.
SQL> alter session set nls_date_format='YYYY-MM-DD';
 
Session altered.
 
SQL> insert into t1 values('2016-04-25');
 
1 row created.
 
 
  

 

SQL*PLUS를 실행하는 OS계정의 환경변수인 NLS_LANG에 영향을 받는데요.

export NLS_LANG=AMERICAN_AMERICA.UTF8

로 되어 있다면 NLS_DATE_FORMAT이 미국기준으로 DD-MON-RR 가 됩니다.

export NLS_LANG=Korean_Korea.UTF8

로 되어 있따면 NLS_DATE_FORMAT이 대한민국기준으로 RR/MM/DD 가 됩니다.

 

그럼 JDBC THIN CLIENT를 사용하는 JDBC프로그램은 어떨까요 ?

경축! 아무것도 안하여 에스천사게임즈가 새로운 모습으로 재오픈 하였습니다.
어린이용이며, 설치가 필요없는 브라우저 게임입니다.
https://s1004games.com

일반적으로 어플리케이션에서는 NLS_DATE_FORMAT 을 셋팅해주지 않습니다.

 

메뉴얼을 보면 NLS_DATE_FORMAT의 디폴트값은 NLS_TERRITORY에 의해 결정됨을 알  수 있습니다.

http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams147.htm#REFRN10119

그리고 NLS_TERRITORY의 디폴트값은 OS dependent임을 알 수 있습니다. 

즉, OS 환경변수인 NLS_LANG 설정에 영향을 받습니다.

http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams156.htm#REFRN10128

참고 >

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch3globenv.htm#NLSPG189

결국 OS의 설정에 따라갈 수 밖에 없는 겁니다. 

테스트를 해보면 아래와 같이 NLS_DATE_FORMAT이 달라짐을 알 수 있습니다.

  • Windows 에서는 RR-MM-DD
  • Linux에서 LANG=ko_kr.utf-8 일때는 RR-MM-DD
  • Linux에서 LANG=en_us.utf-8 일때는 DD-MON-RR

그럼 테스트를 해보겠습니다.

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
public class OracleToDate {
    public static void main(String args[]) {
        try {
            String driver="oracle.jdbc.OracleDriver";
            Class.forName(driver);
            Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:sid", "username", "password");
            Statement stmt = conn.createStatement();
 
            ResultSet rsFormat = stmt.executeQuery("select VALUE from v$nls_parameters where parameter='NLS_DATE_FORMAT'");
            while ( rsFormat.next() ) {
              System.out.println("NLS_DATE_FORMAT:" + rsFormat.getString(1));
            }
            rsFormat.close();
 
            ResultSet rsDateFormat = stmt.executeQuery("select TO_DATE('2016-04-25','YYYY-MM-DD') from DUAL");
            while ( rsDateFormat.next() ) {
              System.out.println("NLS_DATE_FORMAT:" + rsDateFormat.getString(1));
            }
            rsDateFormat.close();
 
            ResultSet rsDate = stmt.executeQuery("select to_char(TO_DATE('2016-04-25','YYYY-MM-DD')) from DUAL");
 
            while ( rsDate.next() ) {
              System.out.println("auto to_char:" + rsDate.getString(1));
            }
 
            rsDate.close();
            stmt.close();
            conn.close();
 
        } catch (Exception e) {
            System.out.println(e);
            e.printStackTrace();
        }
    }
}
 
  

 

먼저 Windows에서 Eclipse로 테스트해봤습니다.

1
2
3
4
NLS_DATE_FORMAT:RR/MM/DD
NLS_DATE_FORMAT:2016-04-25 00:00:00
auto to_char:16/04/25
 
 
  

 

다음은 Linux에서 테스트해봤습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
---------------------------------
$ echo $LANG
en_US.UTF-8
$ javac OracleToDate.java
$ java OracleToDate
NLS_DATE_FORMAT:DD-MON-RR
NLS_DATE_FORMAT:2016-04-25 00:00:00.0
auto to_char:25-APR-16
 
---------------------------------
$ export LANG=ko_KR.UTF-8
$ echo $LANG
ko_KR.UTF-8
$ java OracleToDate
NLS_DATE_FORMAT:RR/MM/DD
NLS_DATE_FORMAT:2016-04-25 00:00:00.0
auto to_char:16/04/25
---------------------------------
  

결국 아무런 설정이 없다면 OS의 LANG에 의해 결정되는 것을 알 수 있습니다.

 

참고로

JDBC Thin Driver는 JVM의 user.language 프로퍼티의 JAVA Locale에서 언어와 지역설정을 읽습니다.

날짜형식은 지역설정에 따라서 결정됩니다. 

 

[출처] https://m.blog.naver.com/PostView.nhn?blogId=hanccii&logNo=220992089311&proxyReferer=http%3A%2F%2Fwww.google.com%2Furl%3Fsa%3Dt%26rct%3Dj%26q%3D%26esrc%3Ds%26source%3Dweb%26cd%3D2%26ved%3D2ahUKEwiymZmNt5ToAhWbyIsBHfRDDLQQFjABegQIARAB%26url%3Dhttp%253A%252F%252Fm.blog.naver.com%252Fhanccii%252F220992089311%26usg%3DAOvVaw3lLH_EwjChDcSm5FbtHPcY

 

 

본 웹사이트는 광고를 포함하고 있습니다.
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
이 게시물을
목록
목록
쓰기
태그
대표 김성준 주소 : 경기 용인 분당수지 U타워 등록번호 : 142-07-27414
통신판매업 신고 : 제2012-용인수지-0185호 출판업 신고 : 수지구청 제 123호 개인정보보호최고책임자 : 김성준 sjkim70@stechstar.com
대표전화 : 010-4589-2193 [fax] 02-6280-1294 COPYRIGHT(C) stechstar.com ALL RIGHTS RESERVED