Using Multiple WHEN clause in SQL Loader

on

Recently, I was working on Oracle 11i customer interface and was making a loader file to load the data from csv file to custom tables, on which I apply the validations and then load the data from these custom tables to Oracle Standard Interface table.

I had following types of tables:
  1. Header Table- Stores data for header and site record
  2. Contact Table- Stores data for contact points (email and telephone details)
So my requirement was to load the data into these tables on the basis of record type (present in the incoming file). So, I had written the below control file to load the data:




OPTIONS (SKIP=3)
LOAD DATA TRUNCATE 
INTO TABLE XXTC_CUSTOMER_HEADER
WHEN RECORD_TYPE = 'HEADER' 
FIELDS TERMINATED BY ","  
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS

(   
SEQ_NUM                         "ltrim(rtrim(:SEQ_NUM))",
RECORD_TYPE                     "ltrim(rtrim(:RECORD_TYPE))",    
CUSTOMER_NAME                   "ltrim(rtrim(:CUSTOMER_NAME))",    
CUSTOMER_NUMBER                 "ltrim(rtrim(:CUSTOMER_NUMBER))",    
TAX_REGISTRATION_NUMBER         "ltrim(rtrim(:TAX_REGISTRATION_NUMBER))",    
TAX_PAYER_ID                    "ltrim(rtrim(:TAX_PAYER_ID))",    
COUNTRY                         "ltrim(rtrim(:COUNTRY))",    
ADDRESS_LINE1                   "ltrim(rtrim(:ADDRESS_LINE1))",    
ADDRESS_LINE2                   "ltrim(rtrim(:ADDRESS_LINE2))",    
ADDRESS_LINE3                   "ltrim(rtrim(:ADDRESS_LINE3))",    
TOWN_OR_CITY                    "ltrim(rtrim(:TOWN_OR_CITY))",    
COUNTY                          "ltrim(rtrim(:COUNTY))",    
POSTAL_CODE                     "ltrim(rtrim(:POSTAL_CODE))",    
CREATION_DATE                   SYSDATE
)
INTO TABLE XXTC_CUSTOMER_CONTACT
WHEN RECORD_TYPE = 'CONTACT_TELEPHONE'
FIELDS TERMINATED BY "," 
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS
(   
SEQ_NUM                         "ltrim(rtrim(:SEQ_NUM))",
RECORD_TYPE                     "ltrim(rtrim(:RECORD_TYPE))",
CUSTOMER_NUMBER                 "ltrim(rtrim(:CUSTOMER_NUMBER))",
TITLE                           "ltrim(rtrim(:TITLE))",
FIRST_NAME                      "ltrim(rtrim(:FIRST_NAME))",
LAST_NAME                       "ltrim(rtrim(:LAST_NAME))",
COUNTRY_CODE                    "ltrim(rtrim(:COUNTRY_CODE))",
AREA_CODE                       "ltrim(rtrim(:AREA_CODE))",
TELEPHONE_NUMBER                "ltrim(rtrim(:TELEPHONE_NUMBER))",
EXTENSION                       "ltrim(rtrim(:EXTENSION))",
TYPE                            "ltrim(rtrim(:TYPE))",
IS_PRIMARY_NUMBER               "ltrim(rtrim(:IS_PRIMARY_NUMBER))",
EMAIL_ADDRESS_OR_URL            "ltrim(rtrim(:EMAIL_ADDRESS_OR_URL))",
EMAIL_TYPE                      "ltrim(rtrim(:EMAIL_TYPE))",
IS_PRIMARY_EMAIL                "ltrim(rtrim(:IS_PRIMARY_EMAIL))",
CREATION_DATE                   SYSDATE
)

But when I tried to load the data using the above control file, the data is getting interfaced into
header table (XXTC_CUSTOMER_HEADER) only and all other records were getting discarded with failing WHEN clause.

Reason: Field scanning does not start over from the beginning of the record for a new INTO TABLE clause. Instead, scanning continues where it left off.

Solution: To resolve this problem we have to make the scanning start from beginning (using position(1) ). So updated control file should be:




OPTIONS (SKIP=3)
LOAD DATA TRUNCATE 
INTO TABLE XXTC_CUSTOMER_HEADER
WHEN RECORD_TYPE = 'HEADER' 
FIELDS TERMINATED BY ","  
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS

(   
SEQ_NUM                         POSITION(1) "ltrim(rtrim(:SEQ_NUM))",
RECORD_TYPE                     "ltrim(rtrim(:RECORD_TYPE))",    
CUSTOMER_NAME                   "ltrim(rtrim(:CUSTOMER_NAME))",    
CUSTOMER_NUMBER                 "ltrim(rtrim(:CUSTOMER_NUMBER))",    
TAX_REGISTRATION_NUMBER         "ltrim(rtrim(:TAX_REGISTRATION_NUMBER))",    
TAX_PAYER_ID                    "ltrim(rtrim(:TAX_PAYER_ID))",    
COUNTRY                         "ltrim(rtrim(:COUNTRY))",    
ADDRESS_LINE1                   "ltrim(rtrim(:ADDRESS_LINE1))",    
ADDRESS_LINE2                   "ltrim(rtrim(:ADDRESS_LINE2))",    
ADDRESS_LINE3                   "ltrim(rtrim(:ADDRESS_LINE3))",    
TOWN_OR_CITY                    "ltrim(rtrim(:TOWN_OR_CITY))",    
COUNTY                          "ltrim(rtrim(:COUNTY))",    
POSTAL_CODE                     "ltrim(rtrim(:POSTAL_CODE))",    
CREATION_DATE                   SYSDATE
)
INTO TABLE XXTC_CUSTOMER_CONTACT
WHEN RECORD_TYPE = 'CONTACT_TELEPHONE'
FIELDS TERMINATED BY "," 
OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS
(   
SEQ_NUM                         POSITION(1) "ltrim(rtrim(:SEQ_NUM))",
RECORD_TYPE                     "ltrim(rtrim(:RECORD_TYPE))",
CUSTOMER_NUMBER                 "ltrim(rtrim(:CUSTOMER_NUMBER))",
TITLE                           "ltrim(rtrim(:TITLE))",
FIRST_NAME                      "ltrim(rtrim(:FIRST_NAME))",
LAST_NAME                       "ltrim(rtrim(:LAST_NAME))",
COUNTRY_CODE                    "ltrim(rtrim(:COUNTRY_CODE))",
AREA_CODE                       "ltrim(rtrim(:AREA_CODE))",
TELEPHONE_NUMBER                "ltrim(rtrim(:TELEPHONE_NUMBER))",
EXTENSION                       "ltrim(rtrim(:EXTENSION))",
TYPE                            "ltrim(rtrim(:TYPE))",
IS_PRIMARY_NUMBER               "ltrim(rtrim(:IS_PRIMARY_NUMBER))",
EMAIL_ADDRESS_OR_URL            "ltrim(rtrim(:EMAIL_ADDRESS_OR_URL))",
EMAIL_TYPE                      "ltrim(rtrim(:EMAIL_TYPE))",
IS_PRIMARY_EMAIL                "ltrim(rtrim(:IS_PRIMARY_EMAIL))",
CREATION_DATE                   SYSDATE
)


Comments

Post a Comment