Starting a job on the arrival of a record in an Oracle table

[iansta]

Guys,

I need to extract some records from a source system. I need to do this once the invoice run has completed. The way that I can identify the invoice run has completed is when a record gets written into the audit log on the database.

The way that I'm considering do is to create a batch that runs a job multiple times that reads the table and if the record exists this will write a file into a directory and exits if successfull otherwise keeps looping.

Another sequence will have a waitforfile stage that picks up the trigger file and runs the jobs.

Can you think of a slicker way of doing the same thing?

Thanks

[chulett]

Whatever way you can make work and are able to support is slick enough. I've done something very much like that, then used DSGetLinkInfo to see if the fishing trip was successful or not. Seems like WaitForFile could be used just as easily if the presence of the file is the trigger.

However, keep in mind the fact that a Server job writing to a file will always create the file when the job starts. Which is why I checked to see if the file was populated rather than simply existed.

[ray.wurlod]

If you can convince "them" to allow it to be an Oracle external table, then you could probably use a Wait For File activity in a job sequence.

[baglasumit21]

Guys,

I need to extract some records from a source system. I need to do this once the invoice run has completed. The way that I can identify the invoice run has completed is when a record gets written into the audit log on the database.

The way that I'm considering do is to create a batch that runs a job multiple times that reads the table and if the record exists this will write a file into a directory and exits if successfull otherwise keeps looping.

Another sequence will have a waitforfile stage that picks up the trigger file and runs the jobs.

Can you think of a slicker way of doing the same thing?

Thanks

I have worked on a similar requirement. My requirement was to run my jobs when a flag is set to 'Y' in the control table.

I have created a function in Oracle which queries the table to check the flag. This function keeps on quering the table for a given time period say 3 hours(parameterised). This function I have called through the sql query of a database stage in once of the job which is my first job in the sequence. Once it finds the flag as Y it will set the userstatus as 'Y' for the job in the transformer.

Now in the second job of my sequence job i am checking the user status for previous job activyty and running my job based on it. If the flag is found to be N then the sequence job aborts after the given time period.