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
'Oracle Database' 카테고리의 다른 글
| Oracle 11g에서 특정 SQL에 대해서 sql_trace 걸기 (0) | 2011.01.27 |
|---|---|
| Oracle의 특정 process가 CPU를 많이 먹을때: kill 대신 suspend (0) | 2011.01.05 |
| Oracle 11g의 새로운 HINT : IGNORE_ROW_ON_DUPKEY_INDEX (0) | 2010.12.23 |
| Oracle Interval Partition drop할때 ORA-14758 발생 (0) | 2010.12.08 |
| Oracle Global Temporary Table (0) | 2010.12.08 |