Oracle table is not getting updated

verify · Post by **verify** » Fri Jan 16, 2009 5:12 am

I'm loading a table with source as sequential file using upsert method.
My job design is:

sequential file --> transformer ---> oracle table.
At source i have 3 columns say col1 int,col2 char,col3 varchar.
At target i have 4 columns say col1 int,col2 char,col3 varchar,col4 timestamp.

primary key: col1(int) and col2(char)

Using tranformer i'm assigning the value "current timestamp" to col4.

At target i used upsert method, when i have a new record it's inserting properly but when i have an update all the updated records are going to reject link and the sqlcode it's given as "1403"(update not found).

if i change the datatype of col2 which is one of my key columns as integer then the records are getting updated.

Did the composite primary key of "int" and "char " combination wouldn't update the records?

DS gurus please help me out...

Any help will be appreciated greatly..

ray.wurlod · Post by **ray.wurlod** » Fri Jan 16, 2009 5:36 am

Please post an exact error message. What are the data types of the columns in the target table - that is, the data types that are reported when you import the table definition? And what stage type are you using to effect the upsert?

verify · Post by **verify** » Fri Jan 16, 2009 7:01 am

I didn't get any errors job is finishing without any warnings.
But all the rows are passed to the reject link of oracle stage with sqlcode-1403.

The target table datatypes are:
COL1:decimal[38,10] not null
COL2:char(25) Unicode not null
COL3:varchar Unicode null
COL4:timestamp microseconds not null

Primary key: col1 and col2.

Please help me out..

vinothkumar · Post by **vinothkumar** » Fri Jan 16, 2009 8:15 am

In transformer try to give TRIM for all VARCHAR fields. Because even a extra space would prvent from update.

anujk_dev · Post by **anujk_dev** » Fri Jan 16, 2009 8:32 am

We have faced a similar sort of problem while updating records in the Teradata Database. The problem was with the CHAR datatype of the primary key. It worked once we changed the Data type to Varchar or change the value of the ENV variable APT_STRING_PADCHAR to ' ' from default of '0x00' (hexadecimal null).
Datastage appends the value of this env var into the CHAR Datatype fields. Due to some reasons the value inserted into the database was not treated as null but value '0x00'. So, we have value in the field as 'AAAAA', it was stored as 'AAAAA0x00' in the DB & it was visible as 'AAAAA' only.
you can try this option & let us know, if this works for you.