Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sqlldr and NULL DATES error ORA-01841

timvincentJan 31 2013 — edited Jan 31 2013
Dear All,

I'm struggling to load a table when the data file contains nulls for date value. i get the following error:

Record 1: Rejected - Error on table W_PARTY_PER_D, column BIRTH_DT.
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Thanks for any pointers, detials below:

My Control file is:

load data
infile '/media/sf_timv/w_party_per_d_40pc.csv'
into table w_party_per_d
fields terminated by "," optionally enclosed by '"'
( ROW_WID,
BIRTH_DT DATE "YYYY-MM-DD HH24:MI:SS" NULLIF BIRTH_DT=BLANKS,
DATASOURCE_NUM_ID,
EDUCATION_BCKGND_NAME,
INTEGRATION_ID,
MARITAL_STAT_NAME,
SEX_MF_NAME )

Table schema:

create table TTUSER.W_PARTY_PER_D (
ROW_WID NUMBER(38) DEFAULT 0 NOT NULL,
BIRTH_DT DATE,
DATASOURCE_NUM_ID NUMBER(38) NOT NULL,
EDUCATION_BCKGND_NAME VARCHAR2(50 CHAR) ,
INTEGRATION_ID VARCHAR2(80 CHAR) NOT NULL,
MARITAL_STAT_NAME VARCHAR2(80 CHAR) ,
SEX_MF_NAME VARCHAR2(50 CHAR) ,
primary key (ROW_WID));

And a snip from the data file:

133927080,NULL,2,NULL,"3-4JDPG7",NULL,NULL
133927081,NULL,2,NULL,"3-3OEB-14465",NULL,NULL
133927082,NULL,2,NULL,"3-41FS-599",NULL,NULL
133927083,NULL,2,NULL,"3-6HGH-5002",NULL,NULL
133927084,NULL,2,NULL,"3-8JM-287",NULL,NULL
133927086,NULL,2,NULL,"3-433N-124",NULL,NULL
133927089,NULL,2,NULL,"3-ANM-1979",NULL,NULL
133927090,NULL,2,NULL,"3-41G1-1778",NULL,NULL
133927091,NULL,2,NULL,"3-41G1-585",NULL,NULL
133927092,1970-03-03 10:30:00,2,NULL,"3-3IH4-3407",NULL,NULL
This post has been answered by Solomon Yakobson on Jan 31 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 28 2013
Added on Jan 31 2013
3 comments
3,607 views