문제상황

Oracle PL/SQL에서 UTL_FILE을 사용하여 CSV 파일을 읽는 도중, 한 줄의 데이터가 길어질 경우 에러가 발생하는 문제가 있었습니다.

-- 문제가 발생한 코드
DECLARE
    file_handle UTL_FILE.FILE_TYPE; -- 파일 핸들러 선언
    line_buffer VARCHAR2(1000);     -- 한 줄 데이터를 저장할 버퍼
BEGIN
    -- 파일 열기: 읽기 모드(R)
    file_handle := UTL_FILE.FOPEN('DIRECTORY_NAME', 'example.csv', 'R');

    -- 한 줄씩 파일 읽기
    UTL_FILE.GET_LINE(file_handle, line_buffer);
    DBMS_OUTPUT.PUT_LINE(line_buffer);

    -- 파일 닫기
    UTL_FILE.FCLOSE(file_handle);
END;
/

ora-29284: 파일 읽기 오류

해결시도

에러가 나는 문장의 길이를 확인했을 때 1000바이트가 초과했다.
길이가 긴 문장에서만 위와 같은 에러가 발생했고, 이는 읽어오는 버퍼의 크기가 작아서 발생한 문제라고 생각했다. 그래서 버퍼의 크기를 늘려봤지만 해결되지 않았다.

-- 문제가 발생한 코드
DECLARE
    file_handle UTL_FILE.FILE_TYPE; -- 파일 핸들러 선언
    line_buffer VARCHAR2(2000);     -- 한 줄 데이터를 저장할 버퍼 -> 1000에서 2000으로 늘림
BEGIN
    -- 파일 열기: 읽기 모드(R)
    file_handle := UTL_FILE.FOPEN('DIRECTORY_NAME', 'example.csv', 'R');

    -- 한 줄씩 파일 읽기
    UTL_FILE.GET_LINE(file_handle, line_buffer);
    DBMS_OUTPUT.PUT_LINE(line_buffer);

    -- 파일 닫기
    UTL_FILE.FCLOSE(file_handle);
END;
/

그래서 파일을 읽는 버퍼의 크기(line_buffer)를 1000에서 2000으로 늘러보았지만 해결되지 않았다. 여러 값으로 변경도 해보았지만 여전히 긴 길이의 줄은 제대로 읽어오지 못했다.

해결 방법

UTL_FILE의 기본 MAX_LINESIZE 제한
이게 문제였다. UTL_FILE.GET_LINE은 기본적으로 한 줄 최대 1024바이트(1KB)까지만 읽을 수 있다. 그래서 아무리 line_buffer의 크기를 늘려도 제대로 읽어오지 못하는 것이었다.
그래서 긴 데이터를 처리하려면 UTL_FILE.FOPEN 호출 시 MAX_LINESIZE 값을 명시적으로 설정해야 한다. UTL_FILE.FOPEN의 네 번째 매개변수로 MAX_LINESIZE를 설정하여, 파일에서 읽을 한 줄의 크기를 늘렸다.

이를 반영한 최종 수정코드이다.

-- 문제가 발생한 코드
DECLARE
    file_handle UTL_FILE.FILE_TYPE; -- 파일 핸들러 선언
    line_buffer VARCHAR2(2000);     -- 한 줄 데이터를 저장할 버퍼 -> 1000에서 2000으로 늘림
BEGIN
    -- 파일 열기: 읽기 모드(R) -> MAX_LINESIZE의 길이를 2000으로 증가시킴
    file_handle := UTL_FILE.FOPEN('DIRECTORY_NAME', 'example.csv', 'R', 2000);

    -- 한 줄씩 파일 읽기
    UTL_FILE.GET_LINE(file_handle, line_buffer);
    DBMS_OUTPUT.PUT_LINE(line_buffer);

    -- 파일 닫기
    UTL_FILE.FCLOSE(file_handle);
END;
/

추가 참고 사항

• MAX_LINESIZE 제한 : Oracle 12c 이상에서는 32767까지 설정 가능하지만 11g 이하에서는 최대 32767이 지원되지 않을 수 있다.

• 참고 사이트(Oracle 공식 사이트) : https://docs.oracle.com/en/database/oracle/oracle-database/23/arpls/UTL_FILE.html#GUID-DF14ADC3-983D-4E0F-BE2C-60733FF58539