Oracle - Procedure 예제_외부파일_읽고_쓰기

[ Oracle - Procedure 예제_외부파일_읽고_쓰기 ]

 

외부파일(txt)를 읽고  쓰기

◈ 테이블의 데이터를 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를 의미합니다.