Re: SQL Loader in one transaction
Date: Tue, 21 Sep 2010 08:05:21 -0700 (PDT)
Message-ID: <b080c20a-7e12-48e5-a90f-432e60a8653f_at_w4g2000vbh.googlegroups.com>
On Sep 20, 5:04 pm, Mladen Gogala <mgog..._at_no.address.invalid> wrote:
> On Mon, 20 Sep 2010 06:07:53 -0700, Mark D Powell wrote:
> > I think what you want to do is set errors=0
>
> I don't think so. If several batches are committed when an error occurs,
> the file will be partially loaded, precisely what the OP doesn't want to
> happen. One way of accomplishing the task at hand is by using an external
> table, another one is by writing a custom load procedure in some
> scripting language. SQL*Loader is not the right tool in this case.
>
> --http://mgogala.byethost5.com
I should have mentioned trying a direct path load where "The default is to read all rows and save data once at the end of the load." so with errors set to zero the entire load file should make it or fail as a single transaction. This would require enought undo exist to handle the entire load as a single transaction. If the load fails the indexes would be unusable and have to be rebuilt.
A small test with 10 rows where the last was bad being loaded into a table with 3 rows already in it ran successfully with only 3 rows appearing after the run.
$ sqlldr mpowel01 control=mark.ctl errors=0 direct=y Password:
SQL*Loader: Release 9.2.0.8.0 - Production on Tue Sep 21 11:00:08 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Load completed - logical record count 10.
However no data was inserted:
SQL*Loader: Release 9.2.0.8.0 - Production on Tue Sep 21 11:00:08 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Control File: mark.ctl Data File: mark.ctl Bad File: mark.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 0 Continuation: none specified Path used: Direct
Table MPOWEL01.MARKTEST, loaded from every logical record. Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
FLD1 FIRST * , O(") CHARACTER
FLD2 NEXT * , O(") CHARACTER
FLD3 NEXT * , O(") CHARACTER
Record 10: Rejected - Error on table MPOWEL01.MARKTEST, column FLD1. ORA-01401: inserted value too large for column
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
SQL*Loader: Release 9.2.0.8.0 - Production on Tue Sep 21 11:00:08 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Control File: mark.ctl Data File: mark.ctl Bad File: mark.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL Number to skip: 0 Errors allowed: 0 Continuation: none specified Path used: Direct
Table MPOWEL01.MARKTEST, loaded from every logical record. Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ----
---------------------
FLD1 FIRST * , O(") CHARACTER
FLD2 NEXT * , O(") CHARACTER
FLD3 NEXT * , O(") CHARACTER
Record 10: Rejected - Error on table MPOWEL01.MARKTEST, column FLD1. ORA-01401: inserted value too large for column
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table MPOWEL01.MARKTEST:
0 Rows successfully loaded.
1 Row not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Column array rows : 5000 Stream buffer bytes: 256000 Read buffer bytes: 1048576 Total logical records skipped: 0 Total logical records rejected: 1 Total logical records discarded: 0 Total stream buffers loaded by SQL*Loader main thread: 0 Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Tue Sep 21 11:00:08 2010
Run ended on Tue Sep 21 11:00:12 2010
Elapsed time was: 00:00:03.33 CPU time was: 00:00:00.04
HTH -- Mark D Powell -- Received on Tue Sep 21 2010 - 10:05:21 CDT