Oracle Enterprise stage, UPSERT works, TRUNCATE aborts!

vinaymanchinila · Post by **vinaymanchinila** » Fri Sep 09, 2005 10:23 am

Hi,
I have a file which I load into a table. If I use UPSERT option in the Oracle enterprise stage, the job finishes fine, but If I use

WRITE METHOD=LOAD & WRITE MODE= TRUNCATE, the job aborts with the following error!

The system(sqlldr bdr@cooradevl31 CONTROL=ora.3331.899749.0.ctl LOG=ora.3331.899749.0.log BAD=ora.3331.899749.0.log.bad SILENT=header PARFILE=ora.3331.899749.0.par) failed; see the log file for the Oracle specific message.

Any help is greatly appreciated
Thanks,

ArndW · Post by **ArndW** » Fri Sep 09, 2005 10:31 am

Vinay,

have you done what the error message has told you to do?

Go to your DataStage temp directory, start with node1 and look into the directory /default/Scratch and then view the file ora.3331.899749.0.log

sometimes the first /tmp node doesn't have the error, in which case I would look at the log file in more detail or just use the "find" unix command to locate the log file.

vinaymanchinila · Post by **vinaymanchinila** » Fri Sep 09, 2005 10:36 am

Hi Arnd,

It is complaining about inserting a blank into a field , but it is really not null .

Record 805: Rejected - Error on table SAP_ZAVL_PROJ, column ZAVL_PROJ_DESIGN_ID.ORA-01400: cannot insert NULL into (ZAVL_PROJ_DESIGN_ID)

ArndW · Post by **ArndW** » Fri Sep 09, 2005 10:46 am

Vinay,

the column is probably an Oracle VarChar2() datatype ... and by definition the loader trims these and makes it a null field.

vinaymanchinila · Post by **vinaymanchinila** » Fri Sep 09, 2005 10:47 am

Hi ,
The job works in 2 scenarios,

1)When I took the source column out and had a constant.

2) When I changed the constant to ' ' it works but I do not understand why it doesnt recognize the ' ' comming in !

Thanks

chulett · Post by **chulett** » Fri Sep 09, 2005 10:50 am

ArndW wrote:the column is probably an Oracle VarChar2() datatype ... and by definition the loader trims these and makes it a null field.

There is a sql loader option to PRESERVE BLANKS and I would think there would be a way to enable this for a load.

vinaymanchinila · Post by **vinaymanchinila** » Fri Sep 09, 2005 10:53 am

Hi Arnd,

It is "NOT NULL CHAR(4)".

I tried to use

if IsNull(Src.Col2) then ' ' else Src.Col2

and it still aborts
Record 805: Rejected - Error on table SAP_ZAVL_PROJ, column ZAVL_PROJ_DESIGN_ID. ORA-01400: cannot insert NULL into (ZAVL_PROJ_DESIGN_ID)

is your name Arnold ?

vinaymanchinila · Post by **vinaymanchinila** » Fri Sep 09, 2005 10:58 am

Searched for it and I used the APT_ORACLE_PRESERVE_BLANKS, and set it to true.

The job finishes now!

Thanks a bunch guys.

ArndW · Post by **ArndW** » Fri Sep 09, 2005 11:08 am

The APT_ORACLE_PRESERVE_BLANKS will work as long as the column isn't VarChar2; I assumed that it was set so was looking for the more obscure error.

Good job - glad to see it is working.

vinaymanchinila · Post by **vinaymanchinila** » Fri Sep 09, 2005 11:12 am

It i sChar(4), so how do we handle this if it is varchar ?

ArndW · Post by **ArndW** » Fri Sep 09, 2005 11:21 am

There isn't a workaround. If you have a NOT NULLable VarChar2 column and the data only has spaces in it then the bulk load method won't work.

vinaymanchinila · Post by **vinaymanchinila** » Fri Sep 09, 2005 11:33 am

But I dont understand why the following statement doesnt work:

if IsNull(Src.Col1) then ' ' else Src.Col1

' '=Blank

Thanks,