Reputation: 613
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?
Upvotes: 0
Views: 7719
Reputation: 11
Oracle 11gR2
I have solved this problem by adding CHAR(4000) after the FILLER keyword. You can change the size according to the length of your string.
Upvotes: 1
Reputation: 2028
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.
Upvotes: 2