[ORACLE] CLOB 데이터 추출 시 ORA-06502: PL/SQL: 수치 또는 값 오류: 문자열 버퍼가 너무 작습니다 ORA-06512

안녕하세요 !!

오래간만에 글 작성하네요 ! 자주 써야지 자주써야지 생각은 하지만 쉽게 되지 않습니다 ㅠㅠ

꾸준하신 분들 존경합니다!

 

금일 알아볼 내용은 ! 바로바로

오라클 커서!

입니다 ! CLOB 데이터를 추출하려다가 ORA-06502: PL/SQL: 수치 또는 값 오류: 문자열 버퍼가 너무 작습니다 ORA-06512 오류가 발생하여 알아보니..

CLOB데이터 크기가 너무 커서 오류가 발생하더군여...

DBMS_LOB.SUBSTR 를 사용하여 해결하라는 글이 많았으나, 저는 4000BYTE가 넘어가버려서 해결이 안됫습니다...

그래서 알아보던 중 오라클 커서 (CURSOR) 라는게 있더군요 ! 바로 알아보도록 하겠습니다 !

 

오라클 커서란?

커서의 종류에는 묵시적 커서(암시적 커서)와 명시적 커서가 있습니다. 묵시적 커서는 오라클 내부에서 자동으로 생성되어 SQL문장이 실행될 때마다 자동으로 만들어져 실행되는 커서이고, 명시적 커서는 사용자가 직접 정의해서 사용하는 커서를 말합니다. 이번 포스팅에서는 사용자가 직접 만드는 명시적 커서를 만들어보고 사용하는 방법에 대해 알아보도록 하겠습니다.

• 특정 SQL 문장을 처리한 결과를 담고 있는 메모리 영역을 가리키는 일종의 포인터
• SQL문 결과 ROW가 여러 개인데 커서를 사용하면 ROW에 순차접근이 가능
• 커서의 종류에는 묵시적 커서, 명시적 커서
• 묵시적 커서 : 오라클 내부에서 자동 생성되어 SQL 문장(INSERT, UPDATE, DELETE 등) 이 실행될 때마다 자동으로 실행
• 명시적 커서 : 사용자가 직접 정의해서 사용하는 커서

이 글에서는 명시적 커서를 알아보도록 하겠습니다 !

명시적 커서

DECLARE
    CURSOR clob_cursor IS --커서 선언을 통하여 조회할 컬럼 선택
		SELECT '컬럼'     -- 조회하고싶은 SELECT문 입력.
        FROM '테이블'
        WHERE '조건';

    l_contents     CLOB;  -- 컬럼을 저정할 변수선언, 한가지 컬럼만 조회가 필요하면 변수선언은 하나만 하면됨.
    l_buffer       VARCHAR2(32767);  -- (청크단위로 임시저장할 varchar2형 변수)
    l_pos          INTEGER;		  -- (현재 데이터 위치)
    l_amount       INTEGER := 32767;
    l_contents_len     INTEGER;
BEGIN
    OPEN clob_cursor;  -- 선언한 커서 오픈
    LOOP
        FETCH clob_cursor INTO l_contents; -- 커서로부터 데이터 가져옴 FETCH 구문은 현재 커서위치에서 데이터를 가져와 변수에 저장함 더이상 데이터가 없으면 종료
        EXIT WHEN clob_cursor%NOTFOUND;

        l_pos := 1;
        l_contents_len := DBMS_LOB.GETLENGTH(l_contents);
              
        DBMS_OUTPUT.PUT_LINE('루프돌때마다 출력: ' || TO_CHAR(l_contents));
                
        LOOP
            EXIT WHEN l_pos > l_contents_len;

            l_buffer := DBMS_LOB.SUBSTR(l_contents, l_amount, l_pos);
            l_pos := l_pos + l_amount;
                        
        END LOOP;
        
    END LOOP;
    CLOSE clob_cursor;
END;
/

 

위 예제를 사용하면 4000BYTE가 넘는 CLOB 데이터도 조회가 가능합니다.

조회할 컬럼의 개수가 늘어난다면 변수선언도 추가로 해줘야 실행됩니다 !

 

태클 및 문의댓글은 환영입니다!