Home » RDBMS Server » Server Utilities » Bad File not creating
Bad File not creating [message #211489] Thu, 28 December 2006 23:28 Go to next message
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 #211492 is a reply to message #211489] Thu, 28 December 2006 23:45 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
If you named your controlfile as myctl.ctl then by default the badfile will be crated as myctl.bad. Did you check that?
>>error like "Maximum length exceeded"
Should be available in your logfile which by default would be named as myctl.log
Re: Bad File not creating [message #211494 is a reply to message #211489] Thu, 28 December 2006 23:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #211506 is a reply to message #211504] Fri, 29 December 2006 01:05 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
vishal gupta
... but it should come in bad record and a bad file should be generated ...

It would, perhaps, happen if you worked for Oracle corp., particularly for the SQL*Loader developing team. But, being as it is, nice guys and girls from Oracle decided not to create a BAD, but DISCARD file from the sample data you provided.
Re: Bad File not creating [message #211727 is a reply to message #211489] Mon, 01 January 2007 22:36 Go to previous messageGo to next message
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
Re: Bad File not creating [message #211742 is a reply to message #211727] Tue, 02 January 2007 01:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Well, you could read the doco....

I don't know what this has to do with your previous question though ./fa/1600/0/

This tells you how to utilise the table default.

Or you could just use a SQL operator

Ross Leishman
Re: Bad File not creating [message #211754 is a reply to message #211489] Tue, 02 January 2007 02:55 Go to previous message
vishal gupta
Messages: 111
Registered: March 2001
Senior Member
Thanks Rleishman for your reply.
Previous Topic: Sql Loader
Next Topic: tkprof error in command prompt
Goto Forum:
  


Current Time: Tue Dec 06 00:25:43 CST 2016

Total time taken to generate the page: 0.17978 seconds