KUP-04026: field too long or datatype error

Question

I am getting the error

KUP-04021:field formatting error for field LONG_TEXT
KUP-04026: field too long for datatype

The table structure is

create table test(
NAME varchar2(4000),
STATUS varchar2(4000),
LONG_TEXT CLOB)
ORGANIZATIONAL EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "TEST_DIRECT"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
SKIP 1
FIELDS TERMINATED BY ','
MISSING FIELDS VALUES ARE NULL
(
NAME,
STAtUS,
LONG_TEXT)
)LOCATION
('test.csv'
)
REJECT LIMIT UNLIMITED;

LOG file showing as

Fields in the Data sorce:
NAME
Terminated by","     CHAR (255)
Trim whitespaces same as sql loader
STATUS
Terminated by","     CHAR (255)
Trim whitespaces same as sql loader
LONG_TEXT
Terminated by","     CHAR (255)
Trim whitespaces same as sql loader

KUP-04021 field formatting error for LONG_TEXT
KUP-04026: field too long for datatype
KUP: record 2 rejected in file path/test.csv

LONG_TEXT data is below

[![LONG_TEXT String ]()

Help should be more appreciate. Thanks

I tried store the LONG_TEXT text in table column LONG_TEXT but getting error. Can you please anyone can guide me on this.

Answer 1

The main issue is that the field lengths need to be specified if you don't want the default size of 255. For example:

(
    NAME      char(4000),
    STATUS    char(4000),
    LONG_TEXT char(999999999)
)

I'm not sure what the maximum size is for CHAR in a SQL*Loader context. There are also several other syntax errors in your code, and you will likely run into more errors when consuming large text file that contains commas and newlines. SQL*Loader and external tables can be quite tricky, and you'll want to reference the SQL*Loader Control File Reference often.