Search code examples
oraclesql-loadercontrolfile

how to change the beginning position of the field in sql loader

I have a file which i want to load using sql loader.the sample file is as follows.

1|Deepak|1|raj|Kumar|mcapatna|powerhouse

the control file is 

LOAD DATA        
 INFILE *          
TRUNCATE 
INTO TABLE Student WHEN (1:1)= '1'
FIELDS TERMINATED BY '|' 
TRAILING NULLCOLS                           
(                 
    nickName1       position(6)  NULLIF  nickName1=BLANKS  ,
    nickName2       NULLIF  nickName2=BLANKS  ,
    class           CONSTANT '10',
    Address             CONSTANT 'NA'

)

The output what i want is nickName1=mcapatna nickName=powerhouse class=10 & Address=NA. I am getting the value for nickName1=pak & for nickName2= 1 That means it is counting based on sequence of single characters..

Solution

  • The control file is in data file field order. To skip the fields in the data file you do not want, you need to "consume" them by defining them as FILLER.

    ...
(
skip_1     FILLER,
skip_2     FILLER,
skip_3     FILLER,
skip_4     FILLER,
skip_5     FILLER,
nickname1,
nickname2,
class      CONSTANT '10',
Address    CONSTANT 'NA'
)

    See this fairly recent post for more info: Skipping data fields while loading delimited data using SQLLDR