sql loader control file help..........

Hi,

I need help for sql ldr control file -

I need to load data from a file into a table, but need to add a constant to each row -

Here is my control file -

load data
infile '/dev/data_files/MERCH.txt'
truncate
into table tb_load
(DATA_RECORD POSITION(1:621) CHAR)

Above control file works and all data loaded as expected, now, I need to add a constant to each record while loading, so I tried to modify control file as below -

load data
infile '/vend_comp_dev/data_files/DRS_SEASNMERCH_2007031719.txt'
truncate
into table tb_vc_load
(DATA_RECORD CONSTANT '18' || POSITION(1:621) CHAR)

But, above control file gets a syntax error on ||
Can someone help?  How can I add a constant while adding the data file into my table, basically for every record I need constant 18 then data record.

Thanx
cm

LVL 2
chandukbAsked:
Who is Participating?
 
ishandoCommented:
You only use the CONSTANT when setting a field to a constant value, eg

   DATA_RECORD CONSTANT '18'

if you want to concatenate '18' to the start of the field you would do:
   
  DATA_RECORD POSITION(1:621) CHAR   "'18' || :DATA_RECORD"
or
  DATA_RECORD POSITION(1:621) CHAR   "concat('18',:DATA_RECORD)"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.