[DB] 오라클 대량 문자열 처리 - CLOB, LONG (ORA-06502, ORA-01704)

 

사건 발단 : 

오라클 프로시저 내에서 LONG 형태로 메일의 HTML 본문을 저장함.
본문의 길이가 일정 길이 이상일 경우, 아래와 같은 에러가 나면서 Exception - USER_EXCEPTION_TABLE에 저장됨.
ORA-06502 수치 또는 값 오류 : 문자열 버퍼가 너무 작습니다. (numericor value error: character string buffer too small)
 

---

 

원인 분석 : 

 
다른 사례 ORA-06502 를 찾아 봤을 땐, 
DBMS_LOB.SUBSTR, DMBS_LOB.GETLENGTH 등을 Return 받을 때 크기가 클 때 문제인 경우가 많았다.
 
https://kr98gyeongim.tistory.com/4

[개발시 에러] ORA-06502:PL/SQL:수치 또는 값 오류:문자열 버퍼가 너무 작습니다.

 
 
일반적으로 선언된 변수의 크기보다 (varchar2 의 경우 SQL에서는 최대 4000Byte) 더 큰 값을 대입했을 때 생기는 오류
 
하지만 MAIL_CONTENTS(본문)을 담는 변수는 Long (이후 CLOB으로도 변경)으로
변경하여 Insert 시 에 동일하게 ORA-06502 메시지가 발생되었다.

* Insert 시 4000자 이상 한번에 넣는 건 안된다고 함.
DBMS tool 에서 Insert 문 실행했을 때,  ORA-01704 : 문자열이 너무 깁니다 라는 메시지 발생됨
PL/SQL 프로시저 호출 시에는 메시지가 다른 지는 잘 모르겠음..

 

---

 

해결방안

 
: 근본적으로 긴 문자열을 한번에 처리하지 않도록 한다. 
:  TO_CLOB (4000자 이하의 문자열) || TO_CLOB (4000자 이하의 문자열) 으로 짤라서 올리면 Insert 정상적으로 동작함
 
4000자 이상의 CLOB Insert 는 아래 스택오버플로우 내 답변을 참고하였음.
https://stackoverflow.com/questions/18394691/oracle-clob-cant-insert-beyond-4000-characters

Oracle CLOB can't insert beyond 4000 characters?

INSERT INTO "TABLE 명" 
(CLOB_COLUMN) VALUES ( TO_CLOB("4000자 미만 문자") || TO_CLOB("4000자 미만 문자") );

 

CREATE TABLE don (x clob);


DECLARE 
 l_clob clob;
BEGIN
  FOR i IN 1..10
  LOOP
    INSERT INTO don (x) VALUES (empty_clob()) --Insert an "empty clob" (not insert null)
    RETURNING x INTO l_clob;

    -- Now we can append content to clob (create a 400,000 bytes clob)
    FOR i IN 1..100
    LOOP
      dbms_lob.append(l_clob, rpad ('*',4000,'*'));
      --dbms_lob.append(l_clob, 'string chunk to be inserted (maximum 4000 characters at a time)');
    END LOOP;
  END LOOP;
END;

 
 
* 오라클 Type - CLOB vs LONG 비교

	CLOB	LONG
저장용량	버전에 따라 다르나 일반적으로 4GB	최대 2GB 크기 문자 저장
저장방식	LOB 세그먼트로 저장되며, 테이블과 별도	테이블 내 저장
작업가능여부	다른 테이블과 조인 등의 연산 가능	다른 테이블과 조인 불가, 일부 연산에서 사용하지 못함
성능	Long Type 대비 더 빠름