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)
[ 외부 데이타 화일 ]
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
replace
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
replace
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
position 지정으로 블랭크를 그대로
입력하기를 원하는 경우 preserve blanks를 지정한다.
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
|