오라클 파일 입출력 패키지 UTL_FILE

ㅗㄹ

안녕하세요 인터럽트입니다. 여러분들은 오라클에서 OS파일에 대한 read/write가 가능한지 알고 계셨나요?
저는 이번에 프로젝트를 하면서 레거시 소스를 보다가 처음으로 알았어요, 찾아보니 기능이 생긴 지 엄청 오래됐지만 아는 사람들만 간간히 사용하는 기능인 것 같더라고요(사실 저만 빼고 다 사용하고 있을지도?ㅎㅎ)
Oracle 7.3 버전에서 UTL_FILE 패키지가 추가됐는데 딱 보자마자 LOG를 남길 때 유용하겠다 라고 생각이 들었습니다.
자 그럼 지금부터 UTL_FILE 패키지에 대해 알아볼까요

---

UTL_FILE이란?

UTL_FILE패키지는 오라클 데이터베이스에서 파일 입출력 작업을 수행하기 위해 추가된 PL/SQL패키지 입니다.
이 패키지를 사용하면 데이터베이스에서 파일을 생성, 열기, 읽기, 쓰기, 삭제하는 작업을 수행할 수 있습니다. 주로 데이터 추출, 로그기록, 외부 파일의 데이터 가져오기 및 내보내기와 같은 작업에 활용되고 있습니다.

 

UTL_FILE 패키지 기능

Function

ex) FunctionName (parameter1, parameter2,,,) return returnType

• FCREATE (directory varchar2, filename varchar2, file_mode varchar2) return FILE_TYPE
  : 새 파일을 생성하고 파일 핸들을 반환한다.
  
  
• FOPEN (directory varchar2, filename varchar2, open_mode varchar2, max_linesize NUMBER default 1024) return FILE_TYPE
  : 기존 파일을 열고 파일 핸들을 반환한다.
  open_mode옵션 파일의 열기 모드를 지정
  r : 읽기모드
  w : 쓰기모드
  a : 추가모드
  max_linesize옵션 파일 한 줄에 저장할 수 있는 최대 문자 수를 지정, 'a' 모드에서만 사용가능
  
  
• FGETATTR (file_handle FILE_TYPE, filename OUT varchar2, location OUT varchar2, file_exists OUT boolean) return NUMBER
  : 파일 속성을 가져온다. 
• GET_LINE (file_handle FILE_TYPE, buffer OUT varchar2) return boolean
  : 파일에서 한 줄씩 읽어온다. 읽을 줄이 없으면 FALSE를 반환하고 그렇지 않으면 TRUE를 반환한다.
  
  
• IS_OPEN (file_handle FILE_TYPE) return boolean
  : 파일이 열려있는지 여부를 확인.
  
  

Procedure

ProcedureName (Parameter1, Parameter2,,,)

• FCLOSE (file_handle FILE_TYPE)
  : 파일을 닫는다.
  
  
• REMOVE (directory varchar2, filenm varchar2)
  :파일을 삭제한다.
  
  
• PUT (file_handle FILE_TYPE, buffer varchar2)
  : 파일에 데이터를 쓴다.
  
  
• PUT_LINE (file_handle FILE_TYPE, buffer varchar2)
  : 파일에 데이터와 개행문자를 함께 쓴다.
  
  
• MKDIR (directory varchar2)
  : 디렉터리를 생성한다.

 

UTL_FILE 예제

준비단계

UTL_FILE패키지를 사용하기 위해서는 디렉터리를 생성해야 하고 권한을 부여해 줘야 합니다.

as-is (Oracle 7.3 ~ Oracle 9i R1)
오라클 7.3버전에서는 데이터베이스 디렉터리 개념이 아직 도입되지 않아서 UTL.FILE패키지를 사용하여 파일 조작 작업을 수행하려면 오라클 서버가 설치된 운영체제의 파일 시스템 경로를 직접 지정해줘야 합니다.

 

1. 디렉터리 생성
   

   //oracle_home은 실제 Oracle Home 디렉터리 경로를 의미하고 utl_file_directory는 UTL_FILE을 핸들링하기 위한 디렉토리 이름입니다.
   mkdir -p /path/to/oracle_home/utl_file_directory

2. Oracle Home 경로설정
   1번단계에서 Oracle Home 디렉터리 내부에 UTL_FILE 디렉터리가 생성했으니 Oracle 데이터베이스 설정을 업데이트해서 UTL_FILE 디렉터리를 가리키도록 설정해야 합니다.
   SQL*PLUS를 이용해 데이터베이스에 연결한 후 아래의 쿼리를 실행합니다.
   

   ALTER SYSTEM SET UTL_FILE_DIR = '/path/to/oracle_home/utl_file_directory' SCOPE=SPFILE;

3. SPFILE 재구성 & 서버재시작
   변경 사항을 적용하려면 SPFILE(서버파라미터파일)을 재구성해야 하고, 재구성을 위해 서버를 재시작해줍니다.
   

   SHUTDOWN IMMEDIATE;
   STARTUP;

위의 3단계를 완료하시면 이제부터 UTL_FILE 패키지를 사용하여 파일을 조작할 수 있습니다.
Oracle 7.3 버전에서는 UTL_FILE_DIR 초기화 매개변수가 도입되기 전이라 디렉터리 권한을 설정할 수 있는 방법이나 명령어가 없습니다. 즉 보안에 취약합니다.

 

to be (Oracle 9i R2 이후)

Oracle 9i R2 이후부터는 UTL_FILE 패키지가 CREATE DIRECTORY에 대응하게 되었습니다.
즉 디렉터리 추가에 따르는 재가동을 이제 안 해도 됩니다.

1. SYS나 SYSTEM USER로 접속한다.
   
   
2. DIRECTORY를 생성한다.
   

   CREATE DIRECTORY  dir_name  AS 'C:\temp';

3. 생성된 DIRECTORY에 대한 READ 권한을 부여한다.
   

   GRANT READ ON DIRECTORY  dir_name TO PUBLIC;

UTL_FILE 패키지로 테이블 데이터를 파일로 생성하는 예제

DECLARE
    l_file_handle UTL_FILE.FILE_TYPE;
    l_buffer VARCHAR2(100);
BEGIN
    -- 파일 핸들 생성
  l_file_handle := UTL_FILE.FOPEN('test.txt', 'w');
 
  -- 테이블의 내용을 반복문으로 읽어 파일에 쓰기
  FOR i IN 1..3 LOOP
    SELECT id, name, age INTO l_buffer FROM test_table WHERE id = i;
 
  -- 파일에 쓰기
    UTL_FILE.PUT_LINE(l_file_handle, l_buffer);
  END LOOP;
 
  -- 파일 핸들 닫기
  UTL_FILE.FCLOSE(l_file_handle);
END;

 

UTL_FILE 패키지로 TXT파일을 OPEN하는 예제

-- TXT 파일을 읽기 모드로 열기
DECLARE
  l_file_handle UTL_FILE.FILE_TYPE;
  l_buffer VARCHAR2(100);
BEGIN
  l_file_handle := UTL_FILE.FOPEN('myfile.txt', 'r');
 
  -- 파일에서 한 줄씩 읽기
  LOOP
    UTL_FILE.GET_LINE(l_file_handle, l_buffer);
 
    -- l_buffer에 저장된 내용 출력
    DBMS_OUTPUT.PUT_LINE(l_buffer);
  END LOOP;
 
  -- 파일 핸들 닫기
  UTL_FILE.FCLOSE(l_file_handle);
END;

 

정리

여기까지 UTL_FILE 패키지의 사용법을 예제와 함께 정리해 봤습니다.

예제로 확인해 보니 UTL_FILE의 사용법이 한눈에 들어오네요.

저는 조만간 오라클 스케줄러 LOG파일을 작성하는 기능으로 사용할 거 같아요.

배우자마자 바로 응용해 버리기ㅎㅎ

 

다음에 조금 더 알찬 내용으로 포스팅하겠습니다.

읽어주셔서 감사합니다.