How to create Control file for Sql loader in Oracle

Hi,

Once the table definition is created, you are ready to create the control file

Here is a sample code...


LOAD DATA
        INFILE 'Locations.csv'       /* Locations.csv is the actual data file*/
INSERT
        INTO TABLE DEV_COST_ALLOC                   /* DEV_COST_ALLOC is the dummy table to be used for data uploading*/
        REPLACE FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
  LINE_ID               ,
  EFFECTIVE_DATE        ,
  EMP_NUM               ,
  EMP_NAME              ,
  LOCATION              ,
  CC_DESCRIPTION        ,
  PROPORTION            ,
  ERROR_DESCRIPTION     ,
  PROCESS_FLAG          ,
  COST_ALLOCATION_ID    ,
  COMBINATION_NAME      ,
  EFFECTIVE_START_DATE  ,
  EFFECTIVE_END_DATE    ,
  OBJ_VER_NUMBER      
)

Number of columns specified with the braces depends on your approach. Normally table definition is replicated as it is, in the control file, without mentioning the data types of the table columns.

Go to File-> Save as

Select "Save as type" as All Files and give a proper name e.g. Abc.ctl

Leave a comment for any query.