Oracle UTL_FILE을 이용한 데이터 Unloading의 간단한 예제

간단히 UTL_FILE을 이용한 scott.emp 테이블 데이터 unloading 하기

create or replace procedure utl_file_test_write (

  filename   in varchar2

  )

is

   v_file            UTL_FILE.FILE_TYPE;

   v_text            VARCHAR2 (20);

   v_filename        VARCHAR2 (200);

   v_delimitedchar   CHAR (1);

BEGIN

   v_delimitedchar := CHR (124);

   v_file := UTL_FILE.FOPEN ('DATA_PUMP_DIR', filename, 'W');

   FOR rec IN

      (SELECT   empno,ename,job,mgr,hiredate,sal,comm,deptno FROM scott.emp)

   LOOP

      UTL_FILE.PUT_LINE (v_file,rec.empno|| v_delimitedchar|| rec.ename||v_delimitedchar|| 

                         rec.job||v_delimitedchar|| rec.mgr||v_delimitedchar||rec.hiredate||

                         v_delimitedchar|| rec.sal ||v_delimitedchar|| rec.comm|| v_delimitedchar|| 

                         rec.deptno  );

   END LOOP; 

   UTL_FILE.FCLOSE (v_file);

END;

/

수행 및 결과

SQL> exec utl_file_test_write ('TEST01');

SQL> !cat /oracle_home/TEST01

7369|SMITH|CLERK|7902|1980-12-17|800||20

7499|ALLEN|SALESMAN|7698|1981-02-20|1600|300|30

7521|WARD|SALESMAN|7698|1981-02-22|1250|500|30

7566|JONES|MANAGER|7839|1981-04-02|2975||20

7654|MARTIN|SALESMAN|7698|1981-09-28|1250|1400|30

7698|BLAKE|MANAGER|7839|1981-05-01|2850||30

7782|CLARK|MANAGER|7839|1981-06-09|2450||10

7788|SCOTT|ANALYST|7566|1982-12-09|3000||20

7839|KING|PRESIDENT||1981-11-17|5000||10

7844|TURNER|SALESMAN|7698|1981-09-08|1500|0|30

7876|ADAMS|CLERK|7788|1983-01-12|1100||20

7900|JAMES|CLERK|7698|1981-12-03|950||30

7902|FORD|ANALYST|7566|1981-12-03|3000||20