- 전체
- Sample DB
- database modeling
- [표준 SQL] Standard SQL
- G-SQL
- 10-Min
- ORACLE
- MS SQLserver
- MySQL
- SQLite
- postgreSQL
- 데이터아키텍처전문가 - 국가공인자격
- 데이터 분석 전문가 [ADP]
- [국가공인] SQL 개발자/전문가
- NoSQL
- hadoop
- hadoop eco system
- big data (빅데이터)
- stat(통계) R 언어
- XML DB & XQuery
- spark
- DataBase Tool
- 데이터분석 & 데이터사이언스
- Engineer Quality Management
- [기계학습] machine learning
- 데이터 수집 및 전처리
- 국가기술자격 빅데이터분석기사
- 암호화폐 (비트코인, cryptocurrency, bitcoin)
ORACLE ORA-01861: literal does not match format string
2020.03.12 16:43
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프로그램은 어떨까요 ?
일반적으로 어플리케이션에서는 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
광고 클릭에서 발생하는 수익금은 모두 웹사이트 서버의 유지 및 관리, 그리고 기술 콘텐츠 향상을 위해 쓰여집니다.
