Problem with Oracle OCI Stage

mcchan · Post by **mcchan** » Mon Jul 30, 2007 10:23 pm

Hi,

I have a simple sever job, it contains a transformer stage which writes data into sequential file stage and follow by Oracle OCI stage to load data into Oracle database. This sever job is called a few times within a job sequence so the it shares the same filename for the sequential file and sometimes the file will be empty. For some reason, Oracle OCI stage seems to try to load the file which was output from the previous sever job. Any idea?

Thanks.

ArndW · Post by **ArndW** » Mon Jul 30, 2007 10:26 pm

Have you declared this as a multi-instance job and are calling it concurrently with different invocation IDs in the sequence? If yes, you need to ensure that the sequential file is different for each instance - hint - use the invocation id as part of the filename.

mcchan · Post by **mcchan** » Mon Jul 30, 2007 10:42 pm

ArndW wrote:Have you declared this as a multi-instance job and are calling it concurrently with different invocation IDs in the sequence? If yes, you need to ensure that the sequential file is different for each ...

They are running in sequence and not concurrently.

Thanks.

ArndW · Post by **ArndW** » Mon Jul 30, 2007 10:54 pm

Ahh, I think I understand now. Does that mean if a run produces no output then it uses the previous run's file again for the Oracle load? Can you check to ensure you do have the "overwrite" setting on in your output file? Also, what version and platform are you on?

mcchan · Post by **mcchan** » Mon Jul 30, 2007 11:15 pm

ArndW wrote:Ahh, I think I understand now. Does that mean if a run produces no output then it uses the previous run's file again for the Oracle load? Can you check to ensure you do have the "overwrite" setting on in your output file? Also, what version and platform are you on?

Not quite. Assuming the first run produces an emply file and the next run that I expect there is at least one row or more in the output file but It looks like the Oracle OCI still load nothing to the database. There is no problem with write permission on the file as I can re-run the second run again and the expected data load into database. I have tried to check the time within director and the creation time for the file and they match.

Thanks.

ArndW · Post by **ArndW** » Mon Jul 30, 2007 11:34 pm

Try adding a parameter with a unique number passed to it from the sequence and use that as part of the sequential filename. Then check the files for each run to see if they match what you expect; this might help narrow down the issue.