THIS EXAMPLE IS FOR LODING DATA INTO CLOB COLUMN BY SQL LOADER
STEP 1 Creating lob table;
================
SQL> conn scott/tiger
Connected.
SQL>
SQL> create table test_clob(
id number,
ename varchar2(10),
resume clob);
STEP 2 first create 2 text file on OS and enter some data in it.
==============================
[oracle@ocs sqlloader]$ vi resume1.txt
name Syed faraz Ahmed
fname Ahmed
dob 01 Jan 1975
======save file =======
[oracle@ocs sqlloader]$ vi resume2.txt
name zaman ahmed tatari
fname naseem ahmed tatari
dob 29 mar 1980
======save file =======
STEP 3 NOW CREATE CSV data file
==============================
[oracle@ocs sqlloader]$ vi emp.csv
1,zaman,resume1.txt
2,faraz,/home/oracle/sqlloader/resume2.txt
===========save file ================
STEP 4 Now create Control file
===============================
[oracle@ocs sqlloader]$ vi testclob.ctl
load data
infile '/home/oracle/sqlloader/emp.csv'
into table test_clob
replace
fields terminated by ',' trailing nullcols
(
id integer external(3),
ename char(10),
clob_file_loc filler char(100),
resume lobfile(clob_file_loc) terminated by EOF
)
===========save file ================
NOTE: In above control if you use Blob column the syntax is same.
clob_file_loc filler char(100) this filler column in sqlloader file which will not load into the table. This is use to get filename that use in next line.
STEP 5 Load data by SQL Loader
==============================
sqlldr scott/tiger control=testclob.ctl
STEP 6 Verify Load data by sqlplus
=================================
SQL> conn scott/tiger
Connected.
SQL>
SQL>
SQL> select * from test_clob;
ID ENAME
---------- ----------
RESUME
------------------------------------------------------------------------------
1 zaman
name Syed faraz Ahmed
fname Ahmed
dob 01 Jan 1975
2 faraz
name zaman ahmed tatari
fname naseem ahmed tatari
dob 29 mar 1980
