OCI Update - Failures not logged

[rameshrr3]

Im trying to update 7 million rows using an OCi stage. The job log says all rows were updated successfully , but I created another job and captured lookup failures from same table i was updating earlier - and i saw about 3000 rows failed the lookup - which means they did not exist in the table in the first place. However the fact that no warning/reject messages were logged when the update happened is giving us a lot of concerns about missing data that will not be tracked easily . Is it an expected behaviour of OCI stage or a bug?

Regards
Ramesh

[chulett]

That's standard Oracle behaviour. It will happily update or delete zero rows, it does not consider that any kind of a failure. That's why it is pretty standard to "pre-qualify" your updates by doing an existence check and only issuing the updates for existing rows.

Tell us, what exactly do you want to happen with the "failed" updates? Insert them? Log them as an issue?

[rameshrr3]

Ideally issue a warning message in the job log . I remember Oracle Enterprise throws something like "Unable To use a row for update" ( though this error message has a differnt context) . I cannot insert these rows yet since not all columns ( some containg valuable info) are present in the 'update' file ( corrected data)

[chulett]

As noted, there's no warning to log as nothing has failed. I'd suggest you look into a pre- or post-process to compare the keys in your source to the target and log anything that exists in the source but not the target. You could do all of that in your source SQL and then stream the problems to an audit file or aggregate and log a count... or whatever you deem to be appropriate.

[rameshrr3]

I do agree with the OCI stage. But this one has been biting me badly of late , so I used an ODBC stage attached to Oracle Wire protocol driver . Works Perfectly !
Update failures ( zero rows found) are captured with link variable REJECTCODE set to '17'

Performance statistics or monitor reports 'things' accurately

Sample log entry

Code: Select all

JCVM0536ORCLinqPolUPDnewFIELDS_ODBC..Transformer_1: Stage statistics 
20 rows read from DSLink30
0 rows written to L_TO_POL
20 rows written to DSLink47
0.160 CPU seconds used, 1.350 seconds elapsed.

The ODBC stage also gives a warning message to the job log like this

Code: Select all

DSD.BCIPut call to SQLExecute failed.
SQL statement:UPDATE WAHL.OITMT201_INFRCE_POL SET PRIM_EMPL_SVC_NUM = ?, PRIM_AGT_NAME = ?, POL_END_DT = ?, POS_POL_TYPE = ?, POS_POL_TYPE_DESC = ?, POL_REGISTER_DT = ? WHERE (CO_CD = ? AND POL_NUM = ?)
0 Rows affected.

CO_CD = 10
POL_NUM = SM3043477X
PRIM_EMPL_SVC_NUM = NULL
PRIM_AGT_NAME = NULL
POL_END_DT = NULL
POS_POL_TYPE = NULL
POS_POL_TYPE_DESC = NULL
POL_REGISTER_DT = NULL

[chulett]

I'm not sure I'd want each and every update that "failed" captured to the log like that, but if it works for you that's great. Yes, ODBC has different rules on things than native Oracle would, even if using an Oracle driver. Makes it behave more like other databases, from what I've seen.