Saturday, September 17, 2011

How to trim the column value while using SQL*LOADER

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.

1 comment:

  1. Hi...

    This blog was very useful..

    thank you and keep blogging..

    ReplyDelete