oracle-databasecsvetlctl

Oracle data not loading after POSITION(10)


i have been trying to load some data from a csv file based on certain conditions and i am restricted by the use of "OR" control in when clause (Oracle ctl) the data loads for the first 11 conditions but does not loads any data after the first 11 condition

following is the code sample

     create table t1 (
     c1 varchar(10),
     c2 varchar(10),
     c3 varchar(10)
     )

----------dataloader-----------

     options(errors=20000)
     LOAD DATA
     INFILE 'D:\Scripts\abc.csv'              
     BADFILE 'D:\Scripts\abc.BAD'
     DISCARDFILE 'D:\Scripts\abc.DIS'
     DISCARDMAX 20000
     insert
     INTO TABLE t1

     when c1='James'

     FIELDS TERMINATED BY "," optionally enclosed by '"'

     Trailing NULLCOLS
     (             
     c1,

     c2,

     c3
     )

     INTO TABLE t1

     when c1='John'

     FIELDS TERMINATED BY "," optionally enclosed by '"'

     Trailing NULLCOLS
     (             
     c1 POSITION(1),

     c2,

     c3
     )

     INTO TABLE t1

     when c1='Sam'

     FIELDS TERMINATED BY "," optionally enclosed by '"'

     Trailing NULLCOLS
     (             
     c1 POSITION(2),

     c2,

     c3
     )
     .
     .
     .         
     INTO TABLE t1

     when c1='Mark'

     FIELDS TERMINATED BY "," optionally enclosed by '"'
     Trailing NULLCOLS
     (             
     c1 POSITION(12),

     c2,

     c3

     )

--------------dataloader execution-----

     sqlldr scott/tiger direct=true control='filepath'

Solution

  • for executing each and every condition we need to place POSITION(1) till POSITION(9) tag for each and every condition. otherwise the ctl file doesnot reads the tagging and ignores the condition