SQL Loader in one transaction
bob123
How can I do a load with SQL Loader
in one transaction (i.e. all or nothing)
rows parameter is too low for my data
10.2.0.4
Thanks in advance
ddf
Provide more information if you want an answer, such as your ctl file,
total rows in your data file, error you're seeing, logfile from the
problematic sql*loader session.
David Fitzjarrell
Mladen Gogala
Make the file accessible as an external table and do "create as select".
That would be a single transaction, succeeds or fails as a whole.
Mark D Powell
I think what you want to do is set errors=0
From 10gR2 Utilities Manual >>
ERRORS specifies the maximum number of insert errors to allow. If the
number of errors exceeds the value specified for ERRORS, then
SQL*Loader terminates the load. To permit no errors at all, set
ERRORS=0. To specify that all errors be allowed, use a very high
number. <<
HTH -- Mark D Powell --
Mladen Gogala
> 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.
Mark D Powell
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
