[Error] ORA-01861: literal does not match format string 해결

들어가며

 2024년 12월 둘째 주 급하게 개편해야 하는 페이지가 생겨서 야근을 하면서 급하게 개발을 진행한 경험이 있습니다. 급하게 개발을 하다 보니 쿼리에서 날짜 관련 Format을 제대로 맞춰주지 못했고 때문에 해당 오류를 맞닥뜨렸습니다. 처음엔 'Local(Window)에선 분명 잘 되는데 왜 운영 서버(Linux)에서는 오류가 나지..?' 하고 당황스러움을 감출 수 없었는데 서버에서 오류 로그를 보니 어디서 실수했는지 딱 보였습니다. "코드는 잘못하지 않는다"는 대학교 선배님의 말씀이 어김없이 생각이 나는 날이었습니다:(

오류 화면

 

원인

 위 짧은 (서론)글에서 눈치를 채신 분이 있을 수도 있겠지만 ORA-01861은 로컬 OS와 운영 서버 OS의 차이에서 발생했습니다, 자세히 말하면 서버와 로컬의 OS LANG 설정이 달라서 로컬에선 괜찮았지만 운영에서는 오류가 발생한 것이었습니다. 차근차근 무슨 말인지 이해해 보는 시간을 가져보겠습니다. (오류를 해결하는 방법을 아는 것도 중요하지만 왜 발생했는지를 이해하고 해결하는 게 정말 중요하다고 생각합니다.)

 

 회사 쿼리를 공개할 수 없으니 비슷하게 쿼리를 만들어 봤습니다. 

 

 TRAINING_HIS 테이블에는 국가대표들이 차고 있는 워치로(?) 생체반응을 확인해 1시간 주기로 운동을 하고 있는지에 대한 히스토리가 적재된다고 가정하겠습니다. 그리고 코치는 한 국가대표의 가장 최근 날짜의 운동 데이터를 조회하고 싶습니다. (굳이 이런 스토리까진 필요 없다고 생각하지만.. 혹시라도 쿼리를 이해하고 싶은 사람이 있을 수도 있으니까..?)

 

 아래는 테이블 스키마라고 하기엔 좀 많이 부족한.. 표와 문제가 발생한 예시 쿼리입니다.

COLUMN	TYPE	COMMENT	데이터 예시
USER_ID	NUMBER	국가대표 ID	123
TR_DT	VARCHAR2(10)	운동 날짜	20250131
TR_TM	VARCHAR2(4)	운동 시간	1300
TRAINING_YN	VARCHAR2(1)	운동 여부	Y

 

WITH MAX_DATE AS (
	SELECT
    	TO_DATE(MAX(TR_DT), 'YYYYMMDD') AS MAX_DATE
    FROM
    	TRAINING_HIS
    WHERER
    	USER_ID = 123
)
SELECT
FROM
	MAX_DATE A
LEFT JOIN
	TRAINING_HIS B
ON
	A.MAX_DATE = B.TR_DT
    AND B.USER_ID = 123
ORDER BY
	TR_DT
    , TR_TM

 

 위 쿼리에서 오류가 발생한 부분은 ON절의 A.MAX_DATE = B.TR_DT 입니다. A.MAX_DATE는 WITH절로 인해 DATE형으로 변환이 되었는데 TR_DT는 VARCHAR2로 문자열이기 때문에 비교를 위해 묵시적 형변환이 일어난 거죠. 그렇다면 왜 묵시적 형변환이 로컬에서는 가능했지만 운영 서버에서는 오류가 발생했을까요? 

 

 오라클에는 초기화 매개 변수들 중에 NLS_DATE_FORMAT라는게 존재하고 오라클 묵시적 형변환을 할 때 사용합니다. 이때 NLS_DATE_FORMAT은 OS계정의 환경변수인 NLS_LANG에 영향을 받습니다. 만약 누군가 임의적으로 바꾸지 않았다면 Linux의 LANG은 en_US.UTF-8일 것이고 LNS_DATE_FORMAT=DD-MON-RR이 될 것입니다. Window의 경우 LNS_LANG=ko_KR.UTF-8이고 LNS_DATE_FORMAT=RR/MM/DD일 것입니다.

 

 때문에 ON 절의  A.MAX_DATE = B.TR_DT 에 데이터를 대입해 보면 20250131 = '20250131' ( DATE = VARCHAR )가 되고 여기서 VARCHAR인 '20250131'이 DATE로 묵시적 형변환이 되는 과정에서 DD-MON-RR 형식에 맞지 않아 오류가 나는 것입니다. 

 그렇다면 여기서 제가 든 생각.. 로컬의 NLS_DATE_FORMAT도 문자열과 일치하지 않으니 로컬에서도 오류가 나야 하는 거 아닌가..? 많은 분들이 쓰신 해당 오류 글을 읽어봤을 때 저와 같은 의문을 가지신 분은 안 계시더라고요..? 이때부터 '내가 이해를 잘 못하나..', '내가 뭘 잘못 알고 있는 건가..?', ' 잘못 이해한 건가..' 하고 심각하게 고민하면서 서칭을 엄청 했습니다..

 

 정말 심각하게 고민해 보고 찾아보다가 Chat GPT에게서 답을 얻을 수 있었습니다. Oracle이 '20250131'과 같은 문자열은 'yyyymmdd'로 처리할 수 있는 유연성이 내장되어 있어 유효한 날짜로 자동 해석할 수 있기 때문에 로컬에서는 오류를 던지지 않는다. 하지만 서버의 NLS_DATE_FORMAT인 DD-MON-RR의 경우 월을 Jan, Feb, Mar와 같이 문자열로 해석해야하는데 그러지 못해 오류를 던졌다. 라고 답을 해주었습니다. ( Chat GPT가 100% 정답만을 말하고 그러진 않지만 이번엔 믿어볼게요, 난 낡고 지쳤으니까..)

서버에서 오류가 난 이유 (feat. Chat GPT)

 

해결 방법

 해결 방법을 알아보면 크게 두 개의 방법이 있습니다. 1) 서버의 LANG 설정을 동일하게 맞춰주던가 2) 명시적으로 프로그래밍을 하는 것입니다. 서버의 LANG을 동일하게 맞추기에는 음.. 어떤 후폭풍이 올지 모르니 간단하게 쿼리에 명시를 하는 방향을 선택했습니다. :) 이게 더 맞는 해결 방법이라고 생각합니다!

 

WITH MAX_DATE AS (
	SELECT
    	TO_CHAR(TO_DATE(MAX(TR_DT), 'YYYYMMDD'), 'YYYYMMDD') AS MAX_DATE
    FROM
    	TRAINING_HIS
    WHERER
    	USER_ID = 123
)
SELECT
FROM
	MAX_DATE A
LEFT JOIN
	TRAINING_HIS B
ON
	A.MAX_DATE = B.TR_DT
    AND B.USER_ID = 123
ORDER BY
	TR_DT
    , TR_TM

 

 저는 WITH절에서 DATE로 변환되는 MAX_DATE를 문자열로 한번 더 바꿔주는 방법을 택했습니다. 이렇게 되면 ON절에서 비교를 할 때 이전과 다르게 문자열과 문자열의 비교라 가능해지는 원리입니다. 위와 같은 쿼리가 아니라 ON절에서 TO_CHAR(A.MAX_DATE, 'YYYYMMDD') = B.TR_DT 로 바꿔줘도 가능합니다! 

 

느낀 점

 사실 이 오류는 2024년 12월이 아니라 10월이 초면이었습니다만.. 또 똑같은 실수를 반복했으니 다음엔 절대 보지 말자는 의미로 이 글을 쓰게 되었습니다.ㅎㅎ

 

 오류를 해결하는 데에는 시간이 별로 들지 않지만 왜 발생했는지, 이렇게 하면 왜 해결되는 건지를 이해하기 위해 공부하다 보면 시간이 많이 소요되고 더불어  '난 많이 부족하구나'라는 생각을 정말 많이 하게 되는 거 같습니다. 그냥 개발을 하면서 당연히 서로 다른 타입을 비교하기 위해 Format을 맞춰야 하니까 사용했던 TO_DATE, TO_CHAR를 사용해왔는데 이번 기회에 왜 사용해야만 하는지에 대해서 생각하는 시간을 가지게 되었습니다. 역시 개발자는 프로그래밍을 할 때 'How'도 중요하지만 'Why'가 더 중요한 거 같다는 깨달음을 얻으며 이 글은 여기서 맞히도록 하겠습니다.

 

 다음 글은 음.. 대학교 친구들과 진행하고 있는 토이프로젝트가 있는데 제가 서버 구축을 담당하고 있기 때문에 그거에 대한 설명? 정리? 가 되지 않을까 싶습니다. 감사합니다! 만약 잘못 된 내용이 있다면 알려주시면 감사합니다:D