| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Commit/Rollback using SQL*Loader
On Sun, 8 Feb 1998 13:12:13 -0800, "Murali Kazhipurath" <murali_at_jps.net> wrote:
>Is there any way to make SQL*Loader treat the whole load process as one
>transaction. i.e., if any kind of exception (including bad records) occurs
>during the load process, SQL*Loader should rollback the whole transaction.
>
>I am using the conventional method and by default the commit point under
>this method is 62 records. I thought about bumping up this number. But
>unfortunately, I am not in a position to know the upper limit of the number
>of records that can come in at any point of time.
I don't think you can prevent "good" records to be commited if there are som "bad" records in a bind array. Loader inserts records in arrays, but it seems like it treats every single record as individual insert transaction. So if in array of 64 records it finds 4 bad records, it rejects this 4 and inserts other 60. So IMHO there is no way to make SQL*Loader to treat the whole load job as a single transaction. Of course, you can allways load data into temporary table, check the log file for bad records and if all records were loaded succesfuly, insert them from temporary table into your destination table.
If despite of this you still want to load all of your records in a single bind array, you can specify an extremely large BINDSIZE parameter (as large as available memory permits) and an extremely large ROWS parameter (largere then you expect your number of records to be inserted will ever be). That way all of your records will be read into single bind aray and then iserted, although the performance of this might be questionable.
>Murali Kazhipurath
>(murali_at_jps.net)
Regards,
Jurij Modic Republic of Slovenia jurij.modic_at_mf.sigov.mail.si Ministry of Finance ============================================================The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Feb 09 1998 - 00:00:00 CST
 |
 |