SSISO Community

시소당

SQL*LOADER

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
   APPEND     
새로운 행을 기존의 데이타에 추가

   INSERT      : 
비어 있는 테이블에 넣을 때

   TRUNCATE              
테이블의 기존 데이타를 모두 truncate 하고
insert

   SQL*Loader
를 실행하면 아래의 파일이 작성 됩니다
.
   

* 로드 작업 중 동작에 관한 통계 등을 포함한 로그 화일(확장자는 log)
    *
데이타 에러 때문에 로드가 안된 레코드를 저장한 배드 화일(확장자는
bad)
    *
사용자의 선택 기준에 적합하지 않은 레코드를 저장한 디스카드 화일(discard 화일
)
      
이것은 discardfile 옵션으로 별도로 지정해야 생성됩니다
.

실행 방법은 다음과 같습니다
.

$ SQLLDR  scott/tiger  CONTROL=sample.ctl   DATA=sample.dat
1.1) 임의의 열에 변화없는 고정 문자열()을 입력한 경우

   ****
테이블 구조 ****
   create table cons_test
   (a number,
    b number,
    c number,
    d varchar(10))

   ****
콘트롤 화일 (즉 이예에서 sample.ctl)****

   load data
   infile cons.dat
   replace
   into table cons_test
   fields terminated by ','
   (a integer external,
    b integer external,
    c CONSTANT '100',
    d char)

   ****
외부 데이타 화일 (, 이예에서 sample.dat) ****

   1,2,DATA
   2,4,DATA2
====================================================================
**** 검색결과 ****
   SQL>select * from cons_test;

          A       B       C D
   ---------------------------------
          1       2     100 DATA
          2       4     100 DATA2

  
주의사항 : 이 예에서 C열은 데이타 화일에 넣어서는 안 됩니다. COSNTANT
 
그것으로 완결된 열 지정의 하나가 됩니다. integer external 데이타 형은

 
수치 데이타를 문자형식(ASCII CODE)로 나타낸 것입니다
.
 
1.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

  
주의사항 : 이 예에서는 B열은 데이타 화일에 넣어서는 안됩니다. SYSDATE
 
그것으로 완결된 열 지정의 하나가 됩니다. 새로운 시스템 날짜매김은 컨벤셔널

 
패스에서는 실행 시에 삽입된 각각의 레코드 배열마다 또, 다이렉트 패스의

 
경우는 로드된 각각의 레코드의 블럭마다 사용됩니다
.
 

1.3) 임의의 수치열에 연속 번호(sequence)를 붙이고 싶은 경우


**** 테이블 구조 ****
create table rectb
(a varchar(10),
 b number,
 c varchar(10))

****
콘트롤 화일 ****

load data
infile rec.dat
replace
into table rectb
fields terminated by ','
(a char,
 b recnum,
 c char)

****
외부 데이타 화일 ****

A,a
B,b
C,c
====================================================================
**** 검색결과 ****
SQL>select * from rectb;

A       B C
---------------------
A       1 a
B       2 b
C       3 c

  
주의사항 : 이 예에서는 B 열은 데이타 화일에 넣어서는 안됩니다. RECNUM
 
그것으로 완전한 열 지정의 하나가 됩니다. 연속번호는 1부터 차례대로 1

 
더해져서 번호가 매겨집니다. 가산된 번호를 둘씩 건너뛰거나 하는 것은 불가능

 
합니다
.
1.4) 임의의 수치열에 연속 번호(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

  
주의 사항 : 이 예에서는 B열은 데이타 화일에 넣어서는 안 됩니다. SEQUENCE
 
그것으로 완결된 열 지정의 하나가 됩니다. 초기 값 100과 늘인 값 5는 다른

 
수치로 변경 가능합니다
.
1.5) 로드하는 논리 레코드를 구성하는 물리 레코드가

복수 열로 구성된 경우
                         (물리 레코드의 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 바이트 째부터 시작해서는 안됩니다. 상기 예의 경우
,
 
레코드의 선두 바이트가 '%'일 때 다음의 레코드가 연결됩니다
.
 
 


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.6) 외부 데이타 화일의 물리 레코드

복수 레코드로 구성된 경우
    
(구성하는 물리 레코드 수가 모두 일정한 경우)

****
테이블 구조 ****

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

  
주의 사항 : 하나의 논리 레코드가 모두 일정한 갯수의 물리 레코드로부터
 
성립되는 것 같은 단순한 경우에 한합니다
.
 
 


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1.7) 데이타의 잘린 문자를 데이타로 로드하고 싶은 경우

****
테이블 구조 ****

create table enc
(a varchar(10),
 b varchar(10),
 c varchar(10))

****
콘트롤 화일 ****
load data
infile enc.dat
replace
into table enc
fields terminated by ',' optionally enclosed by ' " ' and ' " '
(a char,
 b char,
 c char)

****
외부 데이타 화일 ****
"abc,d",2,3
"a,,d",4,5
====================================================================
**** 검색결과 ****
SQL>select * from enc;

A       B       C
-------------------------
abc,d   2       3
a,,d    4       5
 
 


 

 

 

1.8)  POSITION 지정 시,  

CHAR형 데이터 전·후의 blank도 로드하고 싶은 경우


**** 테이블 구조 ****

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
1.9) 어떤 데이타 열의 데이타 유무와 상관없이,

데이타가 없는 경우,  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 레코드째가
 
데이타 에러가 됩니다. 데이타가 들어 있기도 하고 없기도 한 열의 데이타는
 
데이타 화일의 최후로 가져갑니다.

1.10)  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

주의 사항 : 검색결과의 1 행째, 2 행째의 열 C는 블랭크가 아니라 NULL 입니다.

1.11) DATE 필드BLANK로 채워져 있을 때,

NULL을 삽입하고 싶은 경우

****
테이블 구조 ****
create table nulltb2
(a varchar(10),
b varchar(10),
c date)

****
콘트롤 화일 ****
load data
infile null2.dat
replace
into table nulltb2
fields terminated by ','
(a char,
b char,
c date "YY/MM/DD" nullif c = blanks)

****
외부 데이타 화일 ****
aa,bb, ,
11,22, ,
99,88,92/11/11,
00,00,94/12/12,
===========================================================
**** 검색결과 ****
SQL>select * from nulltb2;
A       B       C
-------------------------
aa      bb
11      22
99      88      92/11/11
00      00      94/12/12

주의사항 : 검색결과의 1 행째, 2 행째의 열 C는 블랭크가 아니라 NULL 입니다.

 

1.12) POSITION 지정 시,  BLANK를 그대로 로드하고 싶은 경우

**** 테이블 구조 ****

create table nulltb2
(a varchar(10),
b varchar(10),
c date)

****
콘트롤 화일 ****

--- position
지정으로 블랭크를 그대로 입력 원하는 경우
--- preserve blanks
를 지정한다
.
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
주의사항 : 이 경우 2 레코드째는 A에 블랭크가 들어가고 B NULL이 들어갑니다.

1.13) 데이타 화일의 수치 데이터 열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
 

 

1.14) 데이타가 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 인터페이스를
            
경유하지 않기 때문에 사용할 수 없습니다.

1.15) 어떤 열을 모두 대문자(소문자)로 변환하여 로드하고 싶은경우

****
테이블 구조 ****

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
 
 


 

 

 

 

 

1.16) ZONE 형 데이터를 로드하고 싶은 경우

ZONE
의 데이타 형식은 1문자 1바이트로 나타납니다.

숫자 +123이라면


bit     1234    5678    1234    5678    1234    5678
       1111    0001    1111    0010    1100    0011
10
           1               2         +        3

최후의 8비트의 상위 4비트(부호 비트)로 정부가 지정됩니다
.

      1100(
) 1101(
)

가 됩니다
.

원래는 EBCDIC코드에서


Hex F0 to F9  →  +0 to +9
Hex C0 to C9  →  +0 to +9(
부호비트
)
Hex D0 to D9  →  +0 to -9(
부호비트
)

이것을 아스키로 변환했기 때문에

부호비트
       {ABCDEFGHI}JKLMNOPQR
------------------------------------
부호
           ++++++++++----------
             01234567890123456789

가 됩니다
.


 

 

 

 

 

**** 테이블 구조 ****
create table z
(a varchar(10),
i number,
j number)

****
콘트롤 화일 ****
load data
infile filename.dat
replace
into table z
(a position (01) char,
i position(02:07) zoned(6),
j position(08:13) zoned(6))

****
외부 데이타 화일 ****
A12345{12345}


===========================================================
**** 검색결과 ****
SQL>select * from z;

A            I        J
-----------------------
A       123450  -123450
 
 


 

 

 

 

 

 

 

1.17) decimal 형 데이터를 로드하고 싶은 경우


- decimal 데이타는 1바이트에 2개의 숫자가 지정됩니다.
숫자 +123 이라면

bit    1234 5678 1234 5678
      0001 0010 0011 1100
10
     1    2    3    +
끝의 4비트는 부호 비트로 불리고 다음과 같이 됩니다
.
    1100(
)        1101(
)

****
테이블 구조 ****

create table dec
(a varchar(10),
col1 number,
col2 number)

****
콘트롤 화일 ****
load data
infile filename.dat
replace
into table dec
(a position (01:01) char,
col1 position(02:05) decimal(7,4),
col2 position(06:09) decimal(7,4))

****
외부 데이타 화일 ****
데이타 화일은 바이너리이기 때문에 more 등으로 볼 수 없습니다.
4112 3456 7c12 3456 7d0a
===========================================================
**** 검색결과 ****
SQL>select * from dec;
A               COL1        COL2
--------------------------------
A           123.4567   -123.4567
주의 사항 : 콘트롤 화일에서 decimal 형을 사용하는 것에 따라 (length, scale)을 지정합니다.

1.18) 고정 길이 레코드 데이터를 로드하고 싶은 경우


****
테이블 구조 ****
create table fixtb
(a varchar(10),
b varchar(10))

****
콘트롤 화일 ****
load data
infile fix.dat
"FIX 6"
replace
into table fixtb
(a char(3),
b char(3))


****
외부 데이타 화일 ****
123456abcdef

****
외부 데이타 파일(16) ****
3132 3334 3536 6162 6364 6566
===========================================================
**** 검색결과 ****


SQL>select * from fixtb;
A      B
---------------
123    456
abc    def
 
주의 사항 : 데이타 화일의 끝에는 OxOa(CARRIAGE RETURN)

들어가면 안 됩니다.

OxOa 1문자로 카운트되기 때문입니다.

908 view

4.0 stars