CODEWITHSUNDEEP
oracle-databasesql-loader
Incognito
Incognito

Reputation: 3094

Issues with loading data through SQL Loader

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 -

  1. 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.

  2. 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?

Upvotes: 1

Views: 10012

Answers (3)

Incognito
Incognito

Reputation: 3094

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 )

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191275

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.

Upvotes: 1

Pierre Laporte
Pierre Laporte

Reputation: 1215

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",

Upvotes: 2

Related Questions