Oracle Database TIP
테이블의 데이터를 txt파일로 생성하는 예제(UTIL_FILE Package) 28 8 37,382

by 김정식 UTIL_FILE UTL_FILE_DIR CREATE DIRECTORY 파일생성 [2003.02.17]


  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
....
    

- 강좌 URL : http://www.gurubee.net/lecture/1700

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 그누세상 [2005.10.15 02:53:55]
Oracle 9i R1을 사용하시는 분은
select value from v$parameter where name='utl_file_dir';
을 사용하여 현재 파라미터가 잘 로드되었나 확인하시고, 그 디렉토리 이름을 LOG_DIR 대신 사용하세요.

by 궁금 [2006.07.07 10:48:00]
UTL_FILE 사용해서 파일을 오픈하여 파일의 내용을 테이블에 insert 하고 로그를 insert 하도록 했는데.. 로그 insert 로직을 타지 않네요. ㅜㅜ 머가 문제지..

by eagle [2006.10.20 16:48:23]
다른 분들은 위의 소스 그대로 잘 되는지 모르겠지만 일단 위의 txt파일 생성 디렉토리 지정부분에 경로의 따
옴표 없는 그대로 하니까 에러 나네요.
변경 --> UTL_FILE_DIR='C:\temp'

그리고 디렉토리에 접근 권한에도 read만 하니까 PL_WriteFile 프로시저 실행시 권한이 없다는 에러가 뜹니다.
read, write로 주니까 실행되네요.

by 고맙습니다 [2007.05.10 10:59:06]
그누세상님이 적어주신 퀄리로 utl_file_dir 설정여부를 확인 할 수 있었습니다.
제가 dba 아니라서 확인을 못하고 있었는데 감사합니다...ㅎㅎ

by unijuni [2007.05.11 10:42:07]
유닉스에도 같은 방식으로 하면 되나요?
경로나 디렉토리를 유닉스에 맞게하면 되나요? ^^

by 호좁 [2007.09.06 16:58:19]
아직 저에겐 수준이 너무 높은 지식이네요;;

by 나우 [2008.11.25 17:17:30]
에효 이거때매 계속 에러났는데 정말 잘 써먹었습니다!!
유닉스도 같게 하면되네요! C:\말고 /home/이런식으로요.

by 손님 [2012.10.11 19:33:21]
좋은 정보 감사합니다.^^
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입