database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Tutorials 8767 게시물 읽기
 News | Q&A | Columns | Tutorials | Devel | Files | Links
No. 8767
SQL*Loader 사용법
작성자
정재익(advance)
작성일
2001-12-08 01:04
조회수
10,705

** TXT 파일을 ORACLE DB로 저장할수 있는 오라클 유틸리티입니다 **

 

SQL*LOADER SAMPLES

=====================

 

SQL*Loader는 외부 화일의 데이타를 ORACLE 데이타베이스의 table에 넣기

위한 유틸리티입니다. 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 포지션 지정 시 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문자로 카운트되기 때문입니다.

[Top]
No.
제목
작성자
작성일
조회
8770OPS 란 - 간략한 개요
정재익
2001-12-08
8139
8769OS 명령으로 DATAFILE을 삭제한 경우:ORA-1157,1110
정재익
2001-12-08
5514
8768오라클 데이터베이스 생성방법
정재익
2001-12-08
14421
8767SQL*Loader 사용법
정재익
2001-12-08
10705
8765OCP 강좌 - Performance and Tuning (1)
정재익
2001-12-07
12732
8764OCP 강좌 - Tuning 기초 (3)
정재익
2001-12-07
7643
8763OCP 강좌 - Tuning 기초 (2)
정재익
2001-12-07
9047
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2021 DSN, All rights reserved.
작업시간: 0.022초, 이곳 서비스는
	PostgreSQL v13.3으로 자료를 관리합니다