[DB][Oracle] DBMS_LOB.SUBSTR

DBMS_LOB.SUBSTR 는 Oracle에서 LOB 타입(CLOB, BLOB) 데이터를 잘라서

VARCHAR2 나 RAW 타입으로 변환해주는 함수예요.

CLOB 은 그대로는 ORDER BY 나 GROUP BY 에 못 쓰기 때문에, 이 함수로 문자열을 잘라내 변환해야 합니다.

 

 

📌 기본 구조

 

DBMS_LOB.SUBSTR(
    lob_loc   IN CLOB/BLOB,   -- 잘라낼 LOB 데이터
    amount    IN INTEGER,     -- 잘라낼 길이 (문자 수 또는 바이트 수)
    offset    IN INTEGER := 1 -- 시작 위치 (1부터 시작)
) RETURN VARCHAR2 | RAW;

---

📌 파라미터 설명

1. lob_loc
   • 잘라낼 대상 LOB (예: CLOB 컬럼, BLOB 컬럼)
2. amount
   • 잘라낼 길이
   • CLOB → 문자 수 기준
   • BLOB → 바이트 수 기준
   • 최대 32767까지 가능 (단, SQL 문장 안에서는 4000자가 한계)
3. offset
   • 시작 위치 (기본값 1)
   • 예: 1 이면 처음부터, 10이면 10번째 문자부터

---

📌 예제

 

-- CLOB에서 앞 1000글자 추출
SELECT DBMS_LOB.SUBSTR(sp_memo, 1000, 1) AS sp_memo_preview
FROM T_CR;

-- ORDER BY에 사용
SELECT *
FROM T_CR
ORDER BY DBMS_LOB.SUBSTR(sp_memo, 4000, 1);

---

📌 주의할 점

1. SQL에서는 VARCHAR2 최대 4000자까지만 가능
   → 그래서 보통 amount를 4000 이하로 설정합니다.
2. PL/SQL에서는 32767까지 가능
   → 더 길게 쓰려면 PL/SQL 블록에서만 가능합니다.
3. 성능 고려 필요
   → 긴 CLOB을 자주 SUBSTR 하면 성능 저하가 올 수 있어, 정렬/검색 자주 하는 컬럼이면 VARCHAR2 정렬용 컬럼을 따로 두는 게 권장됩니다.

---

👉 정리하면:
DBMS_LOB.SUBSTR(sp_memo, 4000, 1) = sp_memo 컬럼의 앞 4000글자를 VARCHAR2로 변환
→ 이렇게 하면 ORDER BY, GROUP BY, JOIN 등에 사용할 수 있습니다.