Oracle sqlldr - how to load first few characters of a string to Database in sqlldr

Today I had a Unique challenge while loading Data from text files to Oracle Database using sqlldr.

Many records were getting discarded as bad records. After investigation, I found that, the data is getting rejected because of a specific column whose length is less compared to the length of data in text file. I was getting error as below:

Error on table tbl_test, column col2.
Field in data file exceeds maximum length

The size and datatype of column col2 is varchar2(100).

To load all the data to the Database without any bad records, there are two options.
1. Increase the column width to match the length of data
OR
2. Truncate the data to 100 chars before loading to DB

I preferred the second option for below reasons:
a) First 100 chars of the string is sufficient for my analytics
b) Less space consumption
c) I am not sure of the maximum length that can be for that column in text file.

Now I am decided to trim the data to first 100 chars before loading to DB.
Let's see how to load first few characters of a string to Database in sqlldr?

We have to modify the ctl file (control file) as below:

Old ctl file:

LOAD DATA
INFILE "INFILE_NAME"
BADFILE "BAD_FILE"
DISCARDFILE "DISCARD_FILE"
APPEND into table tbl_test
fields terminated by '\t'
trailing nullcols
(
col1,
col2,
col3,
col4 sysdate
)

Modified ctl file:

LOAD DATA
INFILE "INFILE_NAME"
BADFILE "BAD_FILE"
DISCARDFILE "DISCARD_FILE"
APPEND into table tbl_test
fields terminated by '\t'
trailing nullcols
(
col1,
col2 CHAR(1000) "substr(:col2,1,100)",
col3,
col4 sysdate
)

Change Details:

1. We are using substring function to load first 100 chars
2. If you don't specify length of the input character data, sqlldr uses default of 255 characters. Hence by explicitly mentioning CHAR(1000), we are instructing sqlldr to read all data where col2 length is less than or equal to 1000 chars. We have to increase this number if we see data having higher length.

For more Oracle Tips, Click here.