CODEWITHSUNDEEP
oracle-databaseoracle10goracle11gsql-loader
ozzboy
ozzboy

Reputation: 2720

Whats wrong with this Oracle CTL File or data?

My data is this:

ID,SCORE_DATE,TYPE,SCORE,RAW_SCORE,RANK
A1234,2012-09-05 23:59:59,FOOTBALL_TEAM_MIDDLE_AND_OLD_1234,10,0.123,1
A5678,2012-09-05 23:59:59,FOOTBALL_TEAM_MIDDLE_AND_OLD_1234,20,0.456,2

CTL FILE:

load data           
infile 'E:\Data\Sample.csv'
badfile 'E:\Data\Sample.bad'
APPEND into table TABLE1    
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS               
(   
 DRIVER,
 STARTDTE "to_date(SUBSTR(:STARTDTE,1,10),'YYYY-MM-DD') - 27",
 TYPE  CONSTANT 'FOOTBALL',
 SCORE   ,
 RANKSCORE ":SCORE",
 RANK    ,
 ENDDTE "to_date(SUBSTR(:STARTDTE,1,10),'YYYY-MM-DD')",
 LOADDT     "sysdate"
)

Here is my table structure:

CREATE TABLE "TABLE1"
  (
    "DRIVER" VARCHAR2(50 BYTE),
    "STARTDTE" DATE,
    "SCORE"           NUMBER,
    "ENDDTE" DATE,
    "TYPE" VARCHAR2(20 BYTE),
    "RANK"       NUMBER,
    "RANKSCORE"  NUMBER,
    "LOADDT"     VARCHAR2(20 BYTE)
  );

I am getting an error message for every row loaded: Record 3: Rejected - Error on table TABLE1, column SCORE. ORA-01722: invalid number

Whats wrong?

Upvotes: 1

Views: 2272

Answers (1)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

  1. TYPE is an oracle keyword, you are better off not using such column names.

  2. There is no TYPE column in your table definition . If the column exists, move the constant assignment to the end of the control file, after all the fields have been read.

    STARTDTE "to_date(SUBSTR(:STARTDTE,1,10),'YYYY-MM-DD') - 27", TYPE CONSTANT 'FOOTBALL', SCORE ,

And if you are trying to ignore the field that has "FOOTBALL_TEAM_MIDDLE_AND_OLD_1234" as data, you'll need to add

THIRD_COLUMN FILLER,

to your control file to ignore that data. Something like..

load data           
infile 'E:\Data\Sample.csv'
badfile 'E:\Data\Sample.bad'
APPEND into table TABLE1    
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS               
(   
 DRIVER,
 STARTDTE "to_date(SUBSTR(:STARTDTE,1,10),'YYYY-MM-DD') - 27",
 THIRD_COLUMN FILLER,
 SCORE   ,
 RANKSCORE ":SCORE",
 RANK    ,
 ENDDTE "to_date(SUBSTR(:STARTDTE,1,10),'YYYY-MM-DD')",
 LOADDT     "sysdate"
 TYPE  CONSTANT 'FOOTBALL',
)

should fix the errors you are getting.

Upvotes: 2

Related Questions