Home » RDBMS Server » Server Utilities » sqlldr loading into a clob col (oracle 11.2.0.2.0 AIX 6,1)
sqlldr loading into a clob col [message #528732] Wed, 26 October 2011 13:16 Go to next message
BeefStu
Messages: 192
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 Go to previous messageGo to next message
Littlefoot
Messages: 21530
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 #528744 is a reply to message #528738] Wed, 26 October 2011 15:55 Go to previous messageGo to next message
BeefStu
Messages: 192
Registered: October 2011
Senior Member
Here is my error when I use data string of a couple of hundred
bytes.

Field in data file exceeds maximum length


What do I need to put into the .ctl file for the clob declaration. I think I read the limit is 4000 bytes?

Can you please provide and example.

Thanks for your help
Re: sqlldr loading into a clob col [message #528748 is a reply to message #528744] Wed, 26 October 2011 16:57 Go to previous messageGo to next message
BlackSwan
Messages: 26659
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

http://www.lmgtfy.com/?q=oracle+sqlldr+clob
Re: sqlldr loading into a clob col [message #528753 is a reply to message #528748] Wed, 26 October 2011 19:43 Go to previous message
Barbara Boehmer
Messages: 8919
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>


Previous Topic: Data Import Error
Next Topic: Nid Ultiltiy
Goto Forum:
  


Current Time: Tue Feb 25 04:37:28 CST 2020