Please shed some light on the runaway process I am encountering. The job I am designing is trying to accomplish the following:
The job has to insert a new record in the table if no record exists with the given key (collectionName). If the record exists, then it just has to update a single column in the existing record.
The schema of the table ('collectionInfo') has five columns and they are:
collectionName(PK), collectionType, FilePath, CreateTime, UpdateTime
The table is residing in Oracle 10g database. I designed the job as following
Code: Select all
ORAOCI9 ---->(Ref. stream) Tx -----> ORAOCI9 (Update record Stage)
|
|
ORAOCI9 (Insert record stage)
Code: Select all
SELECT COLLECTIONTYPE,COLLECTIONNAME,FILEPATH,TO_CHAR(CREATETIME, 'YYYY-MM-DD HH24:MI:SS'),TO_CHAR(UPDATETIME, 'YYYY-MM-DD HH24:MI:SS') FROM COLLECTIONINFO WHERE COLLECTIONNAME=:2
I set the Run Limit to '1' and still the job doesn't terminate after one record update.
I modified the above job to include a seqfile with one dummy record as input stage and was successful i.e the job stops after one record update
The 'insert new or update existing' option is not used as the sql statement is different in the two scenarios. Also I am forced to use the Input ORAOCI9 as Ref. stream as the job doesn't run ('insert' case) when there are no existing records with the 'collectionName'.. ie 0 rows on input link.
Please advice on why the job with 'Ref. Stream' doesn't terminate and what is a better way to design the job for this requirement?
This job is part of a sequence operation, that's why it is not done in PL/SQL
Regards,
--Raghu