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?

Answer 1

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)
)