Multiple case statements in SQLLDR Control file

Question

I am using a control file to load data into Oracle 11g which is running on windows 7 machine. I have trouble loading the data with the following case statement and I tried different ways, couldn't succeed. Any suggestion? (In the log file the error is:

Record 1: Rejected - Error on table employee, column blah. ORA-00905: missing keyword

Record 2: Rejected - Error on table employee, column blah. ORA-00905: missing keyword, ..... and so on.

AND the Code is:

  Options (skip=1)

  load data
  APPEND INTO TABLE employee
  fields terminated by "\t"
  TRAILING NULLCOLS
  (
  a,
  b,
  c,
  d,
  blah "CASE WHEN :b='k' THEN 'LPAD(:blah,6,'0')'
             WHEN :b='s' AND :d='p' THEN 'LPAD(:blah,11,'0')'
             WHEN :b='s' AND :d='pr' THEN 'LPAD(:blah,8,'0')'
     END",
  g,
  h, 
  i
  )

score 0 · Answer 1 · answered Mar 26 '15 at 23:05

You have surrounded your LPAD calls with single quotes, which is why they don't work. Try to change the expression to something like

blah "CASE WHEN :b='k' THEN LPAD(:blah,6,'0') 
         WHEN :b='s' AND :d='p' THEN LPAD(:blah,11,'0')
         WHEN :b='s' AND :d='pr' THEN LPAD(:blah,8,'0')
     END",
...

Multiple case statements in SQLLDR Control file

1 Answers1