Issues with loading data through SQL Loader

Question

I am facing some issues in loading a CSV file through SQL Loader.

Here is the control file:

LOAD DATA 
INFILE 'mp.csv'
BADFILE mp.bad'
INSERT 
INTO TABLE mp_staging
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
mp_id INTEGER,
mp_name CHAR,
mp_desc CHAR,
oid CHAR,
ptye_cd CHAR,
creation_tmstmp TIMESTAMP WITH TIME ZONE "YYYY-MM-DD HH24:MI:SS,FF9",
modified_tmstmp TIMESTAMP WITH TIME ZONE "YYYY-MM-DD HH24:MI:SS,FF9",
last_generation_tmstmp TIMESTAMP WITH TIME ZONE "YYYY-MM-DD HH24:MI:SS,FF9",
deact_dt DATE,
)

Here are some sample records from CSV file:

1083,"Test3","","mills_b","PRIVATE",2000-12-06 14:29:21.17,1999-12-06 14:29:21.17,2011-    10-01 04:03:44.6

116,"Mike1","","mills_b","PRIVATE",1999-09-15 11:15:29.42,1999-09-15 11:15:59.663,2011-    10-01 04:01:23.923,,0,"F",""

I am facing two issues -

The second record gets inserted, but with a different mp_id in the table, as in the value inserted in the table is not 1083, but an alotgether differentnumber, which seems to be generated by a sequence, though I have not referred to any sequence in the control file.
The first record fails to load, with this error:

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

Can someone help me in right direction, so that I can debug these issues?

Pierre Laporte · Accepted Answer · 2012-06-22 09:52:21Z

2

The separator between seconds and milliseconds is a dot in your CSV file (1999-12-06 14:29:21.17) but a comma in your format (YYYY-MM-DD HH24:MI:SS,FF9)

My guess is that it may conflict with the FIELDS TERMINATED BY ',' directive.

Can you try this ?

creation_tmstmp TIMESTAMP WITH TIME ZONE "YYYY-MM-DD HH24:MI:SS.FF9",
modified_tmstmp TIMESTAMP WITH TIME ZONE "YYYY-MM-DD HH24:MI:SS.FF9",
last_generation_tmstmp TIMESTAMP WITH TIME ZONE "YYYY-MM-DD HH24:MI:SS.FF9",

answered Jun 22, 2012 at 9:52

Pierre Laporte

1,2258 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Incognito Over a year ago

But how come one record gets loaded, but the other fails. Even though, I tried what you suggested, and still get the same error

Incognito Over a year ago

BTW, that's not causing the recods to fail. You can specify the format with comma and have the actual data with dot, and it will load without any issue. I jsut tried that!

Alex Poole · Accepted Answer · 2012-06-22 11:02:26Z

1

The changing mp_id suggests you have an on-insert trigger on the mp_staging table which is always setting the ID from a sequence. If you need the ability to specify ID values, the trigger can check whether the passed :new.mp_id is null, and only assign the sequence value if it is. You also need to check that the sequence is set higher than any values you're loading, or you might get clashes later.

answered Jun 22, 2012 at 11:02

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

1 Comment

Incognito Over a year ago

Yes, that is what I thought the first time. But, there are no triggers in the system. I just created this temp table for loading data. Also, when I insert a record manually, the ID is preserved: INSERT INTO mp_staging (mp_id) VALUES(123); This inserts a record with MP_ID as 123

Incognito · Accepted Answer · 2012-06-22 12:35:57Z

OK, I have figured the error. It was all related to the control file definition. All I had to do was remove the data type declarations. As far as I figured, when you specify data type, you got to specify the limit of the field. I tried to specify POSITION(*), thinking that this will take care of variable length fields, but I again got the same error.

LOAD DATA  
INFILE 'mp.csv' 
BADFILE mp.bad' 
INSERT  INTO TABLE mp_staging
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
( mp_id , 
mp_name , 
mp_desc , 
oid , 
ptye_cd , 
creation_tmstmp TIMESTAMP WITH TIME ZONE "YYYY-MM-DD HH24:MI:SS,FF9", 
modified_tmstmp TIMESTAMP WITH TIME ZONE "YYYY-MM-DD HH24:MI:SS,FF9",              last_generation_tmstmp TIMESTAMP WITH TIME ZONE "YYYY-MM-DD HH24:MI:SS,FF9", 
deact_dt )

Collectives™ on Stack Overflow

Issues with loading data through SQL Loader

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related