Multiple WHEN conditions in SQL*Loader [message #301423] |
Wed, 20 February 2008 09:53 |
amcghie
Messages: 35 Registered: March 2005 Location: Sunny Dubai
|
Member |
|
|
Hello all
I appear to be having some issues with SQL*Loader and was hoping someone may be able to tell me what is wrong with my control file. Basically I only want to load data where the 'status' is equal to 'SUCCESS' or'FAILURE'.
I have made a simple example to demonstrate my issue:
CREATE TABLE sqlldr_test
( trans_id NUMBER NOT NULL
, trans_dtm DATE NOT NULL
, status VARCHAR2(10) NOT NULL );
My Control is as follows:
LOAD DATA
APPEND
INTO TABLE sqlldr_test
WHEN status = 'SUCCESS'
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
( trans_id INTEGER EXTERNAL
, trans_dtm TIMESTAMP "YYYY-MM-DD HH24MISS"
, status CHAR )
INTO TABLE sqlldr_test
WHEN status = 'FAILURE'
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
( trans_id INTEGER EXTERNAL
, trans_dtm TIMESTAMP "YYYY-MM-DD HH24MISS"
, status CHAR )
The contents of my datafile is as follows:
1,2008-02-20 070000,SUCCESS
2,2008-02-20 070000,SUCCESS
3,2008-02-20 070001,PENDING
4,2008-02-20 070005,FAILURE
Given the control file above I would have expected lines 1,2 and 4 of the datafile to be loaded however as the query (and log file entry) below shows, only lines 1 and 2 have been loaded:
SELECT * FROM sqlldr_test;
TRANS_ID TRANS_DTM STATUS
---------- -------------------- ----------
1 20-FEB-2008 07:00:00 SUCCESS
2 20-FEB-2008 07:00:00 SUCCESS
The output of the sqlldr log file is as follows:
Quote: | SQL*Loader: Release 11.1.0.6.0 - Production on Wed Feb 20 15:13:13 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Control File: sqlldr.ctl
Data File: datafile.dat
Bad File: datafile.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 256000 bytes
Continuation: none specified
Path used: Conventional
Table SQLLDR_TEST, loaded when STATUS = 0X53554343455353(character 'SUCCESS')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TRANS_ID FIRST * , CHARACTER
TRANS_DTM NEXT * , DATETIME YYYY-MM-DD HH24MISS
STATUS NEXT * , CHARACTER
Table SQLLDR_TEST, loaded when STATUS = 0X4641494c555245(character 'FAILURE')
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
TRANS_ID NEXT * , CHARACTER
TRANS_DTM NEXT * , DATETIME YYYY-MM-DD HH24MISS
STATUS NEXT * , CHARACTER
Record 3: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Table SQLLDR_TEST:
2 Rows successfully loaded.
0 Rows not loaded due to data errors.
2 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Table SQLLDR_TEST:
0 Rows successfully loaded.
0 Rows not loaded due to data errors.
4 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 99072 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 4
Total logical records rejected: 0
Total logical records discarded: 2
Run began on Wed Feb 20 15:13:13 2008
Run ended on Wed Feb 20 15:13:17 2008
Elapsed time was: 00:00:03.75
CPU time was: 00:00:00.03
|
Any help would be greatly appreciated.
Kind Regards
Andy
|
|
|
|
Re: Multiple WHEN conditions in SQL*Loader [message #301465 is a reply to message #301462] |
Wed, 20 February 2008 13:59 |
|
Barbara Boehmer
Messages: 9099 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
When you have multiple when clauses, you have to reset the position for the first character of the first field for each when clause after the first one, so that it knows where to start:
LOAD DATA
APPEND
INTO TABLE sqlldr_test
WHEN status = 'SUCCESS'
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
( trans_id INTEGER EXTERNAL
, trans_dtm TIMESTAMP "YYYY-MM-DD HH24MISS"
, status CHAR )
INTO TABLE sqlldr_test
WHEN status = 'FAILURE'
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
( trans_id POSITION (1) INTEGER EXTERNAL
, trans_dtm TIMESTAMP "YYYY-MM-DD HH24MISS"
, status CHAR )
|
|
|
|
|