Scenario:
I was using sqlloader to load the data in my file data.csv to my table emp. The column size of lastname is varchar2(20), but the value in my csv file was having space appended to the name making the size more than 20. Hence when I use sqlloader it faisl with error.
Record 302: Rejected - Error on table emp, column lastname.
ORA-12899: value too large for column "SCHEMA"."EMP"."LASTNAME" (actual: 23, maximum: 20)
And this record goes to bad file. I have to go and check for such values and edit it manually for each and every one.
How can I use trim in my control file?
My Control file is as below:
OPTIONS (SKIP=1)
load data infile 'D:\Load\data.csv'
TRUNCATE into table emp fields
terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(ID,FIRSTNAME ,LASTNAME)
Solution:
You can add trim into control file as below:
OPTIONS (SKIP=1)
load data infile 'D:\Load\data.csv'
TRUNCATE into table emp fields
terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(ID "TRIM(:ID)",FIRSTNAME "TRIM(:FIRSTNAME)",LASTNAME"TRIM(:LASTNAME)")
This will solve the issue.
Hi...
ReplyDeleteThis blog was very useful..
thank you and keep blogging..