3 Introduction to OdiRef Substitution API

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

This chapter includes the following sections:

• Introduction to the Substitution API

• Using Substitution Methods

• Using Substitution Methods in Actions

• Working with Object Names

• Working with Lists of Tables, Columns and Expressions

• Generating the Source Select Statement

• Obtaining Other Information with the API

• Advanced Techniques for Code Generation

Note:

The substitution API methods are listed in Substitution API Reference.

3.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, or an object value that can be converted to a string or be passed as a parameter into a different method. Since ODI version 12.2.1.2.1, there are 2 types of substitution API objects.

• The first type is called the Structured Substitution API. It is a structured set of objects that represent the structure of the code that will be generated. The Structured Substitution API objects are a collection of objects generally related by a tree-like structure. Navigation methods are provided to navigate from parent objects to child objects. The Structured Substitution API is available only when writing commands or global templates for Component KMs, which consist of IKM, LKM, XKM, and CKM.

• The second type of AST object is the monolithic substitution API, or odiRef, which is the only substitution API object that was available prior to ODI version 12.2.1.2.1.

Structured Substitution API

The Structured Substitution API tree has a different structure depending on the base language of the code that will be generated by the KM. The currently supported base languages are SQL, SPARK_PYTHON.

The tree structure for SQL is shown in the following diagram:

Figure 3-1 Structured Substitution API

Description of "Figure 3-1 Structured Substitution API"

The same method may return different values depending on the structure of the mapping logic that was used to generate the tree. The tree is generated by iterating through all the source components for a particular target in a mapping, and executing the KM delegate for each component KM.

The following example illustrates how you would write a create table statement in a KM using the Structured Substitution API.

The following code is entered in a KM or global template:

CREATE TABLE $[tableName] ({#LIST} $[tgtColNameList] $[tgtColDataTypeList] {#SEP#},{#NL#} {#ENDLIST#})

The generated code for the PRODUCT table is:

CREATE TABLE db_staging.PRODUCT( PRODUCT_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.CUSTOMER( CUST_ID numeric(10), CUST_NAME varchar(250), ADDRESS varchar(250), CITY varchar(50), ZIP_CODE varchar(12), COUNTRY_ID varchar(3))

Each element of the template is explained below:

• CREATE TABLE is just simple text that is replicated into the generated code.

• $[tableName] is a substitution API call using the groovy-based templating syntax (since 12.2.1.2.1). The name inside the $[…] escape characters can be a simple variable name of a built-in or groovy variable, or it can include a method call, for example “table.getName()”, if the “table” variable was an object with a getName() method. The tableName variable may be defined in the variable definition groovy script associated with the KM task.

• {#LIST#} is an example of the template flow control command syntax (since ODI 12.2.1.2.1). These commands are always escaped by {#...#} and can be used to control the flow of the text generation. In this case it allows a list variable to be displayed by calling “toString()” for each object in the list. For the {#LIST#} command, if a different method should be called for each list item, the “foreach()” syntax can be used, for example “columnList.foreach(getText())”. The list substitution API objects following {#LIST#} will be expanded one at a time, using the fixed text for each one.

• {#SEP#} This is another template flow control commands that is always paired with {#LIST#}. The text following this command is used as a separator between each list item.

• {#ENDLIST#} This command closes the list flow control definition.

The Monolithic Substitution API Object - odiRef

The monolithic substitution API methods 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.

Calls to odiRef substitution API methods can also be made in the groovy variable definition script for a KM task. Thus a new variable can be defined whose value is equal to the result of a odiRef call, or some combination of calls.

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

3.2.1 Generic Syntax

The substitution methods are used in any text of a task of a Knowledge Module or of a procedure.

The odiRef methods can be called inside a groovy variable definition script. Also they can used within any KM task command 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 Monolithic 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.

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

3.2.3 Using Flexfields

Flexfields are user-defined fields enabling users 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.

Flexfield Access Using Groovy-based Variables and Mapping API

Using the new structured substitution API, the flex field values for a datastore can be obtained by starting with the built-in component KM variable “physicalNode”, and using the ODI public API to navigate to the value. This could be done in the groovy variable definition script, using groovy language (which can be written to look exactly like java) similar to this:

flexFieldValue = “”;
String ffName = “MY_FLEX_FIELD”;
IMapComponent comp = node.getLogicalComponent();
if (comp.isOfType(DatastoreComponent.COMPONENT_TYPE_NAME)) {
  DatastoreComponent datastoreComp = (DatastoreComponent) comp.getDelegate();
  OdiDataStore datastore = (OdiDataStore) datastoreComp.getBoundDataStore();
  Collection<IFlexFieldValue> ffValues = datastore.getFlexFieldsValues();
for (IFlexFieldValue value : ffValues) {
if (value.getName().equals(ffName)) {
   // found it
   flexFieldValue = value.getValue().toString();
    }
   

If this code is written in the groovy variable definition script, then variable “flexFieldValue” can be used in a variable reference in the template or KM command.

This code is more complex than the simple odiRef getTable or getSrcTableList call (as illustrated below). However it is also more flexible to accommodate special cases, such as retreiving all of the flex fields from a given object into an array, or retrieving flex fields from other objects besides the contextual target or source tables.

Also, the odiRef call can be used in the KM task groovy variable definition script to store the value in a temporary variable, which can then be used in the template command.

Flexfield Access Using Monolithic odiRef Substitution API

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.

3.2.4 Using Code Generation Tags

The following code generation tags may be used as a way to write executable java inside the template:

• <%... %>: 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
  

  Usage of the <%...%> tag is discouraged for new Component KMs (IKM/LKM/XKM/CKM), the newer Structured Substitution API and the groovy variable definition script should be used instead.

• <?...?>: 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") @> 
  

3.3 Using Substitution Methods in Actions

An action corresponds to a DDL operation (create table, drop reference, etc) used to generate a session or scenario task 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).

3.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 $[tableName]
DROP CONSTRAINT $[constraintName]

3.3.2 Action Calls Method

The Action Calls methods are odiRef calls that 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.

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

The odiRef name calculation methods can be called from the groovy variable definition script, or inside a java tags like <%...%>, inside the command text.

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

Additionally, a special static object name retrieval method is provided for the case where mapping public API is used in the groovy script, and a map physical node is available. This may be useful if a generated object name is needed for an object other than the current target.

Table 3-1 Static Object Name Retrieval Method

Qualified Name Required	Method	Usable In
Any object named OBJ_NAME	OdiRef.getOdiGeneratedAccessName("OBJ_NAME", mapPhysicalNode, "D")	Anywhere

3.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 a set of template flow control commands that act as iterators to which you provide a set of list variables in a template pattern, plus a separator pattern.

The main template flow control command for looping is {# LIST #}. The template patterns found between the {#LIST#} and {#ENDLIST#} commands are expanded multiple times, one for each item in the list variables that are expanded in the template pattern. There must be at least one list variable that is expanded in the template pattern inside the list construct.

So for example if a list variable was defined in groovy like this:

List myList = [‘a’. ‘b’, ‘c’]

Then add a #LIST command like this:

{#LIST#} The element value is $[myList] {#SEP#} {#NL#} {#ENDLIST#}

The resultant generated code would look like this:

The element value is a
The element value is b
The element value is c

The template pattern between {#SEP#} and {#ENDLIST#} is used as a separator between each list item expansion.

Multiple list variables can be used in the pattern, but each one must have the same number of elements.

To access mapping objects and their generated strings, the structured substitution API or mapping API may be used in the groovy variable definition script. For example the following query could be used to get the generated source column list for the built-in QUERY structured substitution API variable:

{# LIST #} $[QUERY.getSelectList().foreach(getText())] $[QUERY.getColumnAliasSeparator()] $[QUERY.getAliasList()] {# SEP #},{#NL#}{# ENDLIST #}

The older odiRef iterator methods are also still supported, and can be used inside the Java based <%...%> tags, or in the KM task groovy variable definition script. 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: 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 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:

3.5.1 Using INSERT.getTargetColList to create a table

The following example shows how to use the built-in variable INSERT, of type SqlInsertStatement, to create a table.

The following KM code:

create table $[odiRuntimeAccessName]
(
  $[tgtColList.call()]
)
Combined with the following groovy variable definitions in the KM task variable definition script:
tableName = physicalNode.getBoundObjectName();
tableCmp = physicalNode.getLogicalComponent();
tableQualifier = physicalNode.getLocation() == null ? null : physicalNode.getLocation().getName();
odiRuntimeAccessName = OdiRef.getOdiGeneratedAccessName("TARG_NAME", physicalNode, "A");
tableAlias = component.getAlias();
tgtColList = { 
  def cols = component.getAttributes();
  String result = ""
  def first = true
  for (col in cols) {
    if (!first) result += ",\n"
    result += col.getSQLAccessName(false, "") + " " + MappingUtils.getDdlDataType(col.getBoundObject());
    first = false
  }
  return result
}

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

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

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

3.5.4 Using getSrcTableList

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

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

3.7 Obtaining Other Information with the API

The following methods provide additional information which may be useful:

Method	Description	Usable In
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

3.8 Advanced Techniques for Code Generation

The code generation in Oracle Data Integrator is able to interpret any Java code enclosed between "<%" and "%>" tags.

However, the best practice for new KMs developed in ODI version 12.2.1.2.1 or beyond is to keep Java or groovy code separate from the template, by adding it to the KM task groovy variable definition script and defining a set of simple variables that can be used in the template command. For example, if some section of the template command is to be conditionally generated depending on some complex condition, the best practice would be to define a boolean variable in the groovy variable script, and then use that variable in the {#IF#} statement in the template command. In this way, the template and the code are kept separate and both are clearer.

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