| problem with PL/SQL COMMIT [message #2910] |
Tue, 20 August 2002 22:41  |
raji
Messages: 30
Registered: February 2002
|
Member |
|
|
I need to upload a huge text-file(10MB-about 1 lac records) to database.I'm trying to commit at the end.But my procedure commits only about 20,000 records and exits.
I'm not able to find out the reason.Is there any problem in committing after huge inserts or is there anything in DB so that i can configure and insert all my records?
anyone please help me out.
|
|
|
|
| Re: problem with PL/SQL COMMIT [message #2912 is a reply to message #2910] |
Wed, 21 August 2002 02:34  |
Nick
Messages: 64
Registered: February 2000
|
Member |
|
|
Raji,
Loop thru records and commit after every 100 or 200 records.
Declare
vCount Number := 0;
Cursor c is
Select abc
from Table ;
Begin
For cRec in c loop
if mod(vCount,100) = 0 Then
Commit ;
end if ;
.... Do your processing
vCount := vCount + 1 ;
End Loop ;
End ;
|
|
|
|
| Re: problem with PL/SQL COMMIT [message #2916 is a reply to message #2910] |
Wed, 21 August 2002 08:50 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
If your procedure is exiting prematurely, then there must be a reason. Are you handling exceptions in your procedure? Are the being suppressed via 'when others then null' or similar?
Your rollback segments are probably not sized correctly for the types of transactions you are attempting. My first recommendation would be to make them as large as necessary to handle this load.
The fallback option, and a poor one because of several reasons, is to commit every so often (as Nick mentions in his response). However, this approach does not allow you to rollback your transaction. If your load fails for another reason part way through, how do you recover? Somehow identify the rows you've already loaded, delete them, and start over? Easy for an empty table but otherwise an issue.
If you have to use this approach, I would try a commit window much higher than 100-200 - maybe something like 10,000 or higher. Also, don't forget to commit one last time outside of your loop.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
