[ 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 에서 문자열을 가공하기보단 소스레벨에서 가공
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
public static String subStrByte(String str, int cutlen){
if(!str.isEmpty()){
str = str.trim();
if(str.getBytes().length <= cutlen) {
return str;
}else{
StringBuffer sbStr = new StringBuffer(cutlen);
int nCnt = 0;
for(char ch: str.toCharArray()){
nCnt += String.valueOf(ch).getBytes().length;
if(nCnt > cutlen) break;
sbStr.append(ch);
}
return sbStr.toString();
}
} else {
return "";
}
}
|
cs |
[ 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
'DB > ORACLE' 카테고리의 다른 글
| [ORACLE] CLOB BLOB (0) | 2021.04.14 |
|---|---|
| [ORACLE] 실행중인 쿼리 조회 (0) | 2021.03.25 |
| ORACLE 로컬 bit 확인 (0) | 2021.03.16 |
| [oracle] sysdate (0) | 2021.02.08 |
| LEAD, LAG (0) | 2021.02.03 |