sqlldr error

[syncdba]

Hi,
I'm trying to run this sqlldr

LOAD DATA
INFILE 'C:\ESTA\test1.csv'
REPLACE
INTO TABLE stage_internal_learners
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
INTERNAL_LEARNER_IDENTIFIER "stage_internal_learner_s.NEXTVAL",
EMPLOYEE_NUMBER "LTRIM(RTRIMEMPLOYEE_NUMBER))" ,
STATUS "LTRIM(RTRIMSTATUS))" ,
MANAGER_USERNAME "LTRIM(RTRIMMANAGER_USERNAME))" ,
EMPLOYEE_TYPE "LTRIM(RTRIMEMPLOYEE_TYPE))" ,
JOB_TYPE "LTRIM(RTRIMJOB_TYPE))" ,
LEGAL_ENTITY_CODE "LTRIM(RTRIMLEGAL_ENTITY_CODE))" ,
TITLE "LTRIM(RTRIMTITLE))",
FIRST_NAME "LTRIM(RTRIMFIRST_NAME))",
LAST_NAME "LTRIM(RTRIMLAST_NAME))",
MIDDLE_NAME "LTRIM(RTRIMMIDDLE_NAME))" ,
WORK_PHONE "LTRIM(RTRIMWORK_PHONE))" ,
FAX "LTRIM(RTRIMFAX))" ,
EMAIL "LTRIM(RTRIMEMAIL))" ,
USERNAME "LTRIM(RTRIMUSERNAME))" ,
ADDRESS1 "LTRIM(RTRIMADDRESS1))" ,
ADDRESS2 "LTRIM(RTRIMADDRESS2))" ,
ADDRESS3 "LTRIM(RTRIMADDRESS3))" ,
CITY "LTRIM(RTRIMCITY))" ,
STATE "LTRIM(RTRIMSTATE))" ,
ZIP "LTRIM(RTRIMZIP))" ,
COUNTRY "LTRIM(RTRIMCOUNTRY))" ,
SUFFIX "LTRIM(RTRIMSUFFIX ))" ,
JOB_TITLE "LTRIM(RTRIMJOB_TITLE))" ,
EMPLOYEE_PREFERRED_NAME "LTRIM(RTRIMEMPLOYEE_PREFERRED_NAME))" ,
SET_OF_BOOKS_ID "LTRIM(RTRIMSET_OF_BOOKS_ID))" ,
COST_CENTER "LTRIM(RTRIMCOST_CENTER))" ,
SOURCE_SYSTEM "LTRIM(RTRIMSOURCE_SYSTEM))",
DATA_SOURCE "LTRIM(RTRIMDATA_SOURCE))" ,
CREATION_DATE SYSDATE )

but getting error

Record 1: Rejected - Error on table STAGE_INTERNAL_LEARNERS.
ORA-01400: cannot insert NULL into ("XXHD_TP2"."STAGE_INTERNAL_LEARNERS"."FIRST_NAME")

Record 2: Rejected - Error on table STAGE_INTERNAL_LEARNERS.
ORA-01400: cannot insert NULL into ("XXHD_TP2"."STAGE_INTERNAL_LEARNERS"."FIRST_NAME")

Record 3: Rejected - Error on table STAGE_INTERNAL_LEARNERS.
ORA-01400: cannot insert NULL into ("XXHD_TP2"."STAGE_INTERNAL_LEARNERS"."FIRST_NAME")

I have checked all the data in first_name, there is no "NULL" data.

 

[Thargy]

syncdba,

I note that you are doing combinations of rtrim and ltrim. Since you aren't deleting anything other than white spaces, why don't you just use TRIM?

Can you check your data that when you TRIM the FIRST_NAME field, it doesn't produce any nulls?

Can you post your table structure and sample data, then I can try to reproduce the problem here, and help some more.

As a further suggestion, you might temporarily drop the not null constraint, get the data into the table, and then query for nulls in the FIRST_NAME column. This would show you the dud data, which you could then correct. Then truncate the table, reinstate the constraint, and load 'er up!

Regards

Tharg

Grinding away at things Oracular