| Commit in sqlldr [message #246201] |
Wed, 20 June 2007 04:58  |
ssunda6
Messages: 28
Registered: June 2007
|
Junior Member |
|
|
Hi All,
when we use sqlldr , all correct records get inserted into table and bad records go into bad file. And the records inserted into the table are committed.
I do not want the records to be committed in the table if there are any bad records. And I want all the bad records to be present in the bad file for analysis
I tried using "direct" option to load. Though the commit happens at the end of load, the correct records inserted into table get committed. I used "direct =y and errors=0" which will make sure that no records are inserted into table if there is single bad record. But in this case I will get only the first bad record and the sqlldr process stops.
Any inputs on this will be very helpful.
Regards,
ssunda.
|
|
|
|
|
|
| Re: Commit in sqlldr [message #246217 is a reply to message #246214] |
Wed, 20 June 2007 05:41  |
ssunda6
Messages: 28
Registered: June 2007
|
Junior Member |
|
|
Hi ,
Thanx for the reply.
The client does not want to use staging table also.
Is there any way that we can rollback the recors that are inserted. Because if there is any error , they want to modify the error records and then give the same file as input.
Heard that there is some concept called transactional commit which will serve my purpose. But did not find any info on that.
How can we achieve this?
Regards,
ssunda.
[Updated on: Wed, 20 June 2007 05:42] Report message to a moderator |
|
|
|
|
|
| Re: Commit in sqlldr [message #246226 is a reply to message #246221] |
Wed, 20 June 2007 06:07 |
ssunda6
Messages: 28
Registered: June 2007
|
Junior Member |
|
|
Thanx Michel. Using bad file as input is a better option.
There is an error in my previous message.
I mentioned that when "direct=y and errors=0" is used, the sqlldr does not insert any rows even if there is one bad record.
But the actual behaviour is it is inserting the records till it encounters first bad record. And the correct records before the 1st bad record are committed in the table.
Regards,
ssunda.
[Updated on: Wed, 20 June 2007 06:49] Report message to a moderator |
|
|
|
|
|
| Re: Commit in sqlldr [message #246490 is a reply to message #246400] |
Thu, 21 June 2007 01:47 |
ssunda6
Messages: 28
Registered: June 2007
|
Junior Member |
|
|
Hi LittleFoot,
I went thru the External Table concepts.
Please confirm if the procedure I am following is correct and work according to my requirements.
As I dont want any records to be inserted into Target table if there are any bad records, I will use External table.
1. First load data from data file to External table. If there are any bad records( check for existence of bad file) , I will not load the data from external table to target table.
2. Modify the bad records, and insert only the modified bad records into External table by changing the name of input data file.
3. Then load all records from External table to target table
External table is like staging table(temporary table). So, i am not sure if that table will exist till I modify the bad records and insert them into external table.
This is the first time I am using sqlldr,and external tables. Inputs on this would be of great help.
Regards,
ssunda.
|
|
|
|
| Re: Commit in sqlldr [message #246499 is a reply to message #246490] |
Thu, 21 June 2007 02:20 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You don't LOAD into an EOT, you just move a file to that location.
You could then run SELECT * FROM my_eot, and the file will be parsed with all bad records going to the bad file.
You would then need to check the bad file was empty, and if so you could then INSERT INTO my_table SELECT * FROM my_eot.
This effectively loads twice, although Oracle seems to be pretty clever with memory - if the file has not changed it appears to re-use whatever is in cache.
Ross Leishman
|
|
|
|
| Re: Commit in sqlldr [message #246500 is a reply to message #246490] |
Thu, 21 June 2007 02:20 |
 |
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You do NOT load anything into an external table - your "data file" already acts an one and you do not use SQL*Loader at all.
PL/SQL procedure might do the job; here is some kind of a pseudocode which will show you what I meant.
LOOP through all records in a data file
INSERT a record into a (global?) temporary table
IF a record is NOT OK THEN
fix it
END IF
END LOOP
INSERT records into a target table (as SELECT from a temporary table)
[EDIT]
This is Ross  and this is me 
[Updated on: Thu, 21 June 2007 02:23] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
