ODI Substitution methods using Working with Datasets

Working with Datasets

Oracle Data Integrator supports datasets. Each dataset represents a group of joined and filtered sources tables, with their mappings. Datasets are merged into the target datastore using set-based operators (UNION, INTERSECT, etc) at the integration phase.
During the loading phase, the LKM always works on one dataset. Duringthe integration phase, when all datasets need to merged, certain odiRef APIs that support working on a specific dataset are called using an index that identifies the dataset.
The following example explains how this dataset merging is done.

<%for (int i=0; i < odiRef.getDataSetCount(); i++){%>
<%=odiRef.getDataSet(i, "Operator")%>
select  <%=odiRef.getUserExit("OPTIMIZER_HINT")%>
        <%=odiRef.getPop("DISTINCT_ROWS")%>
        <%=odiRef.getColList(i,"", "[EXPRESSION]", ",\n\t", "", "(((INS or UPD) and !TRG) and REW)")%>,
<% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %>
        JRN_FLAG IND_UPDATE
<%} else {%>
        'I' IND_UPDATE
<%}%>
from     <%=odiRef.getFrom(i)%>
where   (1=1)
<%=odiRef.getJoin(i)%>
<%=odiRef.getFilter(i)%>
<%=odiRef.getJrnFilter(i)%>
<%=odiRef.getGrpBy(i)%>
<%=odiRef.getHaving(i)%>
<%}%>

A Java For loop iterates over the datasets. The number of datasets is retrieved using the getDataSetCount method. For each dataset, a SELECT statement is issued, each statement separated from the previous one by the dataset's set-based operator retrieved using the getDataSet method.