오라클 SQL LOADER 테스트

sqlldr userid=scott/tiger control='ulcase1.ctl'

log, bad, discard 옵션을 안줘도 ctl 파일과 같은 이름으로 파일들이 생기네...

ulcase1.ctl

LOAD DATA INFILE * INTO TABLE DEPT_CLONE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO, DNAME, LOC) BEGINDATA 12,RESEARCH,"SARATOGA" 10,"ACCOUNTING",CLEVELAND 11,"ART",SALEM 13,FINANCE,"BOSTON" 21,"SALES",PHILA. 22,"SALES",ROCHESTER 42,"INT'L","SAN FRAN"

sqlldr userid=scott/tiger control='ulcase2.ctl' log='ulcase2.log' bad='ulcase2.bad' discard='ulcase2.dsc'

10을 100으로 고쳐 고의로 에러를 발생시켰다.

ulcase2.ctl

LOAD DATA INFILE * APPEND INTO TABLE DEPT_CLONE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO, DNAME, LOC) BEGINDATA 12,RESEARCH,"SARATOGA" 100,"ACCOUNTING",CLEVELAND 11,"ART",SALEM 13,FINANCE,"BOSTON" 21,"SALES",PHILA. 22,"SALES",ROCHESTER 42,"INT'L","SAN FRAN"

ulcase2.log

SQL*Loader: Release 11.2.0.1.0 - Production on 목 9월 4 11:03:08 2014 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Control File: ulcase2.ctl Data File: ulcase2.ctl Bad File: ulcase2.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 256000 bytes Continuation: none specified Path used: Conventional Table DEPT_CRONE, loaded from every logical record. Insert option in effect for this table: APPEND Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , O(") CHARACTER DNAME NEXT * , O(") CHARACTER LOC NEXT * , O(") CHARACTER Record 2: Rejected - Error on table DEPT_CRONE, column DEPTNO. ORA-01438: 이 열에 대해 지정된 전체 자릿수보다 큰 값이 허용됩니다. Table DEPT_CRONE: 6 Rows successfully loaded. 1 Row not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 49536 bytes(64 rows) Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records read: 7 Total logical records rejected: 1 Total logical records discarded: 0 Run began on 목 9월 04 11:03:08 2014 Run ended on 목 9월 04 11:03:08 2014 Elapsed time was: 00:00:00.19 CPU time was: 00:00:00.08

ulcase2.bad

100,"ACCOUNTING",CLEVELAND

sqlldr errors=1000000 userid=scott/tiger data='emp_data.txt' control='ulcase3.ctl' log='ulcase3.log' bad='ulcase3.bad' discard='ulcase3.dsc'

주의 errors 라는 옵션을 충분히 줘야한단다. 디폴트 50으로 설정되어 있어서 데이터 파일의 적절하지 않은 데이터가 50건이 넘으면 데이터를 넣다가 멈춰버린다.

대량의 데이터 적재 시 반드시 errors의 값을 충분히 설정해 주시기 바란다.

ulcase3.ctl

LOAD DATA REPLACE INTO TABLE EMP_CLONE ( EMPNO POSITION(01:04), ENAME POSITION(06:15) "trim(:ENAME)", JOB POSITION(17:25) "trim(:JOB)", MGR POSITION(27:30) NULLiF MGR=BLANKS, SAL POSITION(32:39) NULLiF SAL=BLANKS, COMM POSITION(41:48) NULLiF COMM=BLANKS, DEPTNO POSITION(50:51) NULLiF EMPNO=BLANKS )

emp_data.txt

7782 CLARK MANAGER 7839 2572.50 10 7839 KING PRESIDENT 5500.00 10 7934 MILLER CLERK 7782 920.00 10 7566 JONES MANAGER 7839 3123.75 20 7499 ALLEN SALESMAN 7698 1600.00 300.00 30 7654 MARTIN SALESMAN 7698 1312.50 1400.00 30 7658 CHAN ANALYST 7566 3450.00 20