간단히 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


UTL_FILE을 이용해 file을 loading 하는 아주 간단한 example. 

declare
   fp utl_file.file_type;
   str varchar2(48);
   VAL SCOTT.TEST%ROWTYPE;   
begin
   fp := utl_file.fopen('DATA_PUMP_DIR','sample_data.txt','r');
   loop
    utl_file.get_line(fp,str);
    VAL.CUST_ID := TRIM(SUBSTR(str,1,9));
    VAL.FC_ID := TRIM(SUBSTR(str,11,20));
    insert into SCOTT.TEST values(VAL.CUST_ID,VAL.FC_ID);
   end loop;
exception
when no_data_found then
   commit;
end;
/


+ Recent posts