2012-10-06

DBMS/오라클] 오라클 UTL_FILE( 파일읽기)

오라클 UTL_FILE( 파일읽기)  sql 
2011/10/06 20:57

 


   ------------------------------------------------------------------------------------------------------
   V_FILENAME := 'LV_CARD_CD_'||V_YYMMDD||'.sam';
    FHANDLER := UTL_FILE.FOPEN('DIR_SAM', V_FILENAME, 'R');
    IF UTL_FILE.IS_OPEN(FHANDLER) THEN
      LOOP
        BEGIN
        UTL_FILE.GET_LINE(FHANDLER, V_LINE);
        IF V_LINE IS NULL THEN
          EXIT;
        END IF;
        VAL.CARD_CD := TRIM(REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 1));
        VAL.CARD_CD_NM := TRIM(CONVERT(REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 2),'UTF8','KO16MSWIN949'));
        INSERT /*+ APPEND PARALLEL(LV_CARD_CD 16) */  INTO A
        ( CARD_CD,CARD_CD_NM )
        VALUES
        (VAL.CARD_CD,VAL.CARD_CD_NM )
        ;
        V_I := V_I + 1;
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
               EXIT;
        END;
      END LOOP;
    END IF;
    UTL_FILE.FCLOSE(FHANDLER);





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

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

UTL_FILE package의 설명

Function/
Procedure		설 명
FOPENInput이나 Output을 위해 file을 연다. 
file이 존재하지 않을 경우 file을 생성한다.
IS_OPENfile handler를 이용해 file이 open되었는지 여부를 return한다.
FCLOSEfile을 닫는다.
FCLOSE_ALL열려 있는 모든 file을 닫는다.
GET_LINEopen된 file로부터 한 line을 읽는다.
PUTopen된 file에 한 line을 write한다. (Line terminator를 붙이지 않는다.)
PUT_LINEopen된 file에 한 line을 write한다. (Line terminator를 붙인다.)
PUTFstring을 formatting에 의해 write한다. (printf처럼)
NEW_LINEopen된 file에 line terminator을 write한다.
FFLUSHopen된 모든 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
....

 
 





출처 : http://cooldev.tistory.com/34

Oracle File UTL_FILE_DIR

UTL_FILE 패키지를 사용하여 PL/SQL에서 파일의 입출력을 실시할 수 있다.
PL/SQL에는 SQL*Plus의 SPOOL에 해당하는 간략화된 명령어가 없으므로 큰 로그를 출력하고 싶은 경우에는 UTL_FILE 패키지를 사용하든지 혹은 테이블을 사용한다.
표준 출력에는 DBMS_OUTPUT 패키지를 사용한다.
Oracle 8i이전이라면 초기화 파라미터 UTL_FILE_DIR를 설정해야 한다. 

준비작업

■ 디렉토리 작성
디렉토리를 작성할 경우에 OS 의 DBA 인 유저(통상은 oracle)로 디렉토리를 작성한다.
root 로 작성하는 경우에는, chown, chgrp, chmod 등에 의해 읽고 쓰기의 권한을 올바르게 설정한다.
파일의 I/O는 서버 프로세스에 의해서 행해지므로 그 프로세스의 오너(oracle)가 읽고 쓰기할 수 있어야 한다.
작성과 권한의 설정예 (root 에 의한 조작:권한만 있으면 root 일 필요는 없습니다)
# mkdir /u05/file_storage/recv_dir
# mkdir /u05/file_storage/send_dir

# chgrp dba /u05/file_storage/recv_dir
# chgrp dba /u05/file_storage/send_dir

# chown oracle /u05/file_storage/recv_dir
# chown oracle /u05/file_storage/send_dir

# chmod 700 /u05/file_storage/recv_dir
# chmod 700 /u05/file_storage/send_dir


디렉토리·오브젝트에 의한 파일 액세스



■ 디렉토리의 작성 CRAETE DIRECTORY
Oracle 9i 버젼부터 UTL_FILE 패키지가 CREATE DIRECTORY에 대응하게 되었다.
디렉토리의 추가에 따르는 재기동도 불필요.
  • 디렉토리 오브젝트의 작성 
    DIRECTORY의 작성은 CREATE DIRECTORY 권한이 필요
       CREATE DIRECTORY recv_area AS '/u05/file_storage/recv_dir';
         CREATE DIRECTORY send_area AS '/u05/file_storage/send_dir';
  • 디렉토리에의 액세스권의 설정
    읽기 권한과 쓰기 권한은 개별적으로 처리한다.
        GRANT READ ON DIRECTORY recv_area TO user_name ;
        GRANT WRITE ON DIRECTORY send_area TO user_name ;
         -- 
         SELECT * FROM ALL_DIRECTORIES ;


■ 파일쓰기 (초기화 파라메터 사용시 )
CREATE OR REPLACE PROCEDURERIVUS.CREATE_DIR_WRITE_SAMPLE
AS
  vHandle   UTL_FILE.FILE_TYPE;
  vDirname  VARCHAR2(250);
  vFilename VARCHAR2(250);
  vOutput   VARCHAR2(32767);
BEGIN
  vDirname  := 'SEND_AREA';-- (주)디렉토리 오브젝트명을 대문자로 지정한다
vFilename := 'test.txt';
  vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'w', 32767);
  vOutput   := 'CREATE DIRECTORY 경유로의 파일출력';
  UTL_FILE.PUT_LINE(vHandle, vOutput);
  UTL_FILE.FCLOSE(vHandle);
EXCEPTION WHENOTHERSTHEN
  UTL_FILE.FCLOSE_ALL;
  RAISE;
END;
/ 
■ 파일읽기 (초기화 파라메터 사용시 )
CREATE OR REPLACE PROCEDURERIVUS.CREATE_DIR_READ_SAMPLE
AS
  vHandle   UTL_FILE.FILE_TYPE;
  vDirname  VARCHAR2(250);
  vFilename VARCHAR2(250);
  vInput    VARCHAR2(32767);
BEGIN
  vDirname  := 'RECV_AREA';
  vFilename := 'test.txt';
  vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'r', 32767);

  BEGIN
    LOOP
      UTL_FILE.GET_LINE(vHandle, vInput,32767);
      DBMS_OUTPUT.PUT_LINE(vInput);
    END LOOP;
  EXCEPTIONWHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('파일의 마지막');
  END;

  UTL_FILE.FCLOSE(vHandle);
EXCEPTION WHENOTHERS THEN
  UTL_FILE.FCLOSE_ALL; RAISE;
END;
/
주의사항
  • UTL_FILE 패키지를 사용해 한 번에 입출력할 수 있는 길이는 32767(32K) 바이트 (※) 
  • OPEN 한 파일은 반드시 CLOSE 하도록 예외 처리를 해둔다.UTL_FILE.FCLOSE_ALL 를 사용하면 편리
  • RAW형을 출력할 수 있지만 줄 끝에는 OS 고유의 개행 코드가 반드시 부여된다. 
    Oracle 10g 에서는 wb 에 의한 (RAW 모드) FOPEN 가 서포트되고 있으므로, 그 쪽을 사용하면 문제 없다. 
이 제한에 있어서 Oracle 10g 이후가 아니면 순수한 바이너리필드를 사용할수 없다.
(※) 한 번의 기입으로 32KB 를 넘을 수 없지만, RAW 모드로의 기입에 대해서는 여러 차례에 기입을 분할하는 것으로 1행이 32KB 를 초과하는 것이 가능하다


◆DIRECTORY 편
UTL_FILE 패키지를 사용해 PL/SQL에서 파일의 입출력을 실시할 수 있다.Oracle 9i 이후부터는 CREATE DIRECTORY 를 사용하여 유저 단위, 읽어들이기, 쓰기의 제한이 가능하게 되었다. 또한  디렉토리를 추가하고 싶은 경우에도 Oracle 의 재기동이 필요하지 않으므로 다운 타임을 줄일 수 있다

준비작업
■ 디렉토리 작성
디렉토리 파일의 작성 (UTL_FILE_DIR 편과 같다)
디렉토리를 작성할 경우에 OS 의 DBA 인 유저(통상은 oracle)로 디렉토리를 작성한다.
내용은 생략 ⇒ UTL_FILE 의 사용법 (UTL_FILE_DIR 편)의 준비작업을 참조


디렉토리·오브젝트에 의한 파일 액세스


■ 디렉토리의 작성 CRAETE DIRECTORY
Oracle 9i 버젼부터 UTL_FILE 패키지가 CREATE DIRECTORY 에 대응하게 되었다.
디렉토리의 추가에 따르는 재기동도 불필요.
  • 디렉토리 오브젝트의 작성 
    DIRECTORY의 작성은 CREATE DIRECTORY 권한이 필요
       CREATE DIRECTORY recv_area AS '/u05/file_storage/recv_dir';
         CREATE DIRECTORY send_area AS '/u05/file_storage/send_dir';
  • 디렉토리에의 액세스권의 설정
    읽기 권한과 쓰기 권한은 개별적으로 처리한다.
        GRANT READ ON DIRECTORY recv_area TO user_name ;
        GRANT WRITE ON DIRECTORY send_area TO user_name ;
         -- 
         SELECT * FROM ALL_DIRECTORIES ;


■ 파일쓰기 (초기화 파라메터 사용시 )
CREATE OR REPLACE PROCEDURERIVUS.CREATE_DIR_WRITE_SAMPLE
AS
  vHandle   UTL_FILE.FILE_TYPE;
  vDirname  VARCHAR2(250);
  vFilename VARCHAR2(250);
  vOutput   VARCHAR2(32767);
BEGIN
  vDirname  := 'SEND_AREA';-- (주)디렉토리 오브젝트명을 대문자로 지정한다
vFilename := 'test.txt';
  vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'w', 32767);
  vOutput   := 'CREATE DIRECTORY 경유로의 파일출력';
  UTL_FILE.PUT_LINE(vHandle, vOutput);
  UTL_FILE.FCLOSE(vHandle);
EXCEPTION WHENOTHERSTHEN
  UTL_FILE.FCLOSE_ALL;
  RAISE;
END;
/
■ 파일읽기 (초기화 파라메터 사용시 )
CREATE OR REPLACE PROCEDURERIVUS.CREATE_DIR_READ_SAMPLE
AS
  vHandle   UTL_FILE.FILE_TYPE;
  vDirname  VARCHAR2(250);
  vFilename VARCHAR2(250);
  vInput    VARCHAR2(32767);
BEGIN
  vDirname  := 'RECV_AREA';
  vFilename := 'test.txt';
  vHandle   := UTL_FILE.FOPEN(vDirname ,vFilename,'r', 32767);

  BEGIN
    LOOP
      UTL_FILE.GET_LINE(vHandle, vInput,32767);
      DBMS_OUTPUT.PUT_LINE(vInput);
    END LOOP;
  EXCEPTIONWHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('파일의 마지막');
  END;

  UTL_FILE.FCLOSE(vHandle);
EXCEPTION WHENOTHERS THEN
  UTL_FILE.FCLOSE_ALL; RAISE;
END; 
/
주의사항
  • UTL_FILE에서 한번에 입출력할 수 있는 길이는 32767(32K) 바이트 (※) 
  • OPEN 한 파일은 반드시 CLOSE 하도록 예외 처리를 해둔다.UTL_FILE.FCLOSE_ALL 를 사용하면 편리
  • RAW형을 출력할 수 있지만 줄 끝에는 OS 고유의 개행 코드가 반드시 부여된다. 
    Oracle 10g 에서는 wb 에 의한 (RAW 모드) FOPEN 가 서포트되고 있으므로, 그 쪽을 사용하면 문제 없다. 
이 제한에 있어서 Oracle 10g 이후가 아니면 순수한 바이너리필드를 사용할수 없다.
(※) 한 번의 기입으로 32KB 를 넘을 수 없지만, RAW 모드로의 기입에 대해서는 여러 차례에 기입을 분할하는 것으로 1행이 32KB 를 초과하는 것이 가능하다



작성자: 시간:
라벨: , , , ,

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)