SQL*Loader는 외부 화일의 데이타를 ORACLE 데이타베이스의 테이블에 넣기 위한 유틸리티이다.
SQL*Loader를 사용하려면 외부 데이타 화일과 콘트롤화일이 필요하다.
콘트롤화일이라고 하는 것은 로드하는 데이타의 정보를 저장한 화일인데 예를 들면 다음과 같다.
load data -> 콘트롤화일의 앞에는 반드시 필요함
infile sample.dat -> 외부 데이타 화일을 지정
replace -> 테이블에 데이타 넣는 방법 지정
into table TABLE_NAME -> 데이타를 로드하는 테이블을 지정
fields terminated by ',' -> 데이타 필드의 종결문자 지정
(a integer external,b char) -> 테이블의 열 및 외부데이타 화일의 데이타 형을 지정
*참고로 Replace 외에 다음의 옵션이 가능하다.
replace -> 테이블의 기존 행을 모두 삭제(delete)하고 Insert(7.0 에서는 truncate 함)
append -> 새로운 행을 기존의 데이타에 추가
insert -> 비어 있는 테이블에 넣을 때
truncate -> 테이블의 기존 데이타를 모두 truncate 하고 인서트(7.0에는 없음)
SQL*Loader를 실행하면 아래의 화일이 생성된다.
- 로드 작업의 결과와 에러 등을 기록한 로그화일(확장자는 log)
- 에러 때문에 로드가 안된 레코드를 저장한 화일(확장자는 bad)
- 사용자의 선택 기준에 적합하지 않은 레코드를 저장한 파일(discard 화일)
이것은 discardfile 옵션으로 별도로 지정해야 생성된다.
실행 방법
$ sqlload scott/tiger control=sample.ctl data=sample.dat
1. 임의의 컬럼에 문자열(값)을 입력한 경우
[ 테이블 구조 ]
create table cons_test (a number, b number, c number, d varchar(10))
[ 콘트롤 화일 ]
load data
infile cons.dat
replace
into table cons_test
fields terminated by ','
(a integer external,
b integer external,
c CONSTANT '100',
d char)
[ 외부 데이타 파일 cons.dat의 내용 ]
1,2,DATA
2,4,DATA2
[ 검색결과 ]
SQL> select * from cons_test;
A B C D
------- ----- ------ ------------
1 2 100 DATA
2 4 100 DATA2
2. 로드한 때의 날짜를 넣고 싶은 경우
[ 테이블 구조 ]
create table sysdatetb (a number, b date, c varchar(10))
[ 콘트롤 화일 ]
load data
infile sysdate2.dat
replace
into table sysdatetb
fields terminated by ','
(a integer external,
b sysdate,
c char(10))
[ 외부 데이타 화일 ]
111,STRINGS
222,STRINGS2
[ 검색결과 ]
SQL> select * from sysdatetb;
A B C
------- --------- ----------
111 13-MAY-94 STRING
222 13-MAY-94 STRING2
SYSDATE는 Conventional Path의 경우는 실행시에 삽입된 각각의 레코드 배열마다, Direct Path의 경우는 로드된 각각의 레코드의 블록마다 사용된다. 새로운 값으로 변경된다.
3. SEQUENCE를 임의의 수에서 임의의 수만큼 붙이고 싶은 경우
[ 테이블 구조 ]
create table seqtb (a varchar(10), b number, c varchar(10))
[ 콘트롤 화일 ]
load data
infile seq.dat
replace
into table seqtb
fields terminated by ','
(a char,
b sequence(100,5),
c char)
[ 외부 데이타 화일 ]
1,a
2,b
3,c
[ 검색결과 ]
SQL> select * from seqtb;
A B C
----- ---- --------
1 100 a
2 105 b
3 110 c
4. 논리 레코드를 구성하는 물리 레코드가 여러줄로 구성된 경우(물리 레코드의 1바이트째로 판단되는 경우)
[ 테이블 구조 ]
create table conti_test (a varchar(10), b varchar(10), c varchar(10))
[ 콘트롤 화일 ]
load data
infile conti.dat
replace
continueif this (1) = '%'
into table conti_test
fields terminated by ','
(a char,
b char,
c char)
[ 외부 데이타 화일 ]
%1,
%2,
3
%A,B
,C
%a,b
%c
%d
,ef
[ 검색결과 ]
SQL> select * from conti_test;
A B C
---- ------ --------
1 2 3
A B C
a bcd ef
이 예의 경우 1바이트 째가 계속 행의 체크를 위해서 사용되기 때문에 실제의 데이타를 1바이트 째부터 시작해서는 안된다.
위의 경우, 레코드의 선두 바이트가 '%'일 때 다음의 레코드가 연결된다.
5. 외부데이타 화일의 물리 레코드가 복수 레코드로 구성된 경우(구성하는 물리 레코드 수가 모두 일정한 경우)
[ 테이블 구조 ]
create table con_test (a varchar(10), b varchar(10), c varchar(10))
[ 콘트롤 화일 ]
load data
infile conti.dat
replace
concatenate 2
into table con_test
fields terminated by ','
(a char,
b char,
c char)
[ 외부 데이타 화일 ]
1,2,
3
a,b,
c
A,
B,C
[ 검색결과 ]
SQL> select * from con_test;
A B C
----- ------ -------
1 2 3
a b c
A B C
6. 포지션 지정시 char형 전후의 블랭크도 로드하고 싶은 경우
[ 테이블 구조 ]
create table pretb (a varchar(10), b varchar(10), c varchar(10))
[ 콘트롤 화일 ]
load data
infile pre.dat
preserve blanks
into table pretb
(a position(01:05) char,
b position(06:10) char,
c position(11:20) char)
[ 외부 데이타 화일 ]
12 4 67890 ab def hi
2 67890 ab def hi
[ 검색결과 ]
SQL> select * from pretb;
A B C
------- ------- ------------
12 4 67890 ab def hi
2 67890 ab def hi
SQL> select length(a), length(c) from pretb;
LENGTH(A) LENGTH(C)
--------- ----------
5 10
5 10
7. 데이타가 없는 경우 NULL 데이타를 넣고자 할 때
[ 테이블 구조 ]
create table tratb (a varchar(10), b varchar(10), c varchar(10))
[ 콘트롤 화일 ]
load data
infile tra.dat
into table tratb
fields termintated by ','
trailing nullcols
(a char,
b char,
c char)
[ 외부 데이타 화일 ]
1,aa,
2,bb,FF
3,cc,
[ 검색결과 ]
SQL> select * from tratbl
A B C
----- ----- -------
1 aa
2 bb FF
3 cc
trailing nullcols를 사용하지 않으면 1 레코드째와 3 레코드째가 데이타 에러가 된다.
8. CHAR형 필드가 BLANK로 채워져 있을 때 NULL을 삽입하고 싶은 경우
[ 테이블 구조 ]
create table nulltb (a varchar(10), b varchar(10), c varchar(10))
[ 콘트롤 화일 ]
load data
infile null.dat
replace
into table nulltb
fields terminated by ','
(a char,
b char,
c char(10) nullif c = blanks)
[ 외부 데이타 화일 ]
aa,bb, ,
11,22, ,
99,88,AA
00,00,BB
[ 검색결과 ]
SQL> select * from nulltb;
A B C
----- ------ ------
aa bb
11 22
99 88 AA
00 00 BB
9. POSITION 지정시 BLANK를 그대로 로드하고 싶은 경우
[ 테이블 구조 ]
create table nulltb2 (a varchar(10), b varchar(10), c date)
[ 콘트롤 화일 ]
load data
infile null3.dat
replace
preserve blanks
into table nulltb2
(a position(1:2) char,
b position(3:4) char nullif b = blanks,
c position(5:13) date "YY/MM/DD")
[ 외부 데이타 화일 ]
998892/11/11
94/12/12
[ 검색결과 ]
SQL>select * from nulltb2;
A B C
----- ----- ---------------
99 88 92/11/11
94/12/12
SQL> select length(a), length(b) from nulltb2;
LENGTH(A) LENGTH(B)
--------- ----------
2 2
2
10. BLANK가 들어가 있을 때 0을 입력하고 싶은 경우
[ 테이블 구조 ]
create table def2 (a varchar(10), b varchar(10), c number)
[ 콘트롤 화일 ]
load data
infile def2.dat
replace
into table def2
fields terminated by ','
(a char,
b char,
c integer external defaultif c = blanks)
[ 외부 데이타 화일 ]
11,11,123
22,22, ,
33,33, ,
44,44, ,
[ 검색결과 ]
SQL> select * from deft;
A B C
----- -------- -------
11 11 123
22 22 0
33 33 0
44 44 0
11. 데이타가 NULL일 때 NULL이라고 하는 문자열을 넣을 경우
[ 테이블 구조 ]
create table ifnulltb (a varchar(10), b varchar(10), c varchar(10))
[ 콘트롤 화일 ]
load data
infile ifnull.dat
replace
into table ifnulltb
fields terminated by ','
(a char,
b char "nvl(:b,'NULL')",
c char)
[ 외부 데이타 화일 ]
1,2,3,
A,,B
a,b,c
[ 검색결과 ]
SQL> select * from ifnulltb;
A B C
----- ------ --------------
1 2 3
A NULL B
a b c
NVL과 같은 SQL 함수는 DIRECT LOAD의 경우 SQL 인터페이스를 경유하지 않기 때문에 사용할 수 없다.
12. 어떤 열을 모두 대문자(소문자)로 변환하여 로드하는 경우
[ 테이블 구조 ]
create table uptb
(a varchar(10),
b varchar(10))
[ 콘트롤 화일 ]
load data
infile upper.dat
replace
into table uptb
fields terminated by ','
(a char "lower(:a)",
b char "upper(:b)")
[ 외부 데이타 화일 ]
aBcDeFg,AbCdEf
ccDD11,ffGG22
[ 검색결과 ]
SQL>select * from uptb;
A B
------- -------------
abcdefg ABCDEF
cdd11 FFGG22
