Trigger Infa workflow based on the status column in oracle table

Question

I want to implement the below scenario without using pl/sql procedure or unix script. I want to be implement by using Powercenter only.

Kindly suggest me.

1. I have a table called emp_details with columns (empno,ename,salary,emp_status,flag,date1) .
2. If someones updates the columns emp_status='abc' and flag='y' , Informatica WF 1 would be in continuous running status and checking emp_status value "ABC"
3. If it found record / records then query all the records and it will invoke WF 2.

4. WF 1 will pass value ename,salary,Date1 to WF 2 (Wf2 will populate will insert the records into the table emp_details2).

   Kindly tell me the informatica approach instead of plsql and shell script...

Comments

Check the following thread
https://community.informatica.com/message/120552

should be helpful

---

That thread was posted by me,... Kindly let me know if ther any other possible way .. i want to do this in only Informtica level not in unix or plsql

---

Wait if someone knows it, in general anonymous post attracts less views.

Answer 1

You can use a trigger in both oracle and SQL server to have something execute when a row changes.

For example, you could run a SQL agent job in SQL server: http://social.msdn.microsoft.com/Forums/en-US/b672ee45-7198-4783-8143-2908f8a954d2/how-to-execute-sql-server-agent-job-from-trigger

You can create a session prior to the sessions you want to run to check the row in Oracle. Create a 'dummy' session to do a select on the table looking for the 'Y (select 1 from table where value = 'Y'). Then write that to a dummy flat file. In the workflow use a decision task based on the # of target rows returned. The decision task will have a True (>=1 row returned) and connect that to the session you want to run or a the decision task will have a False (<1 row returned) connection to an email task that sends out a note to say that you can't run. The workflow will stop there and not run.

So you need a dummy session w/ a SQ Override to read the table where the flag resides with a dummy target. Use a decision task in the workflow to control the flow (i.e. go to the session to run or send an email/abort).

Comments

Hi Swetha, Thanks for you reply.. i will try it out... many thanks

---

You can write-down on Shweta's wall (http://tech.queryhome.com/wall/shweta.damsn ) as a token of appreciation :)

---

Hi Swetha, If i  create a dummy task.. it will pick the status=y and writes to dummy target. suppose if the table updated 5 rows at a time as flag=y, will the above solution works? kindly let me know

---

Hi Swetha, I have created a dummy session which reads status=y and writes to dummy flat file. Could you please let me know, whether i need to create any other session before the decision task?

---

Use decision task before that session and make to run that session. Or copy same session make duplicate sessions with new session names and run the workflows.

You can also see these links for help:

https://community.informatica.com/solutions/loop_through_a_workflow_with_dynamic_connections

https://community.informatica.com/solutions/solution_to_loop_through_a_workflow