大文本数据来源于 文本 文件
-- 复制表结构
create table scott.emp_test_lob as select * from scott.emp where 1=2;
alter table SCOTT.EMP_TEST_LOB add CTEXT clob; -- 大文本字段
-- 获取样例数据
SELECT empno || ',' || ename || ',' || job || ',' || mgr || ','
|| TO_CHAR(hireDate, 'yyyy-mm-dd') || ',' || sal || ',' || comm || ',' || deptno
FROM scott.emp
把以下数据保存为: emp_test_lob.txt 文件
7369,SMITH,CLERK,7902,1980-12-17,800,,20,txtfiles/usr_01.txt
7499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,30,txtfiles/usr_02.txt
7521,WARD,SALESMAN,7698,1981-02-22,1250,500,30,txtfiles/usr_03.txt
7566,JONES,MANAGER,7839,1981-04-02,2975,,20,txtfiles/usr_04.txt
7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30,txtfiles/usr_05.txt
7698,BLAKE,MANAGER,7839,1981-05-01,2850,,30,txtfiles/usr_06.txt
7782,CLARK,MANAGER,7839,1981-06-09,2450,,10,txtfiles/usr_07.txt
7788,SCOTT,ANALYST,7566,1987-04-19,3000,,20,txtfiles/usr_08.txt
7839,KING,PRESIDENT,,1981-11-17,5000,,10,txtfiles/usr_09.txt
7844,TURNER,SALESMAN,7698,1981-09-08,1500,0,30,txtfiles/usr_10.txt
7876,ADAMS,CLERK,7788,1987-05-23,1100,,20,txtfiles/usr_11.txt
7900,JAMES,CLERK,7698,1981-12-03,950,,30
7902,FORD,ANALYST,7566,1981-12-03,3000,,20
7934,MILLER,CLERK,7782,1982-01-23,1300,,10
-- 写控制文件(control_lob.ctl)
LOAD DATA
INFILE 'emp_test_lob.txt'
BADFILE 'emp_test_lob.BAD'
APPEND
INTO TABLE scott.emp_test_lob
TRAILING NULLCOLS
(
empno INTEGER EXTERNAL TERMINATED BY ",",
ENAME char TERMINATED BY ",",
JOB CHAR TERMINATED BY ",",
MGR INTEGER EXTERNAL TERMINATED BY ",",
HIREDATE DATE "YYYY-MM-DD HH24:MI:SS" TERMINATED BY ",",
SAL DECIMAL EXTERNAL TERMINATED BY ",",
COMM DECIMAL EXTERNAL TERMINATED BY ",",
DEPTNO INTEGER EXTERNAL TERMINATED BY ",",
text_file_name FILLER CHAR TERMINATED BY ",",
CTEXT LOBFILE(text_file_name) TERMINATED BY EOF
)
-- 运行
sqlldr scott/tiger@oralocal control=control_lob.ctl