| how to not load some records while loading in sqlloader [message #238451] |
Fri, 18 May 2007 02:12  |
moumita.c
Messages: 3
Registered: May 2007
Location: Kolkata
|
Junior Member |
|
|
Hi,
I am using sqlloader to load data from text file into oracle table. I want those records which contain null in certain fields(say,in job_type field) not to be loaded. I dont want these records to go into discard file also as I am using the discard file to store some other records(say those with job_type =1). Is it possible using control files in sqlloader?
[Updated on: Fri, 18 May 2007 02:15] Report message to a moderator |
|
|
|
|
|
| Re: how to not load some records while loading in sqlloader [message #238693 is a reply to message #238581] |
Sat, 19 May 2007 03:45  |
moumita.c
Messages: 3
Registered: May 2007
Location: Kolkata
|
Junior Member |
|
|
Yes I tried with WHEN caluse. But it caused two probelms.
1. It sent the data which failed the when clause to discard file, which I dont want.
2. It is unable to check whether the field is null.
Let me explain my requirement once again. I have a table X with fields A,B,C. I am sending the records with B=5 to discard file D using 'when' clause. Now, I want that if A is null then the record should not be loaded into table X, neither it shud go to the same discard file D. I need this redords(where A is null) separately so that I can do some processing on them.But I found that sqlloader can use only one discard file for one infile, so it is impossible to seggregate this data. Also, I am unable to check if A='NULL' (though it is posssible to check A<>'NULL' ..i dont know why).
Can you please help.
One solution could have been keeping a NOT NULL constraint on A in my X table and then retrieving the null records from Bad file after loading. But I dont want this null records to get mixed up with other records in bad file as there is a completely different process flow for records with A as NULL. I need to somehow separate this null records while loading.....
[Updated on: Sat, 19 May 2007 04:48] Report message to a moderator |
|
|
|
| Re: how to not load some records while loading in sqlloader [message #238695 is a reply to message #238693] |
Sat, 19 May 2007 04:50 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Well, if SQL*Loader can't do that, something else can.
You didn't mention your Oracle database version, but - if it supports external tables, this would be easily done through (PL/)SQL script.
If external table feature is not available to you, load ALL records into a temporary table. Once all records are in there (all but those in the discard file), you'll be able to write the same (PL/)SQL script as above and insert valid records into your "real" table immediately, process other records (those with A IS NULL) etc.
Or, you could even skip that "temporary table" and load all records directly, write database trigger(s) which would deal with not-that-valid records (A IS NULL ones). This *might*, though, lead to the mutating table error.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
