SQL Loader

Oracle Applications

-Simple and easy steps to oracle SQL LOADER

-A beginners guide for oracle  SQL LOADER

SQL loader introduction :-

wiki says - " SQL*Loader is a bulk loader utility used for moving (shifting) data from external files or flat files into the Oracle database. Its syntax is some what similar to that of the DB2 load utility . SQL*Loader supports various load formats(i.e. comma saprated,pipe saprated etc), selective loading, and multi-table loads." 

• how is become in simple and  easy steps

  so here is some sample of  SQL loaders .ctl file
  
  
  LOAD DATA <-----------"load data instruct computer to read data from flat file or .ctl file itself"
  INFILE *    <---------------"using * or one can give the path of file where the flat  file resides"
  APPEND INTO TABLE XX_TEMP_RCIA <----"An instruction to append i.e. to add in preexisted content"
  
  fields terminated by '|'  <--"this command specify from where the new coulum has started"
  TRAILING  NULLCOLS   <--"To trail the coulumns which had null coulumns"
    (
      ORIG_SYSTEM_CUSTOMER_REF          "TRIM(:ORIG_SYSTEM_CUSTOMER_REF)",
      CUSTOMER_NAME                                   "TRIM(:CUSTOMER_NAME)",
      ORIG_SYSTEM_ADDRESS_REF               "TRIM(:ORIG_SYSTEM_ADDRESS_REF)",
      ADDRESS1                                                   "TRIM(:ADDRESS1)",
      ADDRESS2                                                   "TRIM(:ADDRESS2)",
      ADDRESS3                                                   "TRIM(:ADDRESS3)",
      ADDRESS4                                                   "TRIM(:ADDRESS4)",
      CITY                                                           "TRIM(:CITY)",
      COUNTY                                                    "TRIM(:COUNTY)",
      STATE                                                         "TRIM(:STATE)",
      PROVINCE                                                 "TRIM(:PROVINCE)",
      COUNTRY                                                  "TRIM(:COUNTRY)",
      POSTAL_CODE                                          "TRIM(:POSTAL_CODE)",
      SITE_USE_CODE                                        "TRIM(:SITE_USE_CODE)",
      PRIMARY_SITE_USE_FLAG                     "TRIM(:PRIMARY_SITE_USE_FLAG)",
      CUSTOMER_STATUS                                "TRIM(:CUSTOMER_STATUS)",
      INSERT_UPDATE_FLAG                            "TRIM(:INSERT_UPDATE_FLAG)",
      LAST_UPDATED_BY                                 "TRIM(:LAST_UPDATED_BY)",
      LAST_UPDATE_DATE                                "TRIM(:LAST_UPDATE_DATE )",
      CREATED_BY                                              "TRIM(:CREATED_BY)",
      CREATION_DATE                                        "TRIM(:CREATION_DATE)",
      CUSTOMER_NUMBER                               "TRIM(:CUSTOMER_NUMBER)",
      CUSTOMER_NAME_PHONETIC               "TRIM(:CUSTOMER_NAME_PHONETIC)",
      CUSTOMER_CATEGORY_CODE               "TRIM(:CUSTOMER_CATEGORY_CODE)",
      LOCATION                                                    "TRIM(:LOCATION)",
      ORG_ID                                                          "TRIM(:ORG_ID)"
    )

Some points to avoid ERROR in SQL Loader :

1. The order of fields in SQL Loader must match with the order of fields in the data file.
2. If you are declaring any constant value to field, then it will be declared at the end and not at the start or in between fields otherwise the Loader will give Error. For example, in the above .ctl file, the constant will be declared after ORG_ID.
3. If there is not delimiter after last column, then the Loader will take last column value with Enter (ASCII value=13). So the value of the last column in table is not the same as the value in data file.