using multiple conditions in sql loader

Question

I was trying to load records from a file to an oracle table based on conditions. Since OR operator and WHEN IN statements do not work in sql loader, I tried multiple insert to a table. However, only the records that match the first condition were loaded in the table and the records that matched the second condition were not loaded. My control file looks like below:

Options (BINDSIZE = 7340032)
Load Data
APPEND
INTO TABLE TEMP_GLOBAL_ONE_FEE_REBATE WHEN ACT_TYPE = 'SR'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
        RPT_YEAR,
        RPT_MONTH,
        ........
        ........
 )


INTO TABLE TEMP_GLOBAL_ONE_FEE_REBATE WHEN ACT_TYPE = 'SL'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
        RPT_YEAR,
        RPT_MONTH,
        ........
        ........
 )

** As mentioned, only those records with act_type = 'SR' were loaded and those records with act_type = 'SL' were not loaded.

Any idea how to go on this? Thank you.

Answer 1

Your problem is that the first INTO command reads the file from beginning to end, and then the second INTO command picks up where the first one finished - which is the end of the file in your case. To achieve what you are trying to do, you're gonna have to use two seperate sql loader commands. See this post on AskTom for reference -

https://asktom.oracle.com/pls/apex/f?p=100:11:::YES:RP:P11_QUESTION_ID:3181887000346205200

A more elegant solution would be reading the data from the file using a pl/sql procedure and UTL_FILE package, but this is only worth the trouble if the import is something that happens a lot, and not a one time thing.

Answer 2

You should use POSITION(1) in the first column of each field list:

To force record scanning to start in a specific location, you use the POSITION parameter.

Control file

Options (BINDSIZE = 7340032)
Load Data
APPEND
INTO TABLE TEMP_GLOBAL_ONE_FEE_REBATE WHEN ACT_TYPE = 'SR'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
        RPT_YEAR POSITION(1),
        RPT_MONTH,
        ........
        ........
)


INTO TABLE TEMP_GLOBAL_ONE_FEE_REBATE WHEN ACT_TYPE = 'SL'
FIELDS TERMINATED BY '|' TRAILING NULLCOLS
(
        RPT_YEAR POSITION(1),
        RPT_MONTH,
        ........
        ........
)

Sample data

2015|01|SL
2015|02|SL
2015|03|SL
2015|03|SR
2015|04|SR
2015|04|XX

This will load 2 rows with 'SR', 3 rows with 'SL', and discard one row.

References

1. SQL*Loader with multiple WHENs is rejecting all rows, the Ask Tom queston mentioned in the accepted answer
2. Distinguishing Different Input Record Formats in SQL*Loader Control File
3. Loading Data into Multiple Tables in SQL*Loader Control File Reference