외부파일(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를 의미합니다.