Home » RDBMS Server » Server Utilities » how to not load some records while loading in sqlloader
how to not load some records while loading in sqlloader [message #238451] Fri, 18 May 2007 02:12 Go to next message
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 #238581 is a reply to message #238451] Fri, 18 May 2007 11:01 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you try to play around with the WHEN clause in the control file?
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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.
icon7.gif  Re: how to not load some records while loading in sqlloader [message #238699 is a reply to message #238695] Sat, 19 May 2007 06:20 Go to previous message
moumita.c
Messages: 3
Registered: May 2007
Location: Kolkata
Junior Member
Thanks a lot Littlefoot. I used an approach almost similar to your suggestion of using temporary tables. Since sqlloader is not allowing me to check if A is null, I am sending all the records to discard file using 'WHEN' clause(Now it holds both A null records and B=5 records). From discard file I am loading all records into a discard table (Y) and then from that discard table Y I am selecting those records with 'A is NULL' and applying my PL/Sql code on those. In this way, I am loading only valid records into X as well as I can seggregate the records with A as null. Thanks again for your help Wink
Previous Topic: SQL Loader Problem.
Next Topic: Import database to UNIX
Goto Forum:
  


Current Time: Fri Dec 02 19:02:17 CST 2016

Total time taken to generate the page: 0.19074 seconds