[Oracle] MERGE INTO ~ 구문에서의 CLOB 타입 INSERT 시 에러

 

❌ 에러 발생 SQL

<selectKey keyProperty="sortNo" resultType="int" order="BEFORE">
   			SELECT NVL(MAX(SORT_NO), 0) + 1
   				FROM BOARD
   			WHERE BOARD_TYPE = #{boardType}	
</selectKey>
   		
   		MERGE INTO BOARD A
   		USING (
   				 	SELECT #{id} 				ID
   				 		 , #{boardType} 		BOARD_TYPE
   				 		 , #{titleKo} 			TITLE_KO
   				 		 , #{contentKo} 		CONTENT_KO
   				 		 , #{titleEn} 			TITLE_EN
   				 		 , #{contentEn} 		CONTENT_EN
   				 		 , #{sortNo} 			SORT_NO
   				 		 , #{useYn} 			USE_YN
   				 		 , #{popupYn} 			POPUP_YN
   				 		 , #{popupFr} 			POPUP_FR
   				 		 , #{popupTo} 			POPUP_TO
   				 		 , #{__param.userId}  	CREATE_USER
   						 , CURRENT_DATE    		CREATE_DATE
   				 		 , #{__param.userId} 	UPDATE_USER
   				 		 , CURRENT_DATE 		UPDATE_DATE
   				 	 FROM DUAL
   			  ) B
   		  ON (A.ID = B.ID)
   		WHEN MATCHED THEN
   			 UPDATE
   			 	SET TITLE_KO		= B.TITLE_KO
   			 	  , CONTENT_KO		= B.CONTENT_KO
   			 	  , TITLE_EN		= B.TITLE_EN
   			 	  , CONTENT_EN		= B.CONTENT_EN
   			 	  , POPUP_YN		= B.POPUP_YN
   			 	  , POPUP_FR		= B.POPUP_FR
   			 	  , POPUP_TO		= B.POPUP_TO
   			 	  , USE_YN			= B.USE_YN
   			 	  , UPDATE_USER		= B.UPDATE_USER
   			 	  , UPDATE_DATE		= B.UPDATE_DATE
   		WHEN NOT MATCHED THEN
   			 INSERT (ID, BOARD_TYPE, TITLE_KO, CONTENT_KO, TITLE_EN 
   			 		,CONTENT_EN, SORT_NO, USE_YN, POPUP_YN, POPUP_FR
   			 		,POPUP_TO, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE
   			 		)
			 VALUES (B.ID, B.BOARD_TYPE, B.TITLE_KO, B.CONTENT_KO, B.TITLE_EN 
   			 		,B.CONTENT_EN, B.SORT_NO, B.USE_YN, B.POPUP_YN, B.POPUP_FR
   			 		,B.POPUP_TO, B.CREATE_USER, B.CREATE_DATE, B.UPDATE_USER, B.UPDATE_DATE
   			 		)

공지사항, 이용 안내 등 게시판 형태를 가지고 있는 테이블에
데이터를 INSERT하는 평범한 SQL이다.

Mybatis + ORACLE DB를 사용하고 있다.

다만 문제가 됐던 부분은, 현재 게시판에서 글을 작성할 때 사용하는
스마트에디터에서 이미지 업로드 시 발생했는데,
크기가 큰 이미지를 업로드 할 경우 에러가 발생하는 점이었다.

🗄️ 데이터 타입

BOARD 테이블의 구조는 이렇다.

첨부파일 업로드 시 따로 업로드 되지 않고, BASE64로 인코딩 되어 문자열 형태로 DB에 업로드 된다.

따라서 크기가 큰 이미지는 엄청나게 긴 문자열이 될 수 밖에 없다.

 

🐞 첫번째 에러

"ORA-01461: LONG 값은 LONG 열에 삽입할 때만 바인드할 수 있습니다."
"ORA-01461: can bind a LONG value only for insert into a LONG column"

이미지가 작을 경우는 들어가는데, 이미지가 클 경우는 안 들어간다?

Base64 인코딩 후 문자열 길이의 문제라고 추정했다.

검색해보니 여러 케이스가 있다고 하는데,

1. JDBC 버그, 최신버전으로 업데이트 시 문제 해결(글에서는 11.x 이상)

2. 데이터 타입이 VARCHAR2인 컬럼에 4000byte 이상의 값을 넣으려 할 경우
내 경우는 CLOB타입이라 제외.

1. INSERT DATA에 한글이 포함되어있는 경우

JDBC는 12.x버전을 사용 중이어서 제외, INSERT DATA에 한글을 제외하고 테스트해봐도 동일한 문제가 발생해서 제외.

어느 케이스로도 해결이 되지 않아서
문제로 추정되는 컬럼인 CONTENT_KO 컬럼과 CONTENT_EN 컬럼쪽 INSERT를 주석처리 하니 에러 없이 처리가 됐다.

CONTENT_ 컬럼의 값이 너무 커서 생기는 문제라고 확신했다.

🐞 두번째 에러

MERGE INTO BOARD A
   		USING (SELECT #{id} ID FROM DUAL) B
   		ON (A.ID = B.ID)
   		WHEN MATCHED THEN
   			 UPDATE
   			 	SET TITLE_KO		= #{titleKo}
   			 	  , TITLE_EN		= #{titleEn}
   			 	  , POPUP_YN		= #{popupYn}
   			 	  , POPUP_FR		= #{popupFr}
   			 	  , POPUP_TO		= #{popupTo}
   			 	  , USE_YN			= #{useYn}
   			 	  , UPDATE_USER		= #{__param.userId}
   			 	  , UPDATE_DATE		= CURRENT_DATE
	   			  , CONTENT_KO		= #{contentKo}
	   			  , CONTENT_EN		= #{contentEn}
   		WHEN NOT MATCHED THEN
   			 INSERT (ID, BOARD_TYPE, TITLE_KO, TITLE_EN
   			 		,SORT_NO, USE_YN, POPUP_YN, POPUP_FR
   			 		,POPUP_TO, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, CONTENT_KO,CONTENT_EN
   			 		)
			 VALUES (#{id}, #{boardType}, #{titleKo}, #{titleEn}
   			 		,#{sortNo}, #{useYn}, #{popupYn}, #{popupFr}
   			 		,#{popupTo}, #{__param.userId}, CURRENT_DATE, #{__param.userId}, CURRENT_DATE, #{contentKo}, #{contentEn}
   			 		)
   

UPDATE문의 WHERE절에 서브쿼리가 들어갈 경우 생기는 문제라는 글을 봐서
USING절의 CONTENT_ 컬럼을 서브쿼리로 이용하는 부분을 빼주었다.
UPSERT에 필요한 PK값인 ID만 USING절에 이용하고,
나머지는 가져온 파라미터를 이용하게 변경했다.

그리고 발생한 에러.

"ORA-03146: invalid buffer length for TTC field"
"ORA-03146: TTC 필드에 대한 버퍼 길이가 부적합합니다"

큰 데이터가 들어가는 컬럼을 뒤쪽으로 보내면 해결이 된다는 글을 보아서
적용해봤지만 소용이 없었다. 어떨때는 되고, 어떨때는 안되는 현상도 생겼다.

 

✔️ 최종수정

<selectKey keyProperty="sortNo" resultType="int" order="BEFORE">
   			SELECT NVL(MAX(SORT_NO), 0) + 1
   				FROM BOARD
   			WHERE BOARD_TYPE = #{boardType}	
   		</selectKey>


   		MERGE INTO BOARD A
   		USING (SELECT #{id} ID FROM DUAL) B
   		ON (A.ID = B.ID)
   		WHEN MATCHED THEN
   			 UPDATE
   			 	SET TITLE_KO		= #{titleKo}
   			 	  , TITLE_EN		= #{titleEn}
   			 	  , POPUP_YN		= #{popupYn}
   			 	  , POPUP_FR		= #{popupFr}
   			 	  , POPUP_TO		= #{popupTo}
   			 	  , USE_YN			= #{useYn}
   			 	  , UPDATE_USER		= #{__param.userId}
   			 	  , UPDATE_DATE		= CURRENT_DATE
	   			  , CONTENT_KO		= EMPTY_CLOB()
	   			  , CONTENT_EN		= EMPTY_CLOB()
   		WHEN NOT MATCHED THEN
   			 INSERT (ID, BOARD_TYPE, TITLE_KO, TITLE_EN
   			 		,SORT_NO, USE_YN, POPUP_YN, POPUP_FR
   			 		,POPUP_TO, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE, CONTENT_KO,CONTENT_EN
   			 		)
			 VALUES (#{id}, #{boardType}, #{titleKo}, #{titleEn}
   			 		,#{sortNo}, #{useYn}, #{popupYn}, #{popupFr}
   			 		,#{popupTo}, #{__param.userId}, CURRENT_DATE, #{__param.userId}, CURRENT_DATE, EMPTY_CLOB(), EMPTY_CLOB()
   			 		)
                    

버퍼 공간이 부족하다? 그럼 가장 크기를 많이 차지는 CONTENT_만 INSERT를 분리시키자고 생각했다.
UPSERT 단계에서 처음에 들어가는 데이터는 EMPTY_CLOB()으로 빈 CLOB 데이터를 넣어주고, 해당 row의 PK값으로 CONTENT_만 UPDATE 시켜주기로 했다.

UPDATE BOARD A
   SET CONTENT_KO = #{contentKo}
   	 , CONTENT_EN = #{contentEn}
 WHERE A.ID = #{id}
 

이제 이미지는 문제 없이 INSERT 되는것을 확인 했으나,
전임자가 용량 제한 등을 만들어놓지 않아서 그 부분은 작업이 필요하다.
굳이 쿼리를 분리시켜서 DAO 호출을 2번하게 만든 점은 아쉽지만
해결이 된 걸 다행으로 생각해야겠다.

 

https://velog.io/@kimbad1992/Oracle-MERGE-INTO-%EA%B5%AC%EB%AC%B8%EC%97%90%EC%84%9C%EC%9D%98-CLOB-%ED%83%80%EC%9E%85-INSERT-%EC%8B%9C-%EC%97%90%EB%9F%AC

[Oracle] MERGE INTO ~ 구문에서의 CLOB 타입 INSERT 시 에러