Oracle CLOB 컬럼 Select 시 주의사항 (ORA-06502)

Oracle CLOB 컬럼은 Oracle 버전 10g 이후로 지원되는 LOB 타입의 종류 중 하나로, 문자열이나 이미지 데이터를 4GB까지 저장할 수 있습니다.

어느 날 화면에서 처리한 데이터가 DB에 정상적으로 들어가있는지 확인하기 위해 Orange에서 CLOB 컬럼을 조회해봤습니다.

서비스 Log나 화면 IO를 보면 데이터를 정상적으로 DB에 넣는 것 처럼 보이는데, Orange에서 Select로 조회해보면 일부가 깨져서 보이거나 데이터가 잘리는 현상이 있었습니다.

[현상]

입력한 값 : 안녕하세요? 테스트 데이터입니다.

Orange에서 조회한 CLOB 컬럼의 값 : 안녕하세요? 테스트

[원인]

프로그램 오류라 생각하고 몇 시간 동안 로그도 뒤져보고, 비슷한 유형의 다른 프로그램도 테스트해보고 하다가 원인을 결국 발견했습니다.

CLOB 컬럼 특성 상 4GB 용량의 데이터를 저장할 수 있기 때문에, 최대 4GB의 데이터가 존재할 수 있는 컬럼을 쿼리로 조회할 경우 성능적인 면에서 큰 부하가 걸릴 수 있습니다. 

1000 rows 만 있어도.. 최대 1000*4GB 인 셈이지요.

때문에 CLOB 컬럼 조회 시 부하 발생의 위험을 최소화하고 효율적인 쿼리를 위해서 컬럼이 지니고 있는 전체 데이터가 아닌 로케이터만 조회해옵니다.

즉, CLOB 컬럼 조회 시 부하 발생의 위험을 줄이고 쿼리 효율을 위해 데이터의 일부분만 보여집니다.

CLOB 컬럼의 전체 값을 조회하고 싶을 경우, 아래와 같이 dbms_lob 을 이용하도록 합시다!

(Orange툴을 사용하는 경우라면 dbms_lob 을 사용하지 않아도 Text Output 탭에서 전체값을 확인할 수 있습니다)

[이용 예시]

SELECT dbms_lob.substr(컬럼, dbms_lob.getlength(컬럼))
  FROM 테이블
 WHERE  PK컬럼 = 'PK값'; -- 반드시 한 건 씩 조회해야한다.

위와 같은 현상은 외부 시스템과 인터페이스 연동 테스트를 하던 중 발견한 것입니다. 

현재 개발하고 있는 시스템에서는 EAI에 인터페이스를 전송한 후, 연동된 내용과 결과를 이력으로 관리하는 테이블이 있습니다.

이상하게도 EAI와 외부시스템에서는 정상적인 데이터를 받은 것을 확인하였는데, 인터페이스 연동 이력을 관리하는 내부 테이블에만 데이터가 깨져서 들어가는 겁니다.

프로그램 버그인 줄 알고 오전 동안 헤맸는데 Oracle CLOB 컬럼의 특성을 몰라서 생긴 삽질이었네요.

역시 모르는게 있을 때는 주변에 계신 똑똑이들에게 여쭤보는게 최고인듯..

---

(2021.01.24 내용추가) ORA-06502, 문자열 버퍼가 작다는 오류 발생

[오류]

ORA-06502: "Numeric Or Value Error: Character String Buffer Too Small"

[원인]

dbms_lob.substr() 함수의 return값은 varchar2 타입인데, varchar2 타입에 담을 수 있는 데이터의 최대 길이가 정해져있습니다.

varchar2 타입의 길이는 보통은 4,000 bytes 이며, PL/SQL에서는 32,767 bytes 까지 가능합니다.

clob으로 조회하는 데이터가 varchar2 타입에 담을 수 없는 경우 버퍼가 적다는 오류가 발생하게 됩니다.

이 오류는 오래전부터 존재하던 이슈로, 보통 두 가지 방식으로 접근하여 해결하는 것 같습니다.

[해결방법]

1. 최대 버퍼의 사이즈를 늘린다.

2. 4,000 bytes 내로 데이터를 잘라서 조회한다. (한글의 경우 글자가 깨질 위험이 있음)

참고 링크

- (한글) 오류 원인과 해결방법 안내 : https://tomining.tistory.com/95

- 4,000 bytes별로 잘라서 조회 : https://stackoverflow.com/questions/30507997/migration-script-with-dbms-lob-substr-gets-character-string-buffer-too-small

- dbms_lob.substr() 함수 설명 : https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#i999349