clob,blob 데이터 sqlloder 이용해서 데이터 저장

CLOB, BLOB 파일 리스트

 

테이블 생성

 

SQL> conn hhh/hhh
연결되었습니다.
SQL>
SQL> create table lob_tab
  2  (number_content number(10),
  3  varchar2_content varchar2(100),
  4  clob_content clob,
  5  blob_content blob);

테이블이 생성되었습니다.

 

 

lob_test.ctl  <-파일생성  

 

load data
infile 'C:\Users\hhhwang\Documents\Shared\lob_test_data.txt'
into table lob_tab
fields terminated by ','
(number_content char(10),
varchar2_content char(100),
clob_filename filler char(100),
clob_content lobfile(clob_filename) terminated by eof,
blob_filename filler char(100),
blob_content lobfile(blob_filename) terminated by eof)

 

 

lob_test_data.txt  <-파일생성

 

1,one,C:\Users\hhhwang\Documents\Shared\clob_test1.txt,C:\Users\hhhwang\Documents\Shared\blob_test1.doc
2,two,C:\Users\hhhwang\Documents\Shared\clob_test2.txt,C:\Users\hhhwang\Documents\Shared\blob_test2.doc

 

실행 및 확인

 

C:\WINDOWS\system32>sqlldr userid=hhh/hhh control=C:\Users\hhhwang\Documents\Shared\lob_test.ctl log=C:\Users\hhhwang\Documents\Shared\lob_test.log bad=lob_test.bad

 

SQL*Loader: Release 11.2.0.4.0 - Production on 금 7월 8 10:40:13 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

커밋 시점에 도달 - 논리 레코드 개수 1
커밋 시점에 도달 - 논리 레코드 개수 2

 

C:\WINDOWS\system32>

 

 

C:\WINDOWS\system32>sqlplus hhh/hhh

SQL*Plus: Release 11.2.0.4.0 Production on 금 7월 8 10:48:20 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

다음에 접속됨:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> select * from lob_tab ;