Oracle SQL Loader: FILLER Field in data file exceeds maximum length

Question

I need to load a lof of data to an Oracle Database via SQL Loader.

OS: RHEL

Oracle: 12c

Here is my control file:

OPTIONS(SKIP=1)
LOAD DATA
CHARACTERSET UTF8
INFILE 'temp.dat' "STR '\",\"'"
BADFILE 'bad.dat'
TRUNCATE
INTO TABLE MY_TABLE
FIELDS TERMINATED BY ";"
(
PERSONNEL_NO    CHAR,
emp_name_f_r    FILLER,
LAST_NAME_RUS   CHAR,
FIRST_NAME_RUS  CHAR,
MIDDLE_NAME_RUS CHAR,
LAST_NAME_ENG   CHAR,
FIRST_NAME_ENG  CHAR,
emp_name_s_r    FILLER,
EMAIL   CHAR,
POSITION_NO CHAR,
POSITION_RUS    CHAR(300000),
emp_posname_s_r FILLER,
emp_orgid   FILLER,
emp_orgname_r   FILLER,
emp_orgref_r    FILLER,
emp_dirname_r   FILLER,
emp_pos_path_r  FILLER,
emp_org_path_r  FILLER,
ALIAS   CHAR,
emp_name_f_e    FILLER,
emp_name_s_e    FILLER,
POSITION_ENG    CHAR(300000)
)

I have a lot of errors like this in the LOG:

Record 9: Rejected - Error on table MY_TABLE, column EMP_POS_PATH_R. Field in data file exceeds maximum length

As you can see emp_pos_path_r is FILLER in the control file. So I don't need to load it to the table. I admit that there could be a long data in this field in Data file.

How shall I change the Control file to load the data to the Database?

gsalem gsalem · Accepted Answer · 2019-12-30T22:13:56

The default max length for char (and filler) is 255, so if one of your filler fields can exceed this, add char(...) before the filler keyword. '...' is the max that you have.

Oracle SQL Loader: FILLER Field in data file exceeds maximum length

2 Answers