꿈꾸는 개발자, DBA 커뮤니티 구루비

Oracle 7.3 부터 UTL_FILE 패키지를 이용해서 OS파일에 대한 read/write를 수행 할 수 있다.

UTL_FILE package의 설명

Function/ Procedure	설 명
FOPEN	Input이나 Output을 위해 file을 연다. file이 존재하지 않을 경우 file을 생성한다.
IS_OPEN	file handler를 이용해 file이 open되었는지 여부를 return한다.
FCLOSE	file을 닫는다.
FCLOSE_ALL	열려 있는 모든 file을 닫는다.
GET_LINE	open된 file로부터 한 line을 읽는다.
PUT	open된 file에 한 line을 write한다. (Line terminator를 붙이지 않는다.)
PUT_LINE	open된 file에 한 line을 write한다. (Line terminator를 붙인다.)
PUTF	string을 formatting에 의해 write한다. (printf처럼)
NEW_LINE	open된 file에 line terminator을 write한다.
FFLUSH	open된 모든 file의 내용을 file에 physical하게 write한다.

티렉토리 생성예제

파일을 제어 하려면 DIRECTORY를 생성해야 한다.

Oracle 7.3 부터 오라클 9i R1까지는 %ORACLE_HOME%\admin\\init.ora 파일에 txt파일이 생성될 디렉토리를 지정해야 한다.

Oracle 9i R2 에서는 CREATE DIRECTORY명령으로 DIRECTORY를 생성하면 된다.

Oracle 7.3 부터 오라클 9i R1까지 예제

 
-- ① %ORACLE_HOME%\admin\\init.ora파일을 열어서 아래의 
      파라미터를 추가 한다. 
   UTL_FILE_DIR=C:\temp

-- ② db shutdown 후 restart 함.

-- ③ 만약 UTL_FILE package를 인식하지 못하는 경우에 
   아래의 script를 실행시키면 된다.

-- SYS 유저로 접속
C:/> SQLPLUS sys/manager
SQL> @%ORACLE_HOME%\rdbms\admin\utlfile.sql
SQL> @%ORACLE_HOME%\rdbms\admin\prvtfile.plb

Oracle 9i R2 이후 버전 예제

 
-- ① SYS 나 SYSTEM USER 로 접속 한다.
C:\> SQLPLUS /NOLOG
SQL> CONN / AS SYSDBA

-- ② DIRECTORY를 생성 합니다.
SQL> CREATE DIRECTORY LOG_DIR AS 'C:\temp';

-- ③ 생성된 DIRECTORY에 대한 read권한을 부여합니다. 
SQL> GRANT READ ON DIRECTORY log_dir TO PUBLIC;

테이블 데이터를 파일로 생성하는 예제(Oracle 9i R2)

 
SQL> CREATE OR REPLACE PROCEDURE PL_WriteFile(fname varchar2)
     IS
 
      v_output UTL_FILE.FILE_TYPE;
      v_result VARCHAR2(4000);
        
      CURSOR sql_cur IS
      SELECT empno, ename, deptno
      FROM emp;
    
     BEGIN
 
      v_output := UTL_FILE.FOPEN('LOG_DIR', fname, 'A');

      FOR v_cur IN sql_cur LOOP
        v_result := v_cur.empno||' '||v_cur.ename||' '||v_cur.deptno;
        UTL_FILE.PUT_LINE(v_output, v_result);
      END LOOP; 

      UTL_FILE.FCLOSE(v_output);

     EXCEPTION
      WHEN UTL_FILE.INVALID_PATH THEN 
        DBMS_OUTPUT.PUT_LINE('INVALID PATH');
      WHEN UTL_FILE.INVALID_MODE THEN
        DBMS_OUTPUT.PUT_LINE('INVALID MODE');
      WHEN UTL_FILE.INVALID_OPERATION THEN
        DBMS_OUTPUT.PUT_LINE('INVALID OPERATION');
        
    END;
    /
 
SQL> EXEC PL_WriteFile('output.txt');
 
-- C:\temp 디렉토리에 파일이 생성되었는지 확인해 보세요..

위에서 생성한 파일을 읽어와서 출력하는 예제(Oracle 9i R2)

 
SQL> CREATE OR REPLACE PROCEDURE PL_ReadFile(fname varchar2)
     IS

        v_input UTL_FILE.FILE_TYPE;      
        
        -- Line retrieved from flat file        
        retrieved_buffer VARCHAR2(100); 
 
     BEGIN
 
        -- 파일을 Read
        v_input :=  UTL_FILE.FOPEN('LOG_DIR',fname,'R');
 
        LOOP
            UTL_FILE.GET_LINE(v_input, retrieved_buffer); 
            DBMS_OUTPUT.PUT_LINE(retrieved_buffer);
        END LOOP;
           
        -- CLose the file.
        UTL_FILE.FCLOSE(v_input);
 
     EXCEPTION
 
        WHEN NO_DATA_FOUND THEN
             DBMS_OUTPUT.PUT_LINE('no_data_found');
             UTL_FILE.FCLOSE(v_input);
        WHEN UTL_FILE.INVALID_PATH THEN
             DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
             UTL_FILE.FCLOSE(v_input);
        WHEN UTL_FILE.READ_ERROR THEN
             DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
             UTL_FILE.FCLOSE(v_input);           
        WHEN OTHERS THEN
             DBMS_OUTPUT.PUT_LINE('other stuff');
             UTL_FILE.FCLOSE(v_input);
     END;
     /
 
 SQL> SET SERVEROUTPUT ON;
 SQL> EXEC PL_ReadFile('output.txt');
  
7369  SMITH  20
7499  ALLEN  30
7521  WARD  30
7566  JONES  20
7654  MARTIN  30
7698  BLAKE  30
....