CLOB 4000 이상 SELECT - CLOB 4000 isang SELECT

오라클에서는 문자를 저장하는 VARCHAR2가 최대 4000자까지만 지원되어, 그 이상의 데이터는 CLOB에 저장을 하였다.

그런데, 반대로 읽어올때도 문제가 되는데, 읽어올때도 4000자 이상은 읽어오지를 못하였다.
게다가 CLOB에 저장된 데이터는 평범하게 SELECT column 으로는 조회가 모두 되지 않아, 아래와 같이 조회하여야 했다.

SELECT DBMS_LOB.SUBSTR(col1, DBMS_LOB.GETLENGTH(col1)) FROM MyTable

그런데, 이때 col1의 크기가 4000을 넘어가면 또 에러가 발생한다.

이 부분은 방법을 다시 찾아봐야겠다.

위와 같이 방법을 찾아 진행했었는데, 해당 Query의 결과를 Java단에서 정상적으로 읽지를 못하였다.

그래서, Query는 기존 그대로 사용하고,

Java단에서 CLOB을 처리하도록 수정하였다.

result = // DB에서 읽어온 Data List
CLOB clob = (CLOB) result.get("col1");
BufferedReader contentReader = new BufferedReader(clob.getCharacterStream());
StringBuffer out = new StringBuffer();
String aux;
while ((aux=contentReader.readLine())!=null) {
    out.append(aux);
    out.append("\n"); 
}

[ ORACLE에서의 자료형 VARCHAR2, CLOB 사용 및 가공시 주의점 ]

보통 4000bytes 이하의 문자열에 사용되는 VARCHAR2

대용량 데이터 LOB 의 문자자료형인 CLOB 데이터

[ Charset 에 따른 한글 문자 bytes 크기 ]

ORACLE 의 권장 character set 은 UTF-8 이며, UTF-8 은 문자 하나에 최대 4bytes.

* 한글은 UTF-8 에서 문자 하나당 3bytes, EUC-KR 에서 문자 하나당 2bytes

* SQL에서 VARCHAR2 는 최대 4000bytes 길이 제한.

※ DB charset 확인하는 방법

1) 설정확인(권장)

SELECT name, value$
FROM sys.props$
WHERE name = 'NLS_CHARACTERSET';

2) 문자를 조회하여 확인

SELECT LENGTHB('일') FROM DUAL;

결과로 리턴되는 BYTES 크기 확인

* LENGTHB 는 BYTES 크기 반환

아래와 같이 TABLE 생성시 

CREATE TABLE sample.persons(
    name VARCHAR2(9) NOT NULL,
    PRIMARY KEY(name)
);

VARCHAR2(9) 의 9은 bytes 를 의미하며 UTF-8 기준으로 한글은 3글자밖에 들어갈 수 없다.

[ VARCHAR2 칼럼에 데이터 넣기 ]

1. Query 에서 잘라 넣기

1) SUBSTR 사용하여 CHAR 문자 잘라 넣기

INSERT INTO persons VALUES (SUBSTR('네자이름'), 1, 3));

* SUBSTR 은 문자 length 를 기준으로 자름

* TABLE DESC 에 명시된 자료형의 크기는 default가 bytes 기준이므로 CHAR length를 기준으로 위와 같이 문자열을 잘라넣는건 부적절.

2) SUBSTRB 사용하여 BYTES 단위로 문자 잘라 넣기

INSERT INTO persons VALUES (SUBSTRB('네자이름'), 1, 9));

* SUBSTRB 는 문자 bytes 를 기준으로 자름

* 위와같이 데이터를 넣을 경우 bytes단위로 문자열이 잘려나가기 때문에 한글이 깨질 수 있으므로 부적절.

※ 또 다른 문제.

SQL에서 VARCHAR2 는 최대 4000bytes 길이 제한이 있어

1), 2) 에서 사용된 SUBSTR, SUBSTRB 함수 모두 input 값이 4000bytes 가 넘을시 "ORA-01704: 문자열이 너무 깁니다" 에러가 발생한다.

2. 소스레벨에서 BYTES 단위로 잘라 넣기

정해진 bytes 크기의 한도내에서 한글이 깨지지 않게 문자열 자르기

SQL 에서 문자열을 가공하기보단 소스레벨에서 가공

[ CLOB 데이터 읽기 및 가공하기 ]

CLOB 데이터는 LENGTH, LENGTHB, SUBSTRB 함수 등을 지원하지 않음.

VARCHAR2 데이터에서 사용가능한 함수 사용을 위해 VARCHAR2 로 변환하고자 할 경우,

DBMS_LOB.SUBSTR(clob칼럼, 가져올길이, offset)을 사용하여 VARCHAR2 로 변환이 가능

* CLOB 데이터 가공시 SUBSTR 보다 DBMS_LOB.SUBSTR 의 퍼포먼스가 뛰어남 (참고)

* DBMS_LOB.SUBSTR() 사용시에도 4000bytes 를 넘는 데이터를 가져오지 않도록 주의 해야한다

※ 또 다른 문제.

temp segment 가 unrelease 되는 현상.

CLOB 칼럼에 함수를 직접 사용할 경우, 메모리와 같은 역할을 하는 temp segment를 사용하게 되며 release 하지 않고 쌓일 수 있다. 이는 서버 장애를 유발하여 서비스 전체에 영향을 줄 수 있다.

실제로 이와 관련하여, 운영중인 DB서버 전체가 마비되어 서비스 장애를 경험해 보았다..

=> CLOB 칼럼에 함수 사용은 지양하자. 소스레벨에 그대로 가져와서 소스레벨에서 자르는 걸 권장.

[ 관련에러 ]

ORA-01704: 문자열이 너무 깁니다

ORA-64203: 문자 집합 변환 후 대상 버퍼가 너무 작아 CLOB 데이터를 보유할 수 없습니다.

ORA-06502: 문자열 버퍼가 너무 작습니다.

참고 :

https://tomining.tistory.com/95