Adding clob column to Oracle database using sqlloader

Question

I'm having a file like this delimited by '|'

some varchar text | some varchar text | some varchar text | very long text >3500

I need to upload this file using sqlloader . the schema of the table is

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                    NOT NULL VARCHAR2(100)
 TIME                                    NOT NULL VARCHAR2(60)
 EXCEPTION                               NOT NULL VARCHAR2(300)
 DETAILS                                          CLOB

The content of the control file is

LOAD DATA
INFILE *
REPLACE
INTO TABLE BX_TWISTER_ERRORS
fields terminated by '|'
(
  NAME,
  TIME,
  EXCEPTION,
  DETAILS
)

I'm getting the following error:

Variable length field exceeds maximum length.

For each details field .

Can anyone give any suggestions or solutions for uploading this kinda delimited file using SQL loader?

See here: http://docs.oracle.com/cd/B10500_01/server.920/a96652/ch07.htm#1006805 — A.B.Cade, Jun 12 '12 at 06:40

score 3 · Answer 1 · edited Dec 07 '16 at 16:03

3

Just add CHAR(10000) ore desired size in your control file.

LOAD DATA
INFILE *
REPLACE
INTO TABLE BX_TWISTER_ERRORS
fields terminated by '|'
(
  NAME,
  TIME,
  EXCEPTION,
  DETAILS CHAR(10000)
)

edited Dec 07 '16 at 16:03

eli-k

10,898
11
40
44

answered Dec 07 '16 at 14:15

user7262769

39
2

FYI - sqlldr's internal char buffers are only 255. If you are reading in data fields larger than that you will get the error. I am in the habit of always creating the control file with a CHAR(X) that matches the table. Actually I have a function that will generate a skeleton control file from a table that sets this up to remove some of the tedious work. I posted it before: http://stackoverflow.com/a/37947714/2543416 – Gary_W Dec 07 '16 at 15:31

Adding clob column to Oracle database using sqlloader

1 Answers1