Home » RDBMS Server » Server Utilities » Commit in sqlldr
Commit in sqlldr [message #246201] Wed, 20 June 2007 04:58 Go to next message
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 #246214 is a reply to message #246201] Wed, 20 June 2007 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't do it.
Either it processes the whole file and then commit and report all the bad records, either it stops after the number of errors it find.

Regards
Michel
Re: Commit in sqlldr [message #246217 is a reply to message #246214] Wed, 20 June 2007 05:41 Go to previous messageGo to next message
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 #246221 is a reply to message #246217] Wed, 20 June 2007 05:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't.
Why not using the bad file? Fix it and rename it to the original name if you don't want to change the name in loader script.

Regards
Michel
Re: Commit in sqlldr [message #246226 is a reply to message #246221] Wed, 20 June 2007 06:07 Go to previous messageGo to next message
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 #246400 is a reply to message #246226] Wed, 20 June 2007 15:40 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Could you use the file as an external table instead? It would allow you to load records using (PL/)SQL which might provide more flexibility.
Re: Commit in sqlldr [message #246490 is a reply to message #246400] Thu, 21 June 2007 01:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous message
Littlefoot
Messages: 20901
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)
This can have variations (and be written in a more efficient way), but I hope you got the point.


[EDIT]

This is Ross ./fa/1940/0/ and this is me ./fa/1637/0/

[Updated on: Thu, 21 June 2007 02:23]

Report message to a moderator

Previous Topic: Export
Next Topic: Control File
Goto Forum:
  


Current Time: Fri Dec 09 23:11:18 CST 2016

Total time taken to generate the page: 0.08097 seconds