OraFAQ Forum: Server Utilities » Multiple WHEN conditions in SQL*Loader

Home » RDBMS Server » Server Utilities » Multiple WHEN conditions in SQL*Loader

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Multiple WHEN conditions in SQL*Loader [message #301462 is a reply to message #301423]

Wed, 20 February 2008 13:23

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Hmmm, when you use comma delimited as opposed to fixed format, can you use

column_name='{value}'

with multiple when clauses?

Report message to a moderator

Re: Multiple WHEN conditions in SQL*Loader [message #301465 is a reply to message #301462]

Wed, 20 February 2008 13:59

Barbara Boehmer
Messages: 9090
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 )

Report message to a moderator

Re: Multiple WHEN conditions in SQL*Loader [message #301466 is a reply to message #301465]

Wed, 20 February 2008 14:02

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Shoot, I'm sorry I didn't remember that one. I think Barbara answered this same type of question before.

Report message to a moderator

Re: Multiple WHEN conditions in SQL*Loader [message #301469 is a reply to message #301466]

Wed, 20 February 2008 15:19

amcghie
Messages: 35
Registered: March 2005
Location: Sunny Dubai

Member

Barbara you are an absolute star!

Thank-you so much, not sure how I missed that in the Doco

Report message to a moderator

Previous Topic:	Loadin filename in a table
Next Topic:	SqlLoader Substring in Control File

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 23:50:38 CDT 2024