2 Introduction to the Substitution API

This chapter provides an introduction to the Oracle Data Integrator Substitution API using examples.

This chapter includes the following sections:

Section 2.1, "Introduction to the Substitution API"
Section 2.2, "Using Substitution Methods"
Section 2.3, "Using Substitution Methods in Actions"
Section 2.4, "Working with Object Names"
Section 2.5, "Working with Lists of Tables, Columns and Expressions"
Section 2.6, "Generating the Source Select Statement"
Section 2.7, "Working with Datasets"
Section 2.8, "Obtaining Other Information with the API"
Section 2.9, "Advanced Techniques for Code Generation"

Note:

The substitution API methods are listed in Appendix A, "Substitution API Reference".

2.1 Introduction to the Substitution API

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.

2.2 Using Substitution Methods

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.

2.2.1 Generic Syntax

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 syntax snpRef.<method_name> is still supported but deprecated.

2.2.2 Specific Syntax for CKM

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 Control tab of the Interface.
CKM_STATIC: Triggers a static control of the target datastore. Constraints defined for the datastore and selected as Static constraints will be checked.
DELETE_ERRORS: This option causes automatic suppression of the errors detected.

For example: the following call triggers a flow control with error deletion.

<% @ INCLUDE  CKM_FLOW DELETE_ERROR %>

2.2.3 Using Flexfields

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

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.

2.3 Using Substitution Methods in Actions

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

2.3.1 Action Lines Code

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") %>

2.3.2 Action Calls Method

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.

2.4 Working with Object Names

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 interface	getTable("L", "TARG_NAME", "A")	LKM, CKM, IKM, JKM
The integration (I$) table of the current interface.	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

2.5 Working with Lists of Tables, Columns and Expressions

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 column of the target datastore of an interface.

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: Columns in a CREATE TABLE statement Columns of the update key Expressions for a SELECT statement in a LKM, CKM or IKM Field definitions for a loading script 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 columns 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 an interface. Whenever possible, use the getFrom method instead. The getFrom method is discussed below.	LKM, IKM
getFilterList()	Returns the list of filter expressions in an interface. The getFilter method is usually more appropriate.	LKM, IKM
getJoinList()	Returns the list of join expressions in an interface. 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 an interface. 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 an interface. 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:

2.5.1 Using getTargetColList to create a table

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.

2.5.2 Using getColList in an Insert values statement

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.

2.5.3 Using getSrcTableList

The following example concatenates the list of the source tables of an interface for logging purposes.

For following KM code:

insert into MYLOGTABLE
(
INTERFACE_NAME,
DATE_LOADED,
SOURCE_TABLES
)
values
(
'<%=odiRef.getPop("POP_NAME")%>',
current_date,
'' <%=odiRef.getSrcTablesList("|| ", "'[RES_NAME]'", " || ',' || ", "")%>
)

Generates the following statement:

insert into MYLOGTABLE
(
INTERFACE_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 interface 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 ('').

2.6 Generating the Source Select Statement

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. When used in an LKM, it returns the FROM clause as it should be executed by the source server. When used in an IKM, it returns the FROM clause as it should be executed by the staging area server.	LKM, IKM
getFilter()	Returns filter expressions separated by an "AND" operator. When used in an LKM, it returns the filter clause as it should be executed by the source server. When used in an IKM, it returns the filter clause as it should be executed by the staging area server.	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).

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

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 interfaces and knowledge modules.

2.8 Obtaining Other Information with the API

The following methods provide additional information which may be useful:

Method	Description	Usable In
getPop()	Returns information about the current interface.	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 an interface	LKM, IKM, CKM
getModel()	Returns information about the current model during a reverse-engineering process.	RKM
getPop()	Returns information about the current interface.	LKM, IKM

2.9 Advanced Techniques for Code Generation

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 */