Home » SQL & PL/SQL » SQL & PL/SQL » loading with external table rejected records not written to bad or discard file (Oracle Database 11g Enterprise Edition Release 11.1.0.6.0)
loading with external table rejected records not written to bad or discard file [message #403272] Thu, 14 May 2009 10:02 Go to next message
ndahafa
Messages: 16
Registered: November 2006
Junior Member
I am loading data from flat files using external tables. I have a primary key on my target table and understandably records that violate the primary key constraint get rejected.

The problem is: If the flat file contains a mixture of records that already exist and records that don't exist in the target table, none of the records in that file get loaded and this is understandable (found an explanation at http://www.gennick.com/ETLandBad.html)

However no bad file or discard file is created so that I can go back to trace those records and insert them in the target table using another way.

please help me find a solution
Re: loading with external table rejected records not written to bad or discard file [message #403274 is a reply to message #403272] Thu, 14 May 2009 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/m/403139/136107/#msg_403123
Re: loading with external table rejected records not written to bad or discard file [message #403276 is a reply to message #403272] Thu, 14 May 2009 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It is not clear what is your problem.
Do you want ignore the error?
Do you want trace the error?
Do you want to stop at first error?
...

Regards
Michel
Re: loading with external table rejected records not written to bad or discard file [message #403280 is a reply to message #403276] Thu, 14 May 2009 10:20 Go to previous messageGo to next message
ndahafa
Messages: 16
Registered: November 2006
Junior Member
hi Michel,

since a file can be rejected altogether as long as it contains some 'bad' records, I want to be able to go back to such a file so that I can get the 'good' records and load them into the target table.
external tables does not write those records to a bad file or discard file so I have no way of knowing that there were in fact some 'good' records in the file.

i hope this is clearer
Re: loading with external table rejected records not written to bad or discard file [message #403283 is a reply to message #403280] Thu, 14 May 2009 10:28 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The "bad file" is only written when the data can't be transferred from the file into the external table.

When I understand your case correctly at that stage there isn't any error, so there aren't any bad files.

The problem is in whatever is done to transfer the data to the target table. And you never told us how you do that.
Re: loading with external table rejected records not written to bad or discard file [message #403286 is a reply to message #403280] Thu, 14 May 2009 10:38 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
external tables does not write those records to a bad file or discard file

Unless you specify them in "badfile" and/or "discardfile" parameters.

Quote:
I want to be able to go back to such a file so that I can get the 'good' records and load them into the target table.


Use "reject limit unlimited" clause.

Regards
Michel

[Updated on: Thu, 14 May 2009 10:38]

Report message to a moderator

Previous Topic: PARALLEL query with ROWNUM
Next Topic: Order of where clause in SQL query. (merged 3)
Goto Forum:
  


Current Time: Sat Feb 08 19:23:06 CST 2025