This chapter provides an introduction to the Oracle Data Integrator Substitution API using examples.
This chapter includes the following sections:
Note:
The substitution API methods are listed in Appendix A, "Substitution API Reference".
KMs are written as templates by using the Oracle Data Integrator substitution API. The API methods are java methods that return a string value. They all belong to a single object instance named "odiRef". The same method may return different values depending on the type of KM that invokes it. That's why they are classified by type of KM.
To understand how this API works, the following example illustrates how you would write a create table statement in a KM and what it would generate depending on the datastores it would deal with:
The following code is entered in a KM:
CREATE TABLE <%=odiRef.getTable("L", "INT_NAME", "A")%>(<%=odiRef.getColList("", "\t[COL_NAME] [DEST_CRE_DT]", ",\n", "", "")%>)
The generated code for the PRODUCT table is:
CREATE TABLE db_staging.I$_PRODUCT(
P RODUCT_ID numeric(10), PRODUCT_NAME varchar(250), FAMILY_ID numeric(4), SKU varchar(13), LAST_DATE timestamp)
The generated code for the CUSTOMER table is:
CREATE TABLE db_staging.I$_CUSTOMER( CUST_ID numeric(10), CUST_NAME varchar(250), ADDRESS varchar(250), CITY varchar(50), ZIP_CODE varchar(12), COUNTRY_ID varchar(3))
As you can see, once executed with appropriate metadata, the KM has generated a different code for the product and customer tables.
The following topics cover some of the main substitution APIs and their use within KMs. Note that for better readability the tags "<%" and "%>" as well as the "odiRef" object reference are omitted in the examples.
The methods that are accessible from the Knowledge Modules and from the procedures are direct calls to Oracle Data Integrator methods implemented in Java. These methods are usually used to generate some text that corresponds to the metadata stored into the Oracle Data Integrator repository.
The substitution methods are used in any text of a task of a Knowledge Module or of a procedure.
They can be used within any text using the following syntax
<%=java_expression%>
In this syntax:
The <%= %> tags are used to output the text returned by java_expression. This syntax is very close to the syntax used in Java Server Pages (JSP).
Java expression is any Java expression that returns a string.
The following syntax performs a call to the getTable method of the odiRef java object using three parameters. This method call returns a string. That is written after the CREATE TABLE text.
CREATE TABLE <%=odiRef.getTable("L", "INT_NAME", "A")%>
The Oracle Data Integrator Substitution API is implemented in the Java class OdiReference, whose instance OdiRef is available at any time. For example, to call a method called getFrom(), you have to write odiRef.getFrom().
Note:
For backward compatibility, the "odiRef" API can also be referred to as "snpRef" API. "snpRef" and "odiRef" object instances are synonyms, and the legacy syntax snpRef.<method_name> is still supported but deprecated.
The following syntax is used in an IKM to call the execution of a check procedure (CKM).
This syntax automatically includes all the CKM procedure commands at this point of in the processing.
<% @ INCLUDE (CKM_FLOW | CKM_STATIC) [DELETE_ERROR] %>
The options for this syntax are:
CKM_FLOW: triggers a flow control, according to the CKM choices made in the Check Knowledge Module tab in the properties of the target datastore in the physical diagram of the Mapping.
CKM_STATIC: Triggers a static control of the target datastore. Constraints defined for the datastore and selected as Static constraints will be checked.
DELETE_ERROR: This option instructs the CKM to remove any detected errors from the validated table (flow table in case of CKM_FLOW or target table in case of CKM_STATIC). More precisely, all CKM commands will be generated, which are tagged as Remove Errors.
For example: the following call triggers a flow control with error deletion.
<% @ INCLUDE CKM_FLOW DELETE_ERROR %>
Flexfields are user-defined fields enabling to customize the properties of Oracle Data Integrator' objects. Flexfields are defined on the Flexfield tab of the object window and can be set for each object instance through the Flexfield tab of the object window.
When accessing an object properties through Oracle Data Integrator' substitution methods, if you specify the flexfield Code, Oracle Data Integrator will substitute the Code by the flexfield value for the object instance.
For instance:
<%=odiRef.getTable("L", "MY_DATASTORE_FIELD", "W")%> will return the value of the flexfield MY_DATASTORE_FIELD for the current table.
<%=odiRef.getSrcTableList("", "[MY_DATASTORE_FIELD] ", ", ", "")%> will return the flexfield value for each of the source tables of the mapping.
It is also possible to get the value of a flexfield through the getFlexFieldValue() method.
Note:
Flexfields exist only for certain object types. Objects that do not have a Flexfield tab do not support flexfields.
The following code generation tags may be used:
<%... %>: This tag is evaluated during scenario generation or session creation time. It is used to substitute the metadata information in the text. For example, for the PRODUCT table:
CREATE TABLE <%=odiRef.getTable("L", "INT_NAME", "A")%>
is converted into:
CREATE TABLE db_staging.I$_PRODUCT
<?...?>: This tag is evaluated during session execution time where the physical topology information is substituted depending upon the execution context. The session task logs are created after evaluating this tag. For example, for the target data server user 'TargetName':
"<?=snpRef.getInfo("DEST_USER_NAME")?>"
is converted into:
'TargetName'
<@...@>: This tag is evaluated after session logs are created and should be used for substitutions that you do not want to appear in the Session Task logs. It can be used for substituting clear text passwords which must not be logged in the session task logs for security reasons. For example:
<@=snpRef.getInfo("SRC_PASS") @>
An action corresponds to a DDL operation (create table, drop reference, etc) used to generate a procedure to implement in a database the changes performed in a data integrator model (Generate DDL operation). Each action contains several Action Lines, corresponding to the commands required to perform the DDL operation (for example, dropping a table requires dropping all its constraints first).
Action lines contain statements valid for the technology of the action group. Unlike procedures or knowledge module commands, these statements use a single connection (SELECT ... INSERT statements are not possible). In the style of the knowledge modules, action make use of the substitution methods to make their DDL code generic.
For example, an action line may contain the following code to drop a check constraint on a table:
ALTER TABLE <%=odiRef.getTable("L", "TARG_NAME", "A") %>
DROP CONSTRAINT <%=odiRef.getCK("COND_NAME") %>
The Action Calls methods are usable in the action lines only. Unlike other substitution methods, they are not used to generate text, but to generate actions appropriate for the context.
For example, to perform the a Drop Table DDL operation, we must first drop all foreign keys referring to the table.
In the Drop Table action, the first action line will use the dropReferringFKs() action call method to automatically generate a Drop Foreign Key action for each foreign key of the current table. This call is performed by creating an action line with the following code:
<% odiRef.dropReferringFKs(); %>
The syntax for calling the action call methods is:
<% odiRef.method_name(); %>
Note:
The action call methods must be alone in an action line, should be called without a preceding "=" sign, and require a trailing semi-colon.
The following Action Call Methods are available for Actions:
addAKs(): Call the Add Alternate Key action for all alternate keys of the current table.
dropAKs(): Call the Drop Alternate Key action for all alternate keys of the current table.
addPK(): Call the Add Primary Key for the primary key of the current table.
dropPK(): Call the Drop Primary Key for the primary key of the current table.
createTable(): Call the Create Table action for the current table.
dropTable(): Call the Drop Table action for the current table.
addFKs(): Call the Add Foreign Key action for all the foreign keys of the current table.
dropFKs(): Call the Drop Foreign Key action for all the foreign keys of the current table.
enableFKs(): Call the Enable Foreign Key action for all the foreign keys of the current table.
disableFKs(): Call the Disable Foreign Key action for all the foreign keys of the current table.
addReferringFKs(): Call the Add Foreign Key action for all the foreign keys pointing to the current table.
dropReferringFKs(): Call the Drop Foreign Key action for all the foreign keys pointing to the current table.
enableReferringFKs(): Call the Enable Foreign Key action for all the foreign keys pointing to the current table.
disableReferringFKs(): Call the Disable Foreign Key action for all the foreign keys pointing to the current table.
addChecks(): Call the Add Check Constraint action for all check constraints of the current table.
dropChecks(): Call the Drop Check Constraint action for all check constraints of the current table.
addIndexes(): Call the Add Index action for all the indexes of the current table.
dropIndexes(): Call the Drop Index action for all the indexes of the current table.
modifyTableComment(): Call the Modify Table Comment for the current table.
AddColumnsComment(): Call the Modify Column Comment for all the columns of the current table.
When working in Designer, you should avoid specifying physical information such as the database name or schema name as they may change depending on the execution context. The correct physical information will be provided by Oracle Data Integrator at execution time.
The substitution API has methods that calculate the fully qualified name of an object or datastore taking into account the context at runtime. These methods are listed in the table below:
| Qualified Name Required | Method | Usable In |
|---|---|---|
|
Any object named OBJ_NAME |
getObjectName("L", "OBJ_NAME", "D") |
Anywhere |
|
The target datastore of the current mapping. |
getTable("L", "TARG_NAME", "A") |
LKM, CKM, IKM, JKM |
|
The integration (I$) table of the current mapping. |
getTable("L", "INT_NAME", "A") |
LKM, IKM |
|
The loading table (C$) for the current loading phase. |
getTable("L", "COLL_NAME", "A") |
LKM |
|
The error table (E$) for the datastore being checked. |
getTable("L", "ERR_NAME", "A") |
LKM, CKM, IKM |
|
The datastore being checked |
getTable("L", "CT_NAME", "A") |
CKM |
|
The datastore referenced by a foreign key |
getTable("L", "FK_PK_TABLE_NAME", "A") |
CKM |
Generating code from a list of items often requires a "while" or "for" loop. Oracle Data Integrator addresses this issue by providing powerful methods that help you generate code based on lists. These methods act as "iterators" to which you provide a substitution mask or pattern and a separator and they return a single string with all patterns resolved separated by the separator.
All of them return a string and accept at least these 4 parameters:
Start: a string used to start the resulting string.
Pattern: a substitution mask with attributes that will be bound to the values of each item of the list.
Separator: a string used to separate each substituted pattern from the following one.
End: a string appended to the end of the resulting string
Some of them accept an additional parameter (the Selector) that acts as a filter to retrieve only part of the items of the list. For example, list only the mapped attribute of the target datastore of a mapping.
Some of these methods are summarized in the table below:
| Method | Description | Usable In |
|---|---|---|
|
getColList() |
The most frequently-used method in Oracle Data Integrator. It returns a list of columns and expressions that need to be executed in the context where used. You can use it, for example, to generate lists like these:
This method accepts a "selector" as a 5th parameter to let you filter items as desired. |
LKM, CKM, IKM, JKM, SKM |
|
getTargetColList() |
Returns the list of attributes in the target datastore. This method accepts a selector as a 5th parameter to let you filter items as desired. |
LKM, CKM, IKM, JKM,SKM |
|
getAKColList() |
Returns the list of columns defined for an alternate key. |
CKM, SKM |
|
getPKColList() |
Returns the list of columns in a primary key. You can alternatively use getColList with the selector parameter set to "PK" . |
CKM,SKM |
|
getFKColList() |
Returns the list of referencing columns and referenced columns of the current foreign key. |
CKM,SKM |
|
getSrcTablesList() |
Returns the list of source tables of a mapping. Whenever possible, use the getFrom method instead. The getFrom method is discussed below. |
LKM, IKM |
|
getFilterList() |
Returns the list of filter expressions in a mapping. The getFilter method is usually more appropriate. |
LKM, IKM |
|
getJoinList() |
Returns the list of join expressions in a mapping. The getJoin method is usually more appropriate. |
LKM, IKM |
|
getGrpByList() |
Returns the list of expressions that should appear in the group by clause when aggregate functions are detected in the mappings of a mapping. The getGrpBy method is usually more appropriate. |
LKM, IKM |
|
getHavingList() |
Returns the list of expressions that should appear in the having clause when aggregate functions are detected in the filters of a mapping. The getHaving method is usually more appropriate. |
LKM, IKM |
|
getSubscriberList() |
Returns a list of subscribers. |
JKM |
The following section provide examples illustrating how these methods work for generating code:
The following example shows how to use a column list to create a table.
The following KM code:
Create table MYTABLE
<%=odiRef.getTargetColList("(\n", "\t[COL_NAME] [DEST_WRI_DT]", ",\n", "\n)")%>
Generates the following statement:
Create table MYTABLE
(
CUST_ID numeric(10),
CUST_NAME varchar(250),
ADDRESS varchar(250),
CITY varchar(50),
ZIP_CODE varchar(12),
COUNTRY_ID varchar(3)
)
In this example:
Start is set to "(\n": The generated code will start with a parenthesis followed by a carriage return (\n).
Pattern is set to "\t[COL_NAME] [DEST_WRI_DT]": The generated code will loop over every target column and generate a tab character (\t) followed by the column name ([COL_NAME]), a white space and the destination writable data type ([DEST_WRI_DT]).
The Separator is set to ",\n": Each generated pattern will be separated from the next one with a comma (,) and a carriage return (\n)
End is set to "\n)": The generated code will end with a carriage return (\n) followed by a parenthesis.
The following example shows how to use column listing to insert values into a table.
For following KM code:
insert into MYTABLE
(
<%=odiRef.getColList("", "[COL_NAME]", ", ", "\n", "INS AND NOT TARG")%>
<%=odiRef.getColList(",", "[COL_NAME]", ", ", "", "INS AND TARG")%>
)
Values
(
<%=odiRef.getColList("", ":[COL_NAME]", ", ", "\n", "INS AND NOT TARG")%>
<%=odiRef.getColList(", ", "[EXPRESSION]", ", ", "", "INS AND TARG")%>
)
Generates the following statement:
insert into MYTABLE ( CUST_ID, CUST_NAME, ADDRESS, CITY, COUNTRY_ID , ZIP_CODE, LAST_UPDATE ) Values ( :CUST_ID, :CUST_NAME, :ADDRESS, :CITY, :COUNTRY_ID , 'ZZ2345', current_timestamp )
In this example, the values that need to be inserted into MYTABLE are either bind variables with the same name as the target columns or constant expressions if they are executed on the target. To obtain these 2 distinct set of items, the list is split using the Selector parameter:
"INS AND NOT TARG": first, generate a comma-separated list of columns ([COL_NAME]) mapped to bind variables in the "value" part of the statement (:[COL_NAME]). Filter them to get only the ones that are flagged to be part of the INSERT statement and that are not executed on the target.
"INS AND TARG": then generate a comma separated list of columns ([COL_NAME]) corresponding to expression ([EXPRESSION]) that are flagged to be part of the INSERT statement and that are executed on the target. The list should start with a comma if any items are found.
The following example concatenates the list of the source tables of a mapping for logging purposes.
For following KM code:
insert into MYLOGTABLE
(
MAPPING_NAME,
DATE_LOADED,
SOURCE_TABLES
)
values
(
'<%=odiRef.getPop("POP_NAME")%>',
current_date,
'' <%=odiRef.getSrcTablesList("|| ", "'[RES_NAME]'", " || ',' || ", "")%>
)
Generates the following statement:
insert into MYLOGTABLE ( MAPPING_NAME, DATE_LOADED, SOURCE_TABLES ) values ( 'Int. CUSTOMER', current_date, '' || 'SRC_CUST' || ',' || 'AGE_RANGE_FILE' || ',' || 'C$0_CUSTOMER' )
In this example, getSrcTableList generates a message containing the list of resource names used as sources in the mapping to append to MYLOGTABLE. The separator used is composed of a concatenation operator (||) followed by a comma enclosed by quotes (',') followed by the same operator again. When the table list is empty, the SOURCE_TABLES column of MYLOGTABLE will be mapped to an empty string ('').
LKMs and IKMs both manipulate a source result set. For the LKM, this result set represents the pre-transformed records according to the mappings, filters and joins that need to be executed on the source. For the IKM, however, the result set represents the transformed records matching the mappings, filters and joins executed on the staging area.
To build these result sets, you will usually use a SELECT statement in your KMs. Oracle Data Integrator has some advanced substitution methods, including getColList, that help you generate this code:
| Method | Description | Usable In |
|---|---|---|
|
getFrom() |
Returns the FROM clause of a SELECT statement with the appropriate source tables, left, right and full outer joins. This method uses information from the topology to determine the SQL capabilities of the source or target technology. The FROM clause is built accordingly with the appropriate keywords (INNER, LEFT etc.) and parentheses when supported by the technology.
|
LKM, IKM |
|
getFilter() |
Returns filter expressions separated by an "AND" operator.
|
LKM, IKM |
|
getJrnFilter() |
Returns the special journal filter expressions for the journalized source datastore. This method should be used with the CDC framework. |
LKM, IKM |
|
getGrpBy() |
Returns the GROUP BY clause when aggregation functions are detected in the mappings. The GROUP BY clause includes all mapping expressions referencing columns that do not contain aggregation functions. The list of aggregation functions are defined by the language of the technology in the topology. |
LKM, IKM |
|
getHaving() |
Returns the HAVING clause when aggregation functions are detected in filters. The having clause includes all filters expressions containing aggregation functions. The list of aggregation functions are defined by the language of the technology in the topology. |
LKM, IKM |
To obtain the result set from any SQL RDBMS source server, you would use the following SELECT statement in your LKM:
select <%=odiRef.getPop("DISTINCT_ROWS")%>
<%=odiRef.getColList("", "[EXPRESSION]\t[ALIAS_SEP] [CX_COL_NAME]", ",\n\t", "", "")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getFilter()%>
<%=odiRef.getJrnFilter()%>
<%=odiRef.getJoin()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>
To obtain the result set from any SQL RDBMS staging area server to build your final flow data, you would use the following SELECT statement in your IKM. Note that the getColList is filtered to retrieve only expressions that are not executed on the target and that are mapped to writable columns.
select <%=odiRef.getPop("DISTINCT_ROWS")%>
<%=odiRef.getColList("", "[EXPRESSION]", ",\n\t", "", "(not TRG) and REW")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getJrnFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>
As all filters and joins start with an AND, the WHERE clause of the SELECT statement starts with a condition that is always true (1=1).
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. During the 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.
The select statement is build as in Generating the Source Select Statement, except that each method call is parameterized with i, the index of the dataset being processed. For example, getFrom(i) generates the from statement for the dataset identified by the value of i.
All the methods that support a parameter for the dataset index also support a syntax without this index value. Outside an IKM, then should be used without the dataset index. Within an IKM, if used without the dataset index, these method address the first dataset. This syntax is backward compatible with previous Oracle Data Integrator mappings and knowledge modules.
The following methods provide additional information which may be useful:
| Method | Description | Usable In |
|---|---|---|
|
getPop() |
Returns information about the current mapping. |
IKM, LKM, CKM, JKM |
|
getInfo() |
Returns information about the source or target server. |
Any procedure or KM |
|
getSession() |
Returns information about the current running session |
Any procedure or KM |
|
getOption() |
Returns the value of a particular option |
Any procedure or KM |
|
getFlexFieldValue() |
Returns information about a flex field value. Not that with the "List" methods, flex field values can be specified as part of the pattern parameter. |
Any procedure or KM |
|
getJrnInfo() |
Returns information about the CDC framework |
JKM, LKM, IKM |
|
getTargetTable() |
Returns information about the target table of a mapping. |
LKM, IKM, CKM |
|
getModel() |
Returns information about the current model during a reverse-engineering process. |
RKM |
|
getPop() |
Returns information about the current mapping. |
LKM, IKM |
You can use conditional branching and advanced programming techniques to generate code. The code generation in Oracle Data Integrator is able to interpret any Java code enclosed between "<%" and "%>" tags.
The following examples illustrate how you can use these advanced techniques.
Using Java Variables and String Functions
The following KM Code creates a string variable and uses it in a substitution method call :
<% String myTableName; myTableName = "ABCDEF"; %> drop table <%=odiRef.getObjectName(myTableName.toLowerCase())%>
Generates the following:
drop table SCOTT.abcdef
Using a KM Option to Generate Code Conditionally
The following KM code generates code depending on the OPT001 option value.
<%
String myOptionValue=odiRef.getOption("OPT001");
if (myOption.equals("TRUE"))
{
out.print("/* Option OPT001 is set to TRUE */");
}
else
{
%>
/* The OPT001 option is not properly set */
<%
}
%>
If OPT001 is set to TRUE, then the following is generated:
/* Option OPT001 is set to TRUE */
Otherwise the following is generated
/* The OPT001 option is not set to TRUE */