how to add certain columns while loading data

 how to add certain columns while loading data 

    In this post, we are learning about how to add certain data column while uploading data in database using sql loader. In oracle from version Oracle 9i one can specify BOUNDFILLER columns. BOUNDFILLER can be used if the skipped column's value will be required later again.

 If we need to skip column or field from loading file or data and used will be required later again. In below data we skip skip 'fName' and 'lName' field and used later to assign value to fullName field or column. Look at below examples:

 employee.txt (Data File)  

 ID,fNAME,lNAME,DEPARTMENT,SALARY
 10,Akash,Patil,HomeLoanSales,15000
 20,Kufu,Yadav,Life Insurance,11000
 30,Krishna,Gole,Sales,17000
 40,Amol,Bhagat,Marketing,19500
 50,Satya,Koli,Antivirus,16000

 example1.ctl (Control File)  

 OPTIONS (SKIP=1)
 LOAD DATA
 INFILE 'd:\dataupload\employee.txt'
 INSERT into table employee
 field terminated by "," optionally enclosed by " " 
 ( 
   id,
   fNAME BOUNDFILLER,
   lNAME BOUNDFILLER,
   dept FILLER,
   salary,
  fullName "fNAME||' '||lNAME"
 ) 

Note:- BOUNDFILLER field value must be used after loading data column. Becoz if we used in middle of data then our loading data column sequence will be distrub.