Failed to update

[vikind]

Hi,

I am trying to lookup on the target based on DATA_EXCPT_CD and want insert new records and update the DATA_EXCPT_DESC or DATA_EXCPT_SVRY_CD based for existing records.

When i run through the debugger my mapping updates existing rows
but after i run the workflow it

ERROR: Target table [EXCPT_CD_STG] does not allow UPDATE
Row # [0] in bad file

WRITER_1_*_1> CMN_1053 : Rowdata: ( RowType=1(update) Src Rowid=1 Targ Rowid=1
DATA_EXCPT_CD (DATA_EXCPT_CD:Char.3: "1"
DATA_EXCPT_DESC (DATA_EXCPT_DESC:Char.100: "Patient"
DATA_EXCPT_PRO (DATA_EXCPT_PRO:Char.1: "1"
DATA_EXCPT_SVRY_CD (DATA_EXCPT_SVRY_CD:Char.3: "1"
REC_CRTN_DT_TM (REC_CRTN_DT_TMate: "09/07/2004 04:47:57"
LOAD_DT_TM (LOAD_DT_TMate: "10/11/2004 19:38:57"
DATA_DT_TM (DATA_DT_TMate: "10/11/2004 19:38:57"
)


I selected the source as data driven and for target I tried to select different options Update else insert or update as update.
but every time it rejects the records with existing DATA_EXCPT_CD.

the target database is in oracle.

would really appreciate your help!!

thanks

 

[Dagon]

Do you have a primary key defined on the target table ? If you don't have a primary key defined, Informatica won't be able to update it because it doesn't know which fields to use in the "where" clause of the update statement it generates.

 

[vikind]

the pk is DATA_EXCPT_CD. and i am using router followed by update strategy transformations but still no luck

 

[Dagon]

Check you have privileges to update the table. Log on as the user who runs the Informatica jobs using SQL*Plus and run a test update against the table.

In the logfile for the job, there will be a section where it shows the format of update/insert/delete statements to be used against each target. It will look something like:

WRITER_1_1_1> WRT_8124 Target Table XXXXXX :SQL UPDATE statement:
UPDATE XXXXXXX SET ....

Have a look at this and see if it seems reasonable.

 

[vln]

I think the basic problem will be the primary key only that is available in the target. The same issue I have faced. The updation will happen if the source table & target table keys matches.

Regards
vln

 

[Dagon]

Can I clarify that, when you say there is a primary key defined on the target, you mean this is defined to Informatica, not just defined on the database.

 

[vln]

Yes, I mean in the source & target transformation.

Thanks
VLN