[Oracle] 오라클 LOB 타입이란? 오라클 CLOB 데이터 조회하는 법, DBMS

데이터베이스/[Oracle]

연구소장 J 2024. 2. 2. 19:16

LOB(Large Object) 타입이란?

LOB(Large Object)타입은 오디오, 비디오, 문서 등 다양한 데이터를 큰 용량까지 저장할 수 있는 데이터 타입이다.

LOB은 아래와 같이 4가지 종류가 존재한다.

BLOB(Binary Large Object) : 바이너리(binary)타입의 어떠한 데이터든 저장할 수 있다. 주로 이미지, 오디오, 영상에 활용된다.
CLOB(Character Large Object) : 매우 큰 용량의 문자열 혹은 문서를 저장할 때 사용한다. 고정 길이를 사용한다.
NCLOB(National Character Set Large Object) : National 문자열 세트를 저장한다. CLOB과는 다르게 가변 길이를 사용한다.
BFILE(External Binary File) : 데이터베이스가 아닌 호스트의 운영체제 파일시스템에 바이너리 파일을 저장하는 타입이다. 데이터베이스 테이블에서 BIFLE에 접근할 수 있다. 다만 이때 읽기 전용(read-only)으로만 접근할 수 있다. 보통 BFILE은 어플리케이션에 조작하지 않는 이미지와 같은 정적 데이터(static data)를 저장할 때 사용된다.

LOB 데이터를 다룰 땐 아래와 같은 제한사항이 있음으로 주의해야한다.

주요키(Primary Key)로 사용할 수 없다.
ORDER BY, GROUP BY 절에 사용할 수 없다.
JOIN문에서 SELECT...DISTINCT, SELECT...UNIQUE 등을 사용할 수 없다. 다만 UNION, MINUS 절에서는 사용가능
클러스터(Cluster)에 포함할 수 없다.

VARCHAR2 혹은 RAW 데이터는 4000byte의 길이 제한이 있다. 그 이상은 CLOB으로 저장해야한다.

그런데 CLOB 데이터를 SELECT로 조회하면 그 내용이 짤려서 나오는 경우가 있다.

이럴 때 모든 내용을 조회할 수 있는 방법이 있다.

바로 DBMS_LOB 패키지를 이용하는 것이다.

SELECT DBMS_LOB.SUBSTR(CONTENTS, DBMS_LOB.GETLENGTH(CONTENTS), 1) FROM SOME_TABLE;

위와 같이 DBMS_LOB 패키지의 SUBSTR 함수를 사용하면 CLOB 데이터의 손실없이 모든 내용을 조회 할 수 있다.

SUBSTR에 대해서는 아래에서 자세히 다루겠다.

DBMS_LOB 패키지에서 유용한 함수 몇 개의 사용법을 알아보자.

1. SUBSTR

DBMS_LOB.SUBSTR(CLOB컬럼, 길이, 시작점)

SUBSTR 함수는 위와 같이 사용할 수 있다. SUBSTR 함수를 사용하면 CLOB의 부분 문자열을 얻을 수 있다.

2. GETLENGTH

DBMS_LOB.GETLENGTH(LOB컬럼)

GETLENGTH 함수는 LOB데이터의 길이를 반환한다.

3. INSTR

DBMS_LOB.INSTR(컬럼, 찾을 단어, 시작위치, 단어의 순서)

INSTR 함수를 사용하면 찾고싶은 단어가 어디에 위치했는지 알 수 있다. 여기서 단어의 순서란 몇 번째로 나타나는 단어를 찾고 싶은지이다.

예를 들어

DBMS_LOB.INSTR(CONTENT, 'apple', 1, 1)

위와 같다면 첫번째 apple의 위치를 반환한다.

참고