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?

Answers


  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.


Need Your Help

Doctrine2.3 and OneToOne cascade persist doesn't seem to work

php mysql orm doctrine2

I have two entites (User and UserPreferences) that I want to map OneToOne unidirectional.

The XML code runs properly, but junit fails with NoClassDefFound

java junit eclipse-3.4 xsltc

I am upgrading my environment from eclipse 3.3.1 and java 1.4 to eclipse 3.4.1 and java 1.5.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.