[oracle] LOB(clob, blob) 컬럼 사이즈(bytes) 확인

테스트 테이블 생성

clob, blob을 포함한 테이블 생성 및 데이터 적재

SQL> CREATE TABLE sk.lob_test (
    id      NUMBER PRIMARY KEY,
    clob_col CLOB,
    blob_col BLOB
);

Table created.

SQL> INSERT INTO sk.lob_test (id, clob_col)
VALUES (1, '가나다라마바사');

1 row created.

SQL> INSERT INTO sk.lob_test (id, blob_col)
VALUES (2, utl_raw.cast_to_raw('가나다라마바사'));

1 row created.

SQL> commit ;
SQL> SELECT * FROM sk.lob_test
;

        ID CLOB_COL                                           BLOB_COL
---------- -------------------------------------------------- ----------------------------------------------------------------------
         1 가나다라마바사
         2                                                    EAB080EB8298EB8BA4EB9DBCEBA788EBB094EC82AC

SQL>

 

LOB 컬럼 사이즈 확인

clob 컬럼에서

DBMS_LOB.GETLENGTH 함수는 문자 수를 출력하고

사이즈(bytes)를 구할 때는LENGTHB(DBMS_LOB.SUBSTR(CLOB_COL,DBMS_LOB.GETLENGTH(CLOB_COL),1))와 같은 형식으로 조회 가능합니다.

 

blob 컬럼에서 

DBMS_LOB.GETLENGTH 함수는 사이즈(bytes)를 출력합니다

SQL> SELECT
    ID,CLOB_COL
    , DBMS_LOB.GETLENGTH(CLOB_COL) AS CLOB_LENGTH
    , LENGTHB(DBMS_LOB.SUBSTR(CLOB_COL,DBMS_LOB.GETLENGTH(CLOB_COL),1)) LENGTHB
    , DBMS_LOB.SUBSTR(CLOB_COL,1,DBMS_LOB.GETLENGTH(CLOB_COL)) LAST_VAL
    , BLOB_COL
    , DBMS_LOB.GETLENGTH(BLOB_COL) AS BLOB_LENGTH
    , LENGTHB(DBMS_LOB.SUBSTR(BLOB_COL,DBMS_LOB.GETLENGTH(BLOB_COL),1)) LENGTHB
    , DBMS_LOB.SUBSTR(BLOB_COL,1,DBMS_LOB.GETLENGTH(BLOB_COL)) LAST_VAL
FROM SK.LOB_TEST; 

        ID CLOB_COL              CLOB_LENGTH    LENGTHB LAST_VAL   BLOB_COL                                           BLOB_LENGTH    LENGTHB LAST_VAL
---------- --------------------- ----------- ---------- ---------- -------------------------------------------------- ----------- ---------- ----------
         1 가나다라마바사                  7         21 사                                                             
         2                                                         EAB080EB8298EB8BA4EB9DBCEBA788EBB094EC82AC                  21         42 AC

SQL>

 

clob 사이즈 커서 ORA-06502 에러 발생 시

plsql 구문을 사용하여 조회

-- 30000 byte 데이터 삽입
SQL> DECLARE
    v_clob CLOB;
    v_chunk VARCHAR2(32767) := RPAD('X', 10000, 'X'); 
BEGIN
    -- CLOB 초기화
    INSERT INTO sk.lob_test (id, clob_col)
    VALUES (3, EMPTY_CLOB())
    RETURNING clob_col INTO v_clob;
    -- 10,000자 청크를 3번 삽입
    FOR i IN 1..3 LOOP
        DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_chunk), v_chunk);
    END LOOP;

    COMMIT;
END;
/
PL/SQL procedure successfully completed.

SQL>

-- 에러 발생
SQL> SELECT
    ID,CLOB_COL
    , DBMS_LOB.GETLENGTH(CLOB_COL) AS CLOB_LENGTH
    , LENGTHB(DBMS_LOB.SUBSTR(CLOB_COL,DBMS_LOB.GETLENGTH(CLOB_COL),1)) LENGTHB
    , DBMS_LOB.SUBSTR(CLOB_COL,1,DBMS_LOB.GETLENGTH(CLOB_COL)) LAST_VAL
    , BLOB_COL
    , DBMS_LOB.GETLENGTH(BLOB_COL) AS BLOB_LENGTH
    , LENGTHB(DBMS_LOB.SUBSTR(BLOB_COL,DBMS_LOB.GETLENGTH(BLOB_COL),1)) LENGTHB
    , DBMS_LOB.SUBSTR(BLOB_COL,1,DBMS_LOB.GETLENGTH(BLOB_COL)) LAST_VAL
FROM SK.LOB_TEST
WHERE id = 3
;
ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


-- PL/SQL로 조회
SQL> BEGIN
    FOR rec IN (
        SELECT id id, clob_col val
        FROM sk.lob_test
        --where id = 3
    ) LOOP
        DECLARE
            v_clob CLOB := rec.val;
            v_total_bytes NUMBER := 0;
            v_chunk_size NUMBER := 10000;
            v_offset NUMBER := 1;
            v_length NUMBER := DBMS_LOB.GETLENGTH(v_clob);
            v_chunk VARCHAR2(32767);
        BEGIN
            -- CLOB이 비어있지 않은 경우에만 처리
            IF v_length > 0 THEN
                WHILE v_offset <= v_length LOOP
                    v_chunk := DBMS_LOB.SUBSTR(v_clob, v_chunk_size, v_offset);
                    v_total_bytes := v_total_bytes + LENGTHB(v_chunk);
                    v_offset := v_offset + v_chunk_size;
                END LOOP;
            END IF;
            DBMS_OUTPUT.PUT_LINE('ID: ' || rec.id || ', CLOB 바이트 수: ' || v_total_bytes);
        END;
    END LOOP;
END;
/

ID: 1, CLOB 바이트 수: 21
ID: 2, CLOB 바이트 수: 0
ID: 3, CLOB 바이트 수: 30000

PL/SQL procedure successfully completed.

SQL>