| sqlldr loading into a clob col [message #528732] |
Wed, 26 October 2011 13:16  |
 |
BeefStu
Messages: 208
Registered: October 2011
|
Senior Member |
|
|
I am doing a simple test and need to populate a smaill
table with some data.
My table looks like this:
SQL> desc clob_test
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(20)
C1 CLOB
I have a pipe deimited data file ID range 1-50000 and random
characters with a length of 100-4000 bytes for the clob feild.
My control file looks like this but I am guessing it is wrong:
LOAD DATA
INFILE 'clob_test.dat'
INTO TABLE CLOB_TEST
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
ID INTEGER EXTERNAL NULLIF (ID=BLANKS)
, C1
)
Can somebody let me know what I need to do to my .ctl file
in order to load this data?
Thanks in advance to all who answer
|
|
|
|
| Re: sqlldr loading into a clob col [message #528738 is a reply to message #528732] |
Wed, 26 October 2011 14:25  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
A table:SQL> desc clob_test
Name Null? Type
----------------------------- -------- --------------------
ID NUMBER(20)
C1 CLOB
SQL> select * from clob_test;
no rows selected
A control file:LOAD DATA
INFILE *
INTO TABLE clob_test
REPLACE
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
id,
c1
)
BEGINDATA
1|dee REMOTE-ACCESS 01/01/1986
2|ps REMOTE-ACCESSF 09/07/2011
2000|ky PRIORITY-REMOTE-ACCESS 09/05/2011
Loading session & the result:SQL> $sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 10.2.0.1.0 - Production on Sri Lis 26 21:25:36 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 3
SQL> select * from clob_test;
ID C1
---------- ------------------------------------------------
1 dee REMOTE-ACCESS 01/01/1986
2 ps REMOTE-ACCESSF 09/07/2011
2000 ky PRIORITY-REMOTE-ACCESS 09/05/2011
SQL>[Updated on: Wed, 26 October 2011 14:26] Report message to a moderator |
|
|
|
|
|
|
|
| Re: sqlldr loading into a clob col [message #528753 is a reply to message #528748] |
Wed, 26 October 2011 19:43 |
 |
Barbara Boehmer
Messages: 9102
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you don't specify the data type and size in your control file, then the deafult char(255) is applied. I have provided an example control file, load, and partial results appropriate to what you have described below.
-- The clob_test.dat file was created as follows:
store set saved_settings replace
set echo off feedback off heading off pagsize 0 verify off
set linesize 5000
set colsep ,
spool clob_test.dat
select '1|abcdefghijklmnopqrstuvwxyz', rpad('a', 4000, 'a') from dual
union all
select '50000|abcdefghijklmnopqrstuvwxyz', rpad ('z', 4000, 'z') from dual
/
spool off
start saved_settings
-- test.ctl:
options (skip=1)
LOAD DATA
INFILE clob_test.dat
INTO TABLE clob_test
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(id INTEGER EXTERNAL NULLIF (id=BLANKS),
c1 CHAR (5000))
-- table, load, and partial results:
SCOTT@orcl_11gR2> create table clob_test
2 (id number (20),
3 c1 clob)
4 /
Table created.
SCOTT@orcl_11gR2> host sqlldr scott/tiger control=test.ctl log=test.log
SCOTT@orcl_11gR2> column c1 format a45 word_wrapped
SCOTT@orcl_11gR2> select id, length (c1) from clob_test
2 /
ID LENGTH(C1)
---------- ----------
1 4031
50000 4027
2 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
