DSXchange

Hi ppl,

I am trying to use ORABULK stage in datastage 5.2

when i try to load my data which is of this format

Colombian|101|7.99|175|175
French_Roast|49|8.99|150|150
Espresso|150|9.99|60|60
Colombian_Decaf|101|8.99|155|155
French_Roast_Decaf|49|9.99|90|90

the ORABULK stage creates a data file and control file as follows..

data file -

Colombian|101|7.99|175|175
French_Roast|49|8.99|150|150
Espresso|150|9.99|60|60
Colombian_Decaf|101|8.99|155|155
French_Roast_Decaf|49|9.99|90|90


control file -

-- Oracle SQL*LOAD control file generated on 19:03:47 09 JAN 2003 by DataStage(tm) ORABULK stage version 1.2 NLS map=MS1252
LOAD DATA
TRUNCATE
INTO TABLE coffees
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
("COF_NAME" VARCHAR,
"SUP_ID" INTEGER EXTERNAL,
"PRICE" DECIMAL EXTERNAL,
"SALES" INTEGER EXTERNAL,
"TOTAL" INTEGER EXTERNAL)

when i use the above generated files for loading into Oracle using the SQLLDR utility it simply puts all the column values in the first column so my table in oracle luks like this

COF_NAME SUP_ID PRICE SALES TOTAL
-------------------------------- ---------- ---------- ---------- ----------
lombian|101|7.99|175|175
ench_Roast|49|8.99|150|150
presso|150|9.99|60|60
lombian_Decaf|101|8.99|155|155
ench_Roast_Decaf|49|9.99|90|90


all under COF_NAME column..but if i remove the datatype from my ctl file and make it like this..

-- Oracle SQL*LOAD control file generated on 19:03:47 09 JAN 2003 by DataStage(tm) ORABULK stage version 1.2 NLS map=MS1252
LOAD DATA
TRUNCATE
INTO TABLE coffees
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
("COF_NAME" ,
"SUP_ID" ,
"PRICE" ,
"SALES" ,
"TOTAL" )

it works fine...

can u help me in this regard , bcoz i cannot go and remove the datatype all the time when my job runs

please do help me

Naga.

Programmer Analyst,
Cognizant,
Chennai.

What version of Oracle are you running?

Oracle 9i is my database 9.2

Programmer Analyst,
Cognizant,
Chennai.