SQL Loader를 이용하여 CLOB 타입의 데이터를 로드하려고 했다.
-> Control File에서 char(1000000)으로 받으니 되었다.
CLOB 타입 데이터에 /n(개행) 문자가 있어서 data file을 정상적으로 구분하지 못한다. (다른 row로 인식하는 에러)
-> Control File (infile data directory/dataname.dat 이후 "str X'0D0A'" (기본 엔터) "STR x'hex_str'" 의 옵션을 이용하여 줄 바꿈 구분자(hex code)를 설정할 수 있다.
Record terminators for datafiles that are in stream format in the UTF-16 Unicode encoding default to "\n" in UTF-16 (that is, 0x000A on a big-endian system and 0x0A00 on a little-endian system). You can override these default settings by using the "STR 'char_str'" or the "STR x'hex_str'" specification on the INFILE line. For example, you could use either of the following to specify that 'ab' is to be used as the record terminator, instead of '\n'.
- 출처 : http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_control_file.htm
-> 나의 경우 컨트롤 파일에서 Data를 infile구문에 넣지 않고 쉘을 이용하여 Command 명령을 해야 했기 때문에 다른 방법을 이용했다.
-> SELECT REPLACE(REPLACE(컬럼,CHR(13),''),CHR(10),''); 구문을 이용하여 줄 바꿈을 공백으로 치환하였다.
CHR(13) : 캐리지 리턴(carriage return)
CHR(10) : 라인 피드(new line)
* hex code 찾는 쿼리 : Select utl_raw.cast_to_raw('^'||chr(10)) from dual;
REPLACE
- 구문 : REPLACE(char,search_string,'replacement_string')
- 목적 : search_string이 나타날 때 replacement_string으로 치환해서 char를 리턴한다. replacement_string을 지정하지 않은 경우에는 char안에 나타나는 모든 search_string을 삭제한다.
search_string도 replacement_string도 지정하지 않을 경우에는 함수는 NULL을 리턴한다.
REPLACE 함수는 TRANSLATE 함수 슈퍼세트에 대응한다. TRANSLATE 함수는 1문자의 치환을 행하는 것에 반해서 REPLACE 함수는 문자열의 치환과 삭제를 행할 수 있다.
* 예 SELECT REPLACE('JACK AND JUE','J','BL') "change" FROM DUAL;
아래는 구글링 검색을 통해 데이터에 엔터가 포함되었을때 로드 하는 방법을 찾은 결과이다.
컬럼에 엔터가 포함된 데이터들을 sql loader로 올리는 방법
-- test2 테이블 생성 --
CREATE TABLE test2 (no number(1), timestamp date, xxx VARCHAR2(100));
-- ControlFile : test2.ctl --
LOAD DATA
INFILE ".\test2.txt" "str '$\r\n'"
BADFILE test2.bad
APPEND INTO TABLE test2
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
( "NO" NULLIF ("NO"="NULL")
, "TIMESTAMP" DATE 'MM/DD/YYYY HH24:MI:SS' NULLIF "TIMESTAMP"="NULL"
, xxx
)
-- DataFile : test2.txt --
1,05/04/2011 00:00:00,aaa$
2,NULL,bbb
ccc
ddd$
NULL,05/04/2011 01:00:00,땡큐$
-- Sqlldr 실행 --
sqlldr scott/tiger control=test2.ctl
[sqlldr 옵션 readsize]
readsize값이 작다고 에러가 발생 시
-> sqlldr 옵션에서 readsize(기본값 1048576)를 MAX값으로 변경해 주면 된다.
TODO) 추가적으로 CLOB에 대해 조사
