14S_SQL*Loader (by jiginet) [2014.11.28]
1. 데이터 로딩방식
- Conventional path : SQL insert를 사용하는 방식, 리두/언두 생성됨, 속도 느림
- Direct paht : 데이터베이스 블록에 데이터를 직접 쓰는 방식, 리두/언두 생성안됨, 속도 빠름
2.실행화면
3. 컨트롤파일(control file)
| LOAD DATA | 새 데이터 로드가 시작됨을 의미 |
| INFILE * | \* : 컨트롤 파일에 입력할 데이터가 있음, 파일명 : 외부 데이터 파일 지정 |
| BADFILE 'TEST.BAD' | 거부된 레코드를 배치할 파일명 지정(레코드 형식, 제약조건 등이 맞지 않는 경우) |
| DISCARDFILE 'TEST.DSC' | 폐기된 레코드를 배치할 파일명 지정(WHEN 조건 등에 맞지 않아 입력시 제외 된 파일) |
| REPLACE | REPLACE : 데이터 삭제후 삽입(DELETE), APPEND : 기존 데이터에 추가, INSERT : 빈 테이블에 데이터 추가(기본값), TRUNCATE : 데이터 삭제 후 삽입 |
| INTO TABLE DEPT | 데이터를 저장할 테이블 지정 |
| FIELDS TERMINATED BY ',' | 데이터 필드의 종결문자 지정 |
| (DEPTNO, DNAME, LOC ) | 입력 필드명 지정 |
| BEGINDATA | 입력할 데이터 시작 |
| 1O, Sales, virginia | 입력데이터(기본데이터 타입:char(255)) |
| 20, Accounting, virginia | |
| 30, Consulting, virginia | |
| 40, Finance, virginia |
4. 테스트1(INSERT 오류)
SQL> create table dept
2 ( dept number(2) constraint dept_pk primary key,
3 dname varchar2(14),
4 loc varchar2(13)
5 )
6 /
테이블이 생성되었습니다.
[oracle@mydream test]$ cat demo.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10, sales, test
20, Accounting, test
30, Consulting, test
40, Finance, test
[oracle@mydream ~]$ sqlldr genie/genie control=demo.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on 금 12월 26 12:03:18 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
SQL*Loader-601: INSERT 옵션을 사용하려면 테이블이 비어 있어야 합니다. DEPT 테이블에 오류
[oracle@mydream test]$ cat demo.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10, sales, test
20, Accounting, test
30, Consulting, test
40, Finance, test
[oracle@mydream test]$ sqlldr genie/genie control=demo.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 14:13:30 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 4
[oracle@mydream test]$ cat demo.log
SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 14:13:30 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
제어 파일: demo.ctl
데이터 파일: demo.ctl
부적합한 파일: demo.bad
폐기 파일: 지정 사항 없음
(모든 폐기된 레코드 허용)
로드할 건수: ALL
생략 건수: 0
허용 오류수: 50
바인드 배열: 64 행, 최대 256000 바이트
계속: 지정 사항 없음
사용된 경로: 규약
테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: TRUNCATE
열 이름 위치 Len Term Encl 데이터유형
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER
테이블 DEPT:
4 행 로드되었습니다.
데이터 오류 때문에 0 행(이)가 로드되지 않았습니다
모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다
바인드 배열에 할당된 영역: 49536바이트(64 행)
읽기 버퍼 바이트: 1048576
생략된 논리 레코드의 합계: 0
읽어낸 논리 레코드의 합계: 4
거부된 논리 레코드의 합계: 0
폐기된 논리 레코드의 합계: 0
월 12월 29 14:13:30 2014에 실행 개시
월 12월 29 14:13:30 2014에 실행 종료
경과 시간: 00:00:00.07
CPU 시간: 00:00:00.00
4. 테스트2(최대 길이 초과 오류)
[oracle@mydream test]$ cat demo2.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC )
BEGINDATA
10, sales, test
20, Accounting, test
30, Consulting, test
40, Finance, ****************************************************************************************************************************************************************************************************************************************************************
SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 14:18:42 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
제어 파일: demo2.ctl
데이터 파일: demo2.ctl
부적합한 파일: demo2.bad
폐기 파일: 지정 사항 없음
(모든 폐기된 레코드 허용)
로드할 건수: ALL
생략 건수: 0
허용 오류수: 50
바인드 배열: 64 행, 최대 256000 바이트
계속: 지정 사항 없음
사용된 경로: 규약
테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: TRUNCATE
열 이름 위치 Len Term Encl 데이터유형
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER
레코드 4: 기각됨 - 테이블 DEPT, 열 LOC에 오류
데이터 파일의 필드가 최대 길이를 초과했습니다
테이블 DEPT:
3 행 로드되었습니다.
데이터 오류 때문에 1 행(이)가 로드되지 않았습니다
모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다
바인드 배열에 할당된 영역: 49536바이트(64 행)
읽기 버퍼 바이트: 1048576
생략된 논리 레코드의 합계: 0
읽어낸 논리 레코드의 합계: 4
거부된 논리 레코드의 합계: 1
폐기된 논리 레코드의 합계: 0
월 12월 29 14:18:42 2014에 실행 개시
월 12월 29 14:18:42 2014에 실행 종료
경과 시간: 00:00:00.07
CPU 시간: 00:00:00.00
[oracle@mydream test]$ cat demo2_2.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY ','
(DEPTNO, DNAME, LOC char(1000) )
BEGINDATA
10, sales, test
20, Accounting, test
30, Consulting, test
40, Finance, ****************************************************************************************************************************************************************************************************************************************************************
[oracle@mydream test]$ sqlldr genie/genie control=demo2_2.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 14:27:08 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 4
[oracle@mydream test]$ cat demo2_2.log
SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 14:29:59 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
제어 파일: demo2_2.ctl
데이터 파일: demo2_2.ctl
부적합한 파일: demo2_2.bad
폐기 파일: 지정 사항 없음
(모든 폐기된 레코드 허용)
로드할 건수: ALL
생략 건수: 0
허용 오류수: 50
바인드 배열: 64 행, 최대 256000 바이트
계속: 지정 사항 없음
사용된 경로: 규약
테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: TRUNCATE
열 이름 위치 Len Term Encl 데이터유형
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT 1000 , CHARACTER
테이블 DEPT:
4 행 로드되었습니다.
데이터 오류 때문에 0 행(이)가 로드되지 않았습니다
모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다
바인드 배열에 할당된 영역: 97152바이트(64 행)
읽기 버퍼 바이트: 1048576
생략된 논리 레코드의 합계: 0
읽어낸 논리 레코드의 합계: 4
거부된 논리 레코드의 합계: 0
폐기된 논리 레코드의 합계: 0
월 12월 29 14:29:59 2014에 실행 개시
월 12월 29 14:29:59 2014에 실행 종료
경과 시간: 00:00:00.08
CPU 시간: 00:00:00.00
6. 테스트3(구분자를 포함하는 데이터 로드 방법 )
-- 콤마(,)를 구분자로 하고 문자열은 따옴표(")로 감싼다는 의미
[oracle@mydream test]$ cat demo3.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC )
BEGINDATA
10, sales, "test, TEST"
20, Accounting, "test, ""TEST"""
30, Consulting, test
40, Finance, AAAAA
[oracle@mydream test]$ sqlldr genie/genie control=demo3.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 17:15:38 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 4
SQL > select * from dept;
DEPTNO DNAME LOC
---------- -------------- --------------------
10 sales test, TEST
20 Accounting test, "TEST"
30 Consulting test
40 Finance AAAAA
-- 데이터 사이에 tab, 스페이스(공백), 개행문자 전까지 문자열을 파싱함
-- 10 : 탭 적용
-- 20 : 공백 2칸 적용
-- 30 : 공백 1칸 적용
[oracle@mydream test]$ cat demo4_2.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY WHITESPACE
(DEPTNO, DNAME, LOC )
BEGINDATA
10 sales TEST
20 business aaaa
30 aaa bbb
SQL> /
DEPTNO DNAME LOC
---------- -------------- --------------------
10 sales TEST
20 business aaaa
30 aaa bbb
-- X'09'는 16진수 포멧을 사용한 탭문자(ASCII 9는 탭문자)
-- 10 : 탭 2번 적용
-- 20 : 탭 1번 적용
-- 30 : 공백 1칸 적용
[oracle@mydream test]$ cat demo5.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY X'09'
(DEPTNO, DNAME, LOC )
BEGINDATA
10 sales TEST
20 business aaaa
30 aaa bbb
[oracle@mydream test]$ sqlldr genie/genie control=demo5.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 17:33:23 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 3
SQL> /
DEPTNO DNAME LOC
---------- -------------- --------------------
10 sales <-- 탭 2번 적용
20 business aaaa <-- 탭 1번 적용
[oracle@mydream test]$ cat demo5.log
SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 17:39:35 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
제어 파일: demo5.ctl
데이터 파일: demo5.ctl
부적합한 파일: demo5.bad
폐기 파일: 지정 사항 없음
(모든 폐기된 레코드 허용)
로드할 건수: ALL
생략 건수: 0
허용 오류수: 50
바인드 배열: 64 행, 최대 256000 바이트
계속: 지정 사항 없음
사용된 경로: 규약
테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: TRUNCATE
열 이름 위치 Len Term Encl 데이터유형
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * WHT CHARACTER
DNAME NEXT * WHT CHARACTER
LOC NEXT * WHT CHARACTER
레코드 3: 기각됨 - 테이블 DEPT, 열 DNAME에 오류
논리 레코드가 종료하기 전에 열을 찾지 못했습니다 (TRAILING NULLCOLS 사용)
테이블 DEPT:
2 행 로드되었습니다.
데이터 오류 때문에 1 행(이)가 로드되지 않았습니다
모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다
바인드 배열에 할당된 영역: 49536바이트(64 행)
읽기 버퍼 바이트: 1048576
생략된 논리 레코드의 합계: 0
읽어낸 논리 레코드의 합계: 3
거부된 논리 레코드의 합계: 1
폐기된 논리 레코드의 합계: 0
월 12월 29 17:39:35 2014에 실행 개시
월 12월 29 17:39:35 2014에 실행 종료
경과 시간: 00:00:00.06
CPU 시간: 00:00:00.00
-- filler라는 키워드를 사용해 컬럼매핑을 건너뛸 수 있다.
-- 아래 데이터는 탭을 2회 사용하였음(2번 컬럼, 4번 컬럼의 매핑을 건너뛰겠다는 의미)
[oracle@mydream test]$ cat demo6.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
TRUNCATE
FIELDS TERMINATED BY x'09'
(DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC )
BEGINDATA
10 sales TEST
SQL> /
DEPTNO DNAME LOC
---------- -------------- --------------------
10 sales TEST
7. 테스트4( 고정길이 포맷 데이터 로드 방법)
[oracle@mydream test]$ cat demo7.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(
DEPTNO position(1:2),
DNAME position(3:16),
loc position(17:29)
)
BEGINDATA
10Accounting Virginia,Korea
SQL> /
DEPTNO DNAME LOC
---------- -------------- --------------------
10 Accounting Virginia,Kore
-- position의 위치는 자유롭게 앞뒤로 움직일 수 있다.
-- entire_line 컬럼 참조
[oracle@mydream test]$ cat demo8.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(
DEPTNO position(1:2),
DNAME position(3:16),
loc position(17:30),
entire_line position(1:29)
)
BEGINDATA
10Accounting Virginia,Korea
SQL> /
DEPTNO DNAME LOC ENTIRE_LINE
---------- -------------- -------------------- -----------------------------
10 Accounting Virginia,Korea 10Accounting Virginia,Kore
-- *는 컨트롤 파일의 앞 필드가 끝난 위치를 기준으로 1바이트 추가된 지점을 자동으로 시작점으로 인식
[oracle@mydream test]$ cat demo9.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(
DEPTNO position(1:2),
DNAME position(*:16),
loc position(*:30),
entire_line position(1:29)
)
BEGINDATA
10Accounting Virginia,Korea
SQL> /
DEPTNO DNAME LOC ENTIRE_LINE
---------- -------------- -------------------- -----------------------------
10 Accounting Virginia,Korea 10Accounting Virginia,Kore
-- *+2 는 앞 시작점에서 2칸을 더 건너띄겠다는 의미
-- loc 앞 2자리가 짤렸음을 관찰
[oracle@mydream test]$ cat demo10.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(
DEPTNO position(1:2),
DNAME position(*:16),
loc position(*+2:30),
entire_line position(1:29)
)
BEGINDATA
10Accounting Virginia,Korea
SQL> /
DEPTNO DNAME LOC ENTIRE_LINE
---------- -------------- -------------------- -----------------------------
10 Accounting rginia,Korea 10Accounting Virginia,Kore
-- 컬럼의 길이를 직접 지정하여 사용할 수도 있음
[oracle@mydream test]$ cat demo11.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(
DEPTNO position(1) char(2),
DNAME position(*) char(14),
loc position(*) char(14),
entire_line position(1) char(29)
)
BEGINDATA
10Accounting Virginia,Korea
SQL> /
DEPTNO DNAME LOC ENTIRE_LINE
---------- -------------- -------------------- -----------------------------
10 Accounting Virginia,Korea 10Accounting Virginia,Kore
8. 테스트5(날짜 데이터 로드방법)
-- date 타입으로 필드를 지정함
[oracle@mydream test]$ cat demo12.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
( DEPTNO
, DNAME
, LOC
, LAST_UPDATED DATE 'dd/mm/yyyy'
)
BEGINDATA
10,Accounting,Virginia,1/5/2000
20,Sale,Virginia,21/6/1999
QL> /
DEPTNO DNAME LOC ENTIRE_LINE LAST_UPD
---------- -------------- ------------------------------ ----------------------------- --------
10 Accounting Virginia 00/05/01
20 Sale Virginia 99/06/21
9. 테스트7(함수를 사용한 데이터 로드 방법)
-- sql 엔진을 거쳐야 하기 때문에 direct path 방식으로는 처리할 수 없음
[oracle@mydream test]$ cat demo13.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
( DEPTNO
, DNAME "upper(:dname)"
, LOC "upper(:loc)"
, LAST_UPDATED DATE 'dd/mm/yyyy'
)
BEGINDATA
10,Accounting,Virginia,1/5/2000
20,Sale,Virginia,21/6/1999
30,Consulting,virginia,5/1/2000
SQL> /
DEPTNO DNAME LOC ENTIRE_LINE LAST_UPD
---------- -------------- ------------------------------ ----------------------------- --------
10 ACCOUNTING VIRGINIA 00/05/01
20 SALE VIRGINIA 99/06/21
30 CONSULTING VIRGINIA
-- 입력할 데이터에 존재하지 않는 컬럼을 사용할 경우
[oracle@mydream test]$ cat demo14.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
( DEPTNO
, DNAME "upper(:dname)"
, LOC "upper(:loc)"
, LAST_UPDATED DATE 'dd/mm/yyyy'
, ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Accounting,Virginia,1/5/2000
20,Sale,Virginia,21/6/1999
30,Consulting,virginia,5/1/2000
[oracle@mydream test]$ cat demo14.log
SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 18:57:36 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
제어 파일: demo14.ctl
데이터 파일: demo14.ctl
부적합한 파일: demo14.bad
폐기 파일: 지정 사항 없음
(모든 폐기된 레코드 허용)
로드할 건수: ALL
생략 건수: 0
허용 오류수: 50
바인드 배열: 64 행, 최대 256000 바이트
계속: 지정 사항 없음
사용된 경로: 규약
테이블 DEPT, 로드되었습니다 개개의 논리 레코드로부터
이 테이블에 적당한 Insert 옵션: REPLACE
열 이름 위치 Len Term Encl 데이터유형
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
열에 대한 SQL 문자열 : "upper(:dname)"
LOC NEXT * , CHARACTER
열에 대한 SQL 문자열 : "upper(:loc)"
LAST_UPDATED NEXT * , DATE dd/mm/yyyy
ENTIRE_LINE NEXT * , CHARACTER
열에 대한 SQL 문자열 : ":deptno||:dname||:loc||:last_updated"
레코드 1: 기각됨 - 테이블 DEPT, 열 ENTIRE_LINE에 오류
논리 레코드가 종료하기 전에 열을 찾지 못했습니다 (TRAILING NULLCOLS 사용)
레코드 2: 기각됨 - 테이블 DEPT, 열 ENTIRE_LINE에 오류
논리 레코드가 종료하기 전에 열을 찾지 못했습니다 (TRAILING NULLCOLS 사용)
레코드 3: 기각됨 - 테이블 DEPT, 열 ENTIRE_LINE에 오류
논리 레코드가 종료하기 전에 열을 찾지 못했습니다 (TRAILING NULLCOLS 사용)
테이블 DEPT:
0 행 로드되었습니다.
데이터 오류 때문에 3 행(이)가 로드되지 않았습니다
모든 WHEN절이 실패하여 0 행(이)가 로드되지 않았습니다
모든 필드가 NULL이어서 0 행(이)가 로드되지 않았습니다
바인드 배열에 할당된 영역: 82560바이트(64 행)
읽기 버퍼 바이트: 1048576
생략된 논리 레코드의 합계: 0
읽어낸 논리 레코드의 합계: 3
거부된 논리 레코드의 합계: 3
폐기된 논리 레코드의 합계: 0
월 12월 29 18:57:36 2014에 실행 개시
월 12월 29 18:57:36 2014에 실행 종료
경과 시간: 00:00:00.04
CPU 시간: 00:00:00.00
-- trailing nullcols 를 명시함
[oracle@mydream test]$ cat demo15.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME "upper(:dname)"
, LOC "upper(:loc)"
, LAST_UPDATED DATE 'dd/mm/yyyy'
, ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Accounting,Virginia,1/5/2000
20,Sale,Virginia,21/6/1999
30,Consulting,virginia,5/1/2000
SQL> /
DEPTNO DNAME LOC ENTIRE_LINE LAST_UPD
---------- -------------- ------------------------------ ----------------------------- --------
10 ACCOUNTING VIRGINIA 10AccountingVirginia1/5/2000 00/05/01
20 SALE VIRGINIA 20SaleVirginia21/6/1999 99/06/21
30 CONSULTING VIRGINIA 30Consultingvirginia5/1/2000 00/01/05
-- 날짜 형태 변경
SQL> alter session set nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
세션이 변경되었습니다.
-- sql loader는 insert문에 바인드 변수를 적용한 것과 동일하게 사용가능(아래와 같이 다양한 종류의 쿼리가 사용가능함)
[oracle@mydream test]$ cat demo16.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME "upper(:dname)"
, LOC "upper(:loc)"
, LAST_UPDATED
"case
when length(:last_updated) > 9 then to_date(:last_updated, 'yyyy/mm/dd hh24:mi:ss')
when instr(:last_updated, ':') > 0 then to_date(:last_updated, 'hh24:mi:ss')
else to_date(:last_updated, 'yyyy/mm/dd')
end"
)
BEGINDATA
10,Accounting,Virginia,2015/01/01 11:12:15
20,Sale,Virginia, 02:23:54
30,Consulting,virginia,2014/12/31 11:50:52
40,Finance,virginia,2013/12/22
SQL> SELECT * FROM DEPT;
DEPTNO DNAME LOC ENTIRE_LINE LAST_UPDATED
---------- -------------- ------------------------------ ----------------------------- -------------------
10 ACCOUNTING VIRGINIA 2015/01/01 11:12:15
20 SALE VIRGINIA 2014/12/01 02:23:54
30 CONSULTING VIRGINIA 2014/12/31 11:50:52
40 FINANCE VIRGINIA 2013/12/22 00:00:00
10. 테스트8( 개행문자를 포함한 데이터 로드 방법 )
개행문자 대신 다른 문자를 사용
SQL> alter table dept add comments varchar2(4000);
테이블이 변경되었습니다.
[oracle@mydream test]$ cat demo17.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME "upper(:dname)"
, LOC "upper(:loc)"
, COMMENTS "replace(:comments, '\\n', chr(10))"
)
BEGINDATA
10,Accounting,Virginia, "개행문자 테스트\n 중입니다."
20,Sale,Virginia, "개행문자 테스트\n 중입니다."
30,Consulting,virginia, "개행문자 테스트\n\n 중입니다."
40,Finance,virginia, "개행문자 테스트\n\n\n 중입니다."
SQL> /
DEPTNO DNAME LOC ENTIRE_LIN LAST_UPD COMMENTS
---------- -------------- -------------------- ---------- -------- ------------------------------
10 ACCOUNTING VIRGINIA "개행문자 테스트
중입니다."
20 SALE VIRGINIA "개행문자 테스트
중입니다."
30 CONSULTING VIRGINIA "개행문자 테스트
중입니다."
40 FINANCE VIRGINIA "개행문자 테스트
중입니다."
FIX 속성사용
[oracle@mydream test]$ cat demo18.ctl
LOAD DATA
INFILE demo18.dat "fix 80"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME "upper(:dname)"
, LOC "upper(:loc)"
, COMMENTS
)
-- 데이터 파일
[oracle@mydream test]$ cat demo18.dat
10,salels,viginia,This is the sales\nOffice in virginia
20,Accounting,viginia,This is the Accounting\nOffice in Virginia
30,Consulting,viginia,This is the Consulting\nOffice in Virginia
40,Finance,viginia,This is the Finance\nOffice in VIrginia
[oracle@mydream test]$ od -c -w10 -v demo18.dat
0000000 1 0 , s a l e l s ,
0000012 v i g i n i a , T h
0000024 i s i s t h e
0000036 s a l e s \ n O f f
0000050 i c e i n v i r
0000062 g i n i a \n 2 0 , A
0000074 c c o u n t i n g ,
0000106 v i g i n i a , T h
0000120 i s i s t h e
0000132 A c c o u n t i n g
0000144 \ n O f f i c e i
0000156 n V i r g i n i a
0000170 \n 3 0 , C o n s u l
0000202 t i n g , v i g i n
0000214 i a , T h i s i s
0000226 t h e C o n s u
0000240 l t i n g \ n O f f
0000252 i c e i n V i r
0000264 g i n i a \n 4 0 , F
0000276 i n a n c e , v i g
0000310 i n i a , T h i s
0000322 i s t h e F i n
0000334 a n c e \ n O f f i
0000346 c e i n V I r g
0000360 i n i a \n
0000365
-- 입력도중 실패
[oracle@mydream test]$ sqlldr genie/genie control=demo18.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 22:06:32 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 3
SQL*Loader-501: 파일 (demo18.dat)을 읽을 수 없습니다
SQL*Loader-566: 데이터 파일의 끝에서 부분 레코드 발견
SQL*Loader-2026: SQL Loader가 계속할 수 없으므로 로드가 중단되었습니다.
SQL> /
DEPTNO DNAME LOC ENTIRE_LIN LAST_UPD COMMENTS
---------- -------------- -------------------- ---------- -------- ------------------------------
10 SALELS VIGINIA This is the sales\nOffice in v
irginia
20
-- 문자열 끝에 공백이 존재하여 trim 함수 적용이 필요함
주의점
줄의 종결 표시는 플랫폼에 따라 다르다.
유닉스 : \n (SQL에서 CHR(10))
윈도우 : \r\n (SQL에서 CHR(13) || CHR(10))
FIX 방법을 사용한다면 반드시 같은 플랫폼에서 파일을 생성하고 로드해야만 한다
11. 테스트9( VAR 속성사용 )
[oracle@mydream test]$ cat demo19.ctl
LOAD DATA
INFILE demo19.dat "var 3"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME "upper(:dname)"
, LOC "upper(:loc)"
, COMMENTS
)
-- 유닉스에서는 개행문자가 1byte, 윈도우에서는 2byte 이므로, 데이터를 생성하는 운영체제에 따라 크기를 다르게 해줘야 함
-- (윈도우에서는 각행을 056, 066, 066, 060 으로 변경해야 함)
[oracle@mydream test]$ cat demo19.dat
05510,Sales,Virginia,This is the Sales
Office in Virginia
06520,Accounting,viginia,This is the Accounting
Office in Virginia
06530,Consulting,viginia,This is the Consulting
Office in Virginia
05940,Finance,viginia,This is the Finance
Office in Virginia
-- 데이터의 길이 지정이 잘못될 경우 데이터 입력에 오류가 발생
[oracle@mydream test]$ sqlldr genie/genie control=demo19.ctl
SQL*Loader: Release 11.2.0.1.0 - Production on 월 12월 29 23:47:22 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
커밋 시점에 도달 - 논리 레코드 개수 3
SQL*Loader-501: 파일 (demo19.dat)을 읽을 수 없습니다
SQL*Loader-566: 데이터 파일의 끝에서 부분 레코드 발견
SQL*Loader-2026: SQL Loader가 계속할 수 없으므로 로드가 중단되었습니다.
-- 데이터가 3개행만 입력되었음
SQL> /
DEPTNO DNAME LOC ENTIRE_LINE LAST_UPD COMMENTS
---------- -------------- --------------- --------------- -------- ------------------------------
10 SALES VIRGINIA This is the Sales
Office in Virginia
20 ACCOUNTING VIGINIA This is the Accounting
Office in Virginia
30 CONSULTING VIGINIA This is the Consulting
Office in Virginia
12. 테스트10( str 속성 사용)
-- 파이프(|)를 문장의 끝으로 인식하고자 할 때
SQL> select utl_raw.cast_to_raw('|'||chr(10)) from dual;
UTL_RAW.CAST_TO_RAW('|'||CHR(10))
--------------------------------------------------------------------------------------------------------------------------------
7C0A
-- 위에서의 7C0A를 문자의 끝으로 인식하게 한다.
[oracle@mydream test]$ cat demo20.ctl
LOAD DATA
INFILE demo20.dat "str X'7C0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( DEPTNO
, DNAME "upper(:dname)"
, LOC "upper(:loc)"
, COMMENTS
)
[oracle@mydream test]$ cat demo20.dat
10,Sales,Virginia,This is the Sales
Office in Virginia|
20,Accounting,viginia,This is the Accounting
Office in Virginia|
30,Consulting,viginia,This is the Consulting
Office in Virginia|
40,Finance,viginia,This is the Finance
Office in VIrginia|
SQL> /
DEPTNO DNAME LOC ENTIRE_LINE LAST_UPD COMMENTS
---------- -------------- --------------- --------------- -------- ------------------------------
10 SALES VIRGINIA This is the Sales
Office in Virginia
20 ACCOUNTING VIGINIA This is the Accounting
Office in Virginia
30 CONSULTING VIGINIA This is the Consulting
Office in Virginia
40 FINANCE VIGINIA This is the Finance
Office in VIrginia
참고
윈도우에서 생성한 파일의 경우는 아래처럼 사용함
SQL> select utl_raw.cast_to_raw('|'||chr(13)||chr(10)) str_raw from dual;
STR_RAW
--
7C0D0A
13. 테스트11( PL/SQL로 LOB 데이터 로딩 )
--DBMS_LOB 패키지(LoadFromFile, LoadBLOBFromFile, LoadCLOBFromFile)를 이용
-- 디렉토리 생성
[oracle@mydream app]$ mkdir /app/demo_dir
-- 디렉토리 객체 생성
-- 대문자 디렉토리 생성
SQL> create or replace directory dir1 as '/app/demo_dir/';
디렉토리가 생성되었습니다.
-- 소문자 디렉토리 생성
SQL> create or replace directory "dir2" as '/app/demo_dir/';
디렉토리가 생성되었습니다.
-- 데모 테이블 생성
SQL> create table demo
2 ( id int primary key,
3 theclob clob
4 )
5 /
테이블이 생성되었습니다.
-- 테스트 파일 생성
SQL> !echo 'Hello World!!' > /app/demo_dir/test.txt
-- 프로시저 생성
SQL> DECLARE
2 L_CLOB CLOB;
3 L_BFILE BFILE;
4 BEGIN
5 INSERT INTO DEMO VALUES (1, EMPTY_CLOB()) RETURNING THECLOB INTO L_CLOB; -- CLOB을 EMPTY_CLOB()로 초기화
6
7
8 L_BFILE := BFILENAME('DIR1', 'test.txt'); -- BFIEL 객체 생성, 'dir2'의 경우에는 소문자로도 가능
9 DBMS_LOB.FILEOPEN(L_BFILE); -- LOB를 OPEN
10 DBMS_LOB.LOADFROMFILE(L_CLOB, L_BFILE, DBMS_LOB.GETLENGTH(L_BFILE)); -- LOB locator에 운영체제파일 /tmp/test.txt의 내용 로드
11 12 DBMS_LOB.FILECLOSE(L_BFILE); -- BFILE CLOSE
13 END;
14 /
PL/SQL 처리가 정상적으로 완료되었습니다.
-- 글자가 깨져서 들어감. 원인은 못 밝힘 ^^;
-- export LANG=C로 해보았으나 한글, 영문 모두 깨짐
SQL> select * from demo;
ID THECLOB
---------- --------------------------------------------------------------------------------
3 ？？漠？？？？
5 ？？漠？？？℡？
1 ？？漠？？？？
2 ？？漠？？？？
4 쟑뇛？？뫆깈敬？？？？℡