Not sure if anyone can help, but just in case somebody has had a similar experience.
My issue is that it appears that the update statement when loading into an Oracle db using OCIORA8 stage does not seem to be working. The perplexing thing is that I have other jobs which do exactly the same thing and they are working fine.
The design of the job is
Code: Select all
---> OracleDB_insert
/
Seq ---> Tfr <
| \
| ---> OracleDB_update
Hash
The update SQL is as follows:
Code: Select all
UPDATE ODS_CONTROLLED_OBJECT_RESTRICT
SET RESTRICTION_EXPIRY_DATE=TO_DATE(:4, 'YYYY-MM-DD HH24:MI:SS'),
RESTRICTION_REASON_CODE=:5,
ODS_JOB_ID=:6,
ODS_UPDATE_DATE_ID=:7,
ODS_UPDATE_TIME_ID=:8
WHERE CONTROLLED_OBJECT_ID=:1
AND RESTRICTION_TYPE_CODE=:2
AND RESTRICTION_EFFECTIVE_DATE=TO_DATE(:3, 'YYYY-MM-DD HH24:MI:SS')
Other information about the settings in the ORAOCI8 stage
- insert:
Array size = 100
Transaction size = 0
Update action = Insert rows without clearing
Transaction Isolation = Read Committed
SQL = generated
- Update:
Array size = 1
Transaction size = 0
Update action = Update exising rows only
Transaction Isolation = Read Committed
SQL = generated
Does anybody out there have any ideas that I may have missed?
Thanks in advance.