Using CLOB in external tables – KUP-04026

This article presents solution for “KUP-04026: field too long for datatype” for external tables. Usually it happens when you want to read data for CLOB columns.

In most cases end users expect that all text fields in csv files are VARCHAR2. However VARCHAR2 size is limited to maximum 4000 characters. So if data for a column is bigger it should be defined as CLOB.

Example: field defined as CLOB and CHAR(n) where n is maximum size that is expected for the field.

CREATE TABLE "TEST_TABLE" 
(
  ...
  "DESCRIPTION" CLOB, 
  ...
) 
ORGANIZATION EXTERNAL 
( 
   TYPE ORACLE_LOADER
   DEFAULT DIRECTORY "DATA_DIR"
   ACCESS PARAMETERS
   ( 
      RECORDS DELIMITED BY x'0A' 
      CHARACTERSET WE8MSWIN1252
      FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
      (
          ...
          Description                              char(50000),
          ...
       )
   )
   LOCATION
   ( 
      "DATA_DIR":'test_table_1.csv', 
      "DATA_DIR":''test_table_2.csv', 
      "DATA_DIR":''test_table_3.csv'
       )
    )
   REJECT LIMIT UNLIMITED ;

Additional article worth to read:

Loading files as CLOB or BLOB columns using sqlldr

Have a fun 🙂

Tomasz

 

One thought on “Using CLOB in external tables – KUP-04026

Leave a Reply

Your email address will not be published. Required fields are marked *