DEVELOPyo

[ 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

1. CLOB 을 읽을 때 4000bytes 가 넘을 경우 java 단에서 메모리 부족으로 에러가 발생한다.

2. DBMS_LOB.SUBSTR을 사용하여 4000bytes 이하로 데이터를 잘라서 가져올 수 있다 (CLOB 데이터는 VARCHAR2로 바뀐다)

3. CLOB 칼럼에 함수를 씌우거나 "||" 를 사용하여 문자를 붙이는 등의 작업이 DB query 에서 이뤄질 경우 temp segment 에 CLOB 데이터 크기만큼 공간을 차지하게 된다.

4. order by 등과 다르게 CLOB 으로 인한 TEMP segment 공간은 session이 끝날때 까지 제거되지 않는다.

5. TEMP space 가 꽉 차게 되면 타 서비스에 영향을 주게 된다

SUBSTR 을 사용하지 않고 CLOB 데이터를 그대로 읽어오면 TEMP SEGMENT 가 full 차는 현상은 방지할 수 있지만

CLOB 데이터의 크기가 4000bytes 가 넘을 경우 자바에서 읽어올 수 없다.

해결방법은

1. CLOB 데이터를 temp segment 에서 release 해줄 수 있도록 alter session ~ 을 SUBSTR(CLOB,...) 쿼리와 함께 호출.

2. java 에서 clob 데이터에 대한 release 처리를 해준다.

SELECT A.STATUS        -- 상태정보

      ,A.SID           -- SID 

      ,A.SERIAL#       -- 시리얼번호 

      ,A.USERNAME      -- 유저 

      ,A.OSUSER        -- OS 사용자

      ,B.SQL_TEXT      -- 쿼리

  FROM V$SESSION A

      ,V$SQLAREA B

 WHERE A.SQL_HASH_VALUE = B.HASH_VALUE 

   AND A.SQL_ADDRESS = B.ADDRESS 

   AND A.STATUS = 'ACTIVE'

taking.co.kr/96

로컬 ORACLE 설치 bit 확인

www.overtop.co.kr/279

* 현재시각 2021/02/08 17시일때

select * from where tdate > sysdate-1;
: 2/7 17시 이후 데이터

select * from where trunc(tdate) > trunc(sysdate)-1;
: 2/8 0시 이후 데이터

select * from where tdate > trunc(sysdate)-1;
: 2/7 0시 이후 데이터

LEAD

다음행읽기

LAG

이전행읽기

gent.tistory.com/339

MS SQL

EXEC procedure();

그외 ORACLE, MYSQL 등

CALL procedure();

EXEC vs CALL 차이

리턴(out) 파라미터가 있는 경우 프로시저 호출 방법

DECLARE
var aa VARCHAR(10);
var bb VARCHAR(10);
BEGIN
CALL PROCEDURE(
aa,
bb,
param
);
END;

Mybatis xml 에서의 프로시저 호출 방법

{CALL SEND_PROC(#{retCd, jdbcType=NUMERIC, mode=OUT},
                         #{retMsg, jdbcType=VARCHAR, mode=OUT},
                         #{flag, jdbcType=VARCHAR, mode=IN},
                         #{msg, jdbcType=VARCHAR, mode=IN},
                         #{tel, jdbcType=VARCHAR, mode=IN},
                         #{id, jdbcType=VARCHAR, mode=IN},
                         TO_CHAR(SYSDATE +
                                      (#{seq, jdbcType=NUMERIC, mode=IN}) * 10 / (24*60*60),                                                                    'YYYYMMDDHH24MISS') ,'')
}