How to modify data while data uploading to database

 How to modify data while data uploading to database 

    In this post, we are learning about How to modify data while data uploading to database using sql loader. We can also populate columns with static or derived values. However, this only applies for the conventional load path (and not for direct path loads).

Here are some examples:

 example1.ctl (Control File) 

LOAD DATA
  INFILE *
  INTO TABLE modified_data
  (  
     rec_no                       "my_db_seq.nextval",
     region                       CONSTANT '31',
     time_loaded                  "to_char(SYSDATE, 'HH24:MI')",
     data1        POSITION(1:5)   ":data1/100",
     data2        POSITION(6:15)  "upper(:data2)",
     data3        POSITION(16:22) "to_date(:data3, 'YYMMDD')"
  )
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112

 example2.ctl (Control File)  

 OPTIONS (SKIP=1)
 LOAD DATA
 INFILE 'd:\dataupload\employee.txt'
 INSERT into table employee
 field terminated by "," optionally enclosed by " " 
 ( 
   id "my_db_seq.nextval" ,
   name ,
   dept "decode(:dept, "M", 'Marketing', 'Production')",
   salary
)