[펌]테이블의 데이터를 txt파일로 생성하는 예제(UTIL_FILE Package)

오라클 불틴 No. 10327 참고해서 조금 수정했습니다.

◈ 테이블의 데이터를 txt파일로 생성하는 예제입니다.(UTL_FILE package)
 
    Oracle 7.3 부터 UTL_FILE package를 이용해서 OS파일에 대한 read/write를 수행할 수 있습니다.

◈ UTL_FILE package의 설명
 Function/Procedure
  Description
 
 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한다.
 

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

   1) %ORACLE_HOME%\admin\<SID>\init<SID>.ora파일을 열어서 아래의
      파라미터를 추가합니다.
   
      UTL_FILE_DIR=C:\temp

   2) db shutdown 후 restartup함.

   3) 만약 UTL_FILE package를 인식하지 못하는 경우에 아래의 script를 실행시키면 됩니다.
        -- sys유저로 접속
           SQLPLUS sys/manager
                  
       SQL> @%ORACLE_HOME%\rdbms\admin\utlfile.sql
           ..
       SQL> @%ORACLE_HOME%\rdbms\admin\prvtfile.plb

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

  1) SYS 나 SYSTEM USER 로 접속 합니다.

     C:\>SQLPLUS /NOLOG
     SQL> conn sys/manager

  2) DIRECTORY를 생성 합니다.

     SQL> CREATE DIRECTORY LOG_DIR AS 'C:\temp';
     DIRECTORY CREATED.

  3) 생성된 DIRECTORY에 대한 read권한을 부여합니다. .

     SQL> GRANT READ ON DIRECTORY log_dir TO PUBLIC;

 

◈ 테이블 데이터를 파일로 생성하는 예제(Oracle 9i R2에서 했습니다.)

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');
 
PL/SQL 처리가 정상적으로 완료되었습니다.
 
 C:\temp 디렉토리에 파일이 생성되었는지 확인해 보세요..

 

◈ 위에서 생성한 파일을 읽어와서 출력하는 예제(Oracle 9i R2에서 했습니다.)
 
   CREATE OR REPLACE PROCEDURE PL_ReadFile(fname varchar2)  IS

       v_input UTL_FILE.FILE_TYPE;             
       retrieved_buffer VARCHAR2(100); -- Line retrieved from flat file
 
  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
....

PL/SQL 처리가 정상적으로 완료되었습니다.

 

◈ 참고사항

 - v9.2 이전에는 utl_file function 에 대한 directory 를 access 하기 위해서는
   UTL_FILE_DIR parameter 를 init<sid>.ora file 에 기술하여야만 했습니다.
  
   그러나, UTL_FILE_DIR 을 access 하는 것에 대해 더이상 recommand 하지 않으며,
   UTL_FILE_DIR 대신에 CREATE DIRECTORY 기능을 recommand 합니다.
 
   DIRECTORY object는 application 관리자에서 보다 유연하고 융통성 있는
   제어를 제공하며, db의 shutdown 없는 dynamic 한 유지관리을 할 수 있게 합니다.
 
   CREATE DIRECTORY 에 대한 권한은 default 로 SYS 와 SYSTEM user 에 대해서만
   부여되어 있습니다.
 
 
 - UTL_FILE.FOPEN('LOG_DIR',fname,'R')에서
  'R'은 read를,'W'는 write를, 'A'는 append를 의미합니다.