SQL Loader

SQL Loader

엑셀 등 다른 응용 프로그램에 저장되어 있던 대량의 데이터를 오라클로 이전할 때 사용할 수 있는 유틸리티

오라클 설치시 기본적으로 설치됨

ERP (Enterprise Resource Planning, 전산 자원 관리) 구축 프로젝트에서 

기존에 엑셀 데이터들을 오라클 DB로 입력해야 할 때 수동으로 할 수는 없으니까 

SQL Loader를 사용해 오라클로 한 번에 이전 하는 것

구조

다른 응용 프로그램에서 생성도니 특정 규칙을 가진 텍스트 파일을

메모리를 거치지 않고 데이터 파일로 직접 대량 입력

- 입력 파일을 여러개 가질 수 있음

- 입력 필드가 고정 길이나 가변 길이로 가능

- 하나의 입력 데이터로 여러 테이블에 동시 입력 가능

- 테이블의 기존 데이터 변경이나, 지우고 다시 입력하거나 추가하는 작업도 가능

- SQL 함수 사용해서 입력 가능

- Sequence 사용해서 데이터를 순차적으로 입력 가능

구성파일

CONTROL FILE   

SQL Loader를 사용하기 위한 정보를 가진 control file (오라클 운영을 위한 control file과는 다름)

확장자는 ctl

내용

LOAD DATA

새로운 데이터 입력이 시작됨을 의미

중단된 작업을 다시 시작할 경우엔 앞에 CONTINUE를 추가해 주면 됨

INFILE *

입력하고자 하는 데이터 파일이 외부에 있을 경우는 * 대신 파일 이름을,

컨트롤 파일 내부에 데이터가 있을 경우에는 * 사용

REPLACE

테이블의 기존 행 덮어쓰기

APPEND

기존내용 아래에 신규 내용 추가

INSERT

내용이 없는 테이블에 신규 데이터 입력

TRUNCATE

기존 내용을 모두 truncate 한 후 다시 입력

INTO TABLE '테이블명'

데이터를 입력할 테이블명 적으면 됨

WHEN

입력하고자 하는 조건이 있으면 when을 사용해 적어주면 된다

FIELDS TERMINATED BY ','

각 필드의 구분 기호를 설정, 콤마를 썼으므로 필드 구분기호는 콤마가 된다

OPTIONALLY ENCLOSED BY '"' (A, B. C, ...)

데이터가 있을 경우 칼럼 이름을 적어준다 

BEGIN DATA A, B, C, ...

실제 데이터 적어주기

LOGFILE

SQL Loader 실행 중 장애나 에러를 확인 가능한 파일

BADFILE

입력 거부된 목록을 저장하는 파일, 

지정하고 싶으면 controlfile 에 추가해주면 됨, 뒤에 '파일명.bad'를 적어주면 된다.

입력형태가 틀리거나 제약조건 위배시 이 파일에 기록되는데 내용을 수정해서 다시 입력 가능

DISCARDFILE

폐기된 파일 목록을 저장할 파일, 

지정하고 싶으면 controlfile 에 추가해주면 됨, 뒤에 '파일명.dsc'

WHERE절 조건에 해당하지 않아 폐기되는 파일을 기록

필요할 경우만 생성할 것, 역시 내용 수정 후 다시 사용 가능

※ SQL Loader 수행 전 확인 사항 ※

1. NLS_LANG 환경 변수 체크

$ env | grep NLS_LANG

2. ORACLE_HOME 환경 변수 체크

$ env | grep ORACLE_HOME

--> 잘못 설정이 된 경우 ORA-12560: TNS:protocol adapter error 발생

3. LD_LIBRARY_PATH 환경 변수 체크

$ env | grep LD_LIBRARY_PATH

--> $ORACLE_HOME/lib 값이 이 변수안에 지정되어있어야 함

잘못 설정이 된 경우 libwtc8 library cannot be found. 에러 발생

4. ORA_NLS33 / ORA_NLS10 환경 변수 체크

$ env | grep ORA_NLS33

$ env | grep ORA_NLS10

5. $sqlldr <-오라클 계정으로 실행하면 옵션들이 나오는데

   여러 옵션들 중에서 errors 옵션을 충분히 준 후 작업할 것!

   에러를 몇 개 까지 기록할 것인가를 지정하는 것인데 기본값은 50

   에러가 50개를 넘어가게 되면 SQL Loader 작업이 중단됨.

입력할 데이터가 외부 프로그램 자료일 경우

작업 순서

엑셀일 경우 파일을 "CSV (쉼표로 분리)" 확장자로 저장한 후 오라클 계정으로 편한 경로로 파일을 옮기기

vi 에디터로 해당 파일을 열어 칼럼이름을 삭제한 후 저장

입력할 테이블을 생성

SQL Loader의 Control File 생성

load data

infile "csv파일경로"

into table 테이블명

fields terminated by ','

(칼럼이름,칼럼이름,...)

SQL Loader 실행

$ sqlldr scott/tiger control='컨트롤파일경로'

테이블조회해서 확인하기

[예제]

입력 데이터가 control file 내부에 있을 경우

Control File에서

infile *

optionally enclosed by '"'

begindata

데이터들입력

주의할 점은 한글이 제대로 입력되지 않는 경우가 종종 발생하는데

character set이 틀릴경우 SQL*Loader-02050: Multibyte character error 발생 됨

그래서 꼭 Character Set 잘 확인 할 것!

또한, OS 설정 캐릭터랑 오라클 설정 캐릭터랑 안맞아서 !로 나가서 작업할 때랑 exit로 나가서 작업할 때랑 다른 결과를 나타냄

그럴 땐 .bash_profile에서 export LANG=C로 바꿔주고 한번 해보기

[예제]

Log File, Bad File, Discard File 사용하기

Log File은 데이터 입력시 문제가 되는 내용, 에러 내역을 알려줌

데이터 입력에 실패할 경우 로그파일 기록을 보면 된다.

Bad File위치도 Log File에 있는데

Bad File은 문제가 되는 내용만 저장되어 있음

문제가 되는 내용을 수정 --> dat파일로 저장 --> control file 수정해서 문제 파일만 다시 입력

이 과정으로 입력이 되지 않는 데이터를 다시 입력할 수 있다.

[예제]

Control file에서 position 사용하기

엑셀파일일 경우 .prn 텍스트 (공백으로 분리) 형식으로 저장 후 리눅스로 옮김

vi editor로 해당 파일을 열고 칼럼 이름들은 지운 후 데이터들은 한칸씩 공백으로 구분해 다시 편집

control file 수정시 position이라는 파라미터를 사용해 칼럼 길이를 직접 지정

예) position(01:04) --> 첫째칸부터 넷째칸까지가 칼럼 길이라는 소리

입력하는 데이터가 한글일 경우 한글은 2bytes 문자기 때문에 칼럼 위치를 잘 지정할 것!

null 값을 입력하려면 컨트롤 파일에 trailing nullcols 꼭 넣어주기

integer external --> 숫자 칼럼

char --> 문자 칼럼

[예제]

null 값 0으로 표시하기

Control File에 입력시

null값이 있는 칼럼에 "nvl(:칼럼이름,0)" 를 추가해 준다.

[예제]