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
Great! This is what I was looking for!
Thanks Tomek