ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Processing multiple Interface through single Package

The Steps are as follows

1. Get the File list into the  ODI Procedure ( ‘ GENERATING LIST OF SCENARIO ’)
2. Assign the Loop to Zero ( LOOP=0)
3. Get the Next Scenario of the Interface  to be processed
4. Process the Scenarios using  ‘ ODISTARTSCENARIO ‘  by passing the value from #GET_SCENARIO  variable
5.  Refresh the Variable ‘ Check ‘
6. Check if there are still  scenarios of the Interfaces  to be processed or not. If so Next step , else Exit .
7. Increment the Loop by 1.

The Package Flow is shows below in this Image.
image

ODI Procedure to get list of scenarios.

image

DROP TABLE <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.SCENARIOS

CREATE TABLE <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.SCENARIOS
(S_NO NUMBER(3),SCEN_NAME VARCHAR2(50))

INSERT ALL
INTO  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.SCENARIOS  VALUES (1,’AGE_GROUP’)
INTO  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.SCENARIOS  VALUES (2,’FILE_FLOW’)
INTO  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.SCENARIOS  VALUES (3,’FILE_LIST’)
SELECT * FROM DUAL

Variable  #GET_SCENARIO

image

For Scenario Name i have used the  #GET_SCENARIO  so i get the value of the next scenario to be processed

GET _SCENARIO VARIABLE QUERY

Here iam getting the values of the scen_name from the temp table created using ODI_procedure as mentioned above.

SELECT SCEN_NAME FROM <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.SCENARIOS
WHERE S_NO=#LOOP

CHECK VARIABLE QUERY

Here iam checking at every step if there is any other Scenario left out to be processed or not.

SELECT COUNT(*)-#LOOP  FROM  <%=odiRef.getInfo(“DEST_WORK_SCHEMA”)%>.SCENARIOS

[ Note – Please see my post – Multiple File – single Interface on variable usage and options ,and my Post Generating Multiple Scenarios on how to generate multiple scenarios for Interfaces ]

2 Comments

  1. Hi,
    I want to get the session number of the scenario invoked using odistartscenario . is there any way to get the session number returned?

Leave a Reply

Required fields are marked *.