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: docs.oracle.com/cd/B10500_01/server.920/a96652/ch07.htm#1006805 — A.B.Cade
– A.B.Cade, Commented Jun 12, 2012 at 6:40

eli-k · Accepted Answer · 2016-12-07 16:03:00Z

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 7, 2016 at 16:03

eli-k

11.5k12 gold badges46 silver badges48 bronze badges

answered Dec 7, 2016 at 14:15

user7262769

392 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gary_W Over a year ago

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: stackoverflow.com/a/37947714/2543416

Collectives™ on Stack Overflow

Adding clob column to Oracle database using sqlloader

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related