| Bad File not creating [message #211489] |
Thu, 28 December 2006 23:28  |
vishal gupta
Messages: 111
Registered: March 2001
|
Senior Member |
|
|
Hi,
I am using SQLLoader to load data from .csv to oracle.
My control file is as (Only first few lines)
LOAD DATA
CHARACTERSET 'UTF8'
INFILE ' '
REPLACE
INTO TABLE TMP_CONTACTS
WHEN (Flag != ' ')
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
First_Name CHAR(50),
Last_Name CHAR(50),
Field3 FILLER,
.................
As you can see,first field is defined as CHAR(50).
But,one of the records in the .csv file is more than 50 (55 to be exact) and this record is not getting loaded in the oracle tables also but no bad file is being generated ?
This record does satisfy the When clause also.So,if in this record the number of characters were less than 50 ,it is getting loaded in the oracle table(I have tried this).
My question is why the bad file is not being created as it should give me an error like "Maximum length exceeded".
I am using Oralce 9i.
regards
|
|
|
|
|
|
| Re: Bad File not creating [message #211494 is a reply to message #211489] |
Thu, 28 December 2006 23:54  |
vishal gupta
Messages: 111
Registered: March 2001
|
Senior Member |
|
|
Thanks mahesh for your reply.
There is no bad file.i have checked that.
This is what log file says about this record .i.e record no.2
Record 2: Discarded - failed all WHEN clauses.
I am not getting this.The Flag column for this record contains a value which is 'Y' and my when clause only validates the Flag column(You can take a look at my .ctl file above).
So why is this giving me an message like failed all WHEN clauses.
regards
|
|
|
|
| Re: Bad File not creating [message #211502 is a reply to message #211494] |
Fri, 29 December 2006 00:46 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
This is not an error (bad), it is a discard. ie. The record was not in error, it just didn't load because you asked it not to in your WHEN clause(s).
Look in the .dis file instead.
Ross Leishman
|
|
|
|
| Re: Bad File not creating [message #211504 is a reply to message #211489] |
Fri, 29 December 2006 00:53 |
vishal gupta
Messages: 111
Registered: March 2001
|
Senior Member |
|
|
Hi,
Please look at my first post .
| Quote: | As you can see,first field is defined as CHAR(50).
But,one of the records in the .csv file is more than 50 (55 to be exact) and this record is not getting loaded in the oracle tables also but no bad file is being generated ?
|
You are right ,it is coming in discard file but it should come in bad record and a bad file should be generated as the number of characters is more than defined which is 50 and ideally it should give "Maximum excedded" message.
regards
|
|
|
|
|
|
| Re: Bad File not creating [message #211727 is a reply to message #211489] |
Mon, 01 January 2007 22:36 |
vishal gupta
Messages: 111
Registered: March 2001
|
Senior Member |
|
|
Hi,
Thanks LittleFoot for your reply.
Anyway,my logic involves looking for a .bad file in order to give a message to the user that there was a error.
But,it seems that in some situation ,there is ambiguity in the results in sqlloader.
is there any possible way that i can set a default for a column in the control file.
For example,if the column does not have a value,then set it to 'V' similar to default we have in oracle database.
regards
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
