top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Trigger Infa workflow based on the status column in oracle table

+1 vote
1,217 views

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...

posted Nov 7, 2014 by anonymous

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button
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.

1 Answer

+2 votes

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).

answer Nov 7, 2014 by Shweta Singh
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
Similar Questions
0 votes

Hi All,

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

Kinly suggest me.

1.I have a table called emp_details with coulmns (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 contunous 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.

  1. 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...

Thank you

+1 vote

I am using below statement but i got error.
ALTER TABLE XXXtable
ADD xxx_name varchar2(50) not null;

error message:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

+1 vote

In my source table data is

s_name,p_name,value
s1 ,   p1,     10
s1 ,   p2,     xyz
s1 ,   p3 ,    abc
s2 ,   p1 ,    20
s2 ,   p2 ,    xyz
s2 ,   p3 ,    abc

I want two target tables, first table is based on s_name s1, second table based on s_name s2. Both table contains contains p_name and value.

The target table data like as

Table s1

p_name,value 
p1,    10
p2,    xyz
p3,    abc 

Table s2

p_name,value 
p1,    20
p2,    xyz
p3,    abc 
+1 vote

I have a Time column in my SQL Server table which has data like (14:00:00.0000000).

I am trying to load this type of data in Oracle table which has corresponding column datatype as timestamp. I am trying to convert the time column in SQL server to timestamp column in oracle using Informatica.

Whats the best way to do it?

0 votes

Oracle:Can database trigger written on synonym of a table and if it can be then what would be the effect if original table were accessed?

...