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

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의 제한사항

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

 

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

 

CLOB 데이터 조회

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 사용법

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의 위치를 반환한다. 

 

 

---

참고

 

[1] 오라클 공식 문서 : https://docs.oracle.com/en/database/oracle/oracle-database/23/adlob/LOB-classifications.html