A SQL Structured Substitution API Reference

This appendix lists some of the commonly used structured substitution API calls and their details. For a full reference for the structured substitution API, see Java Substitution API Reference for Oracle Data Integrator.

Figure A-1 SQL Structured Substitution API object UML Diagram

Description of "Figure A-1 SQL Structured Substitution API object UML Diagram"

A simplified version of this drawing is as follows:

Figure A-2 Simplified Version of SQL Structured Substitution API object

Description of "Figure A-2 Simplified Version of SQL Structured Substitution API object"

Built-in variable names

The following built-in variables are used in the code examples:

• INSERT (SqlInsertStatement) — The top-level insert statement object produced by a target IKM.

• QUERY (SqlQuery) — The top-level source query used to extract the data to be loaded to the target or staging table.

• ATTR (MapAttribute)— The current source attribute that is in scope when processing the template (if any).

• physicalNode (MapPhysicalNode)— The physical mapping node that the KM is assigned to.

• component (IMapComponent) — The logical mapping component corresponding to the physical node that the KM is assigned to.

Commonly Used Method List

The commonly used method list includes the following:

• SqlInsertStatement.getColumnList()

• SqlInsertStatement.getQuery()

• SqlQuery.getSubqueries()

• SqlInsertStatement.getTargetTable()

• SqlQuery.getFromList()

• SqlQuery.getSelectList()

• FromClause.getJoinTable()

• FromClause.getSourceTables()

• FromClause.getTableQuery()

• ArrayExpression.getTemplate()

• ArrayExpression.getChildMap ()

A.1 SqlInsertStatement.getColumnList()

Usage

public List<Column>getColumnList()

Description

This method is used to get a list of the target column objects for the insert statement. Each column contains information about the name, datatype, and size of the target column. Also the query select item that is used to load this column can be determined.

Example 1

A test task is set up in an IKM that has IKM Oracle Insert as a base KM.

KM task-local groovy variable definition script:

def colList = INSERT.getColumnList()
colListStr = ''

for (Column col : colList) {
  if (colListStr.length() != 0) colListStr += ",\n"
  colListStr += col.getTable().getQualifier() + '.' + col.getTable().getName()
  colListStr += " /* Will be loaded from " + (col.getSourceSelectItem()  == null ? "unknown" : col.getSourceSelectItem()) + " */"
}

KM target command text:

Generated column list string = $[colListStr]

Generated Code:

Generated column list string =
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Stock_Name" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Stock_Symbol" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Stock_Type" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Industry_Type" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."SP_Rating" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Company_Name" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Registered_Address" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Registered_City" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Registered_State" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Postal_Code" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."Registered_County" */,
COMPKM_TEST_TGT_SCHEMA.Stock_Dim /* Will be loaded from STOCK_SRC."FaceValue" */

Example 2:

To set up for the example, a new Flex Field object is set up for “Attribute” objects in the Security navigator under the “Objects” accordion. The new Flex Field is called “ATTR_SUFFIX”. Also the source datastore model has a non-default value set for 3 of its attributes. The values are “_KEY1”, “_KEY2”, and “_KEY3”.

Also a new boolean KM option called “ADD_SUFFIXES” is added to test KM, and the value of the new option is set to true for the KM instance that is assigned to the target node in the test mapping. A test task is set up in an IKM that has IKM Oracle Insert as a base KM.

KM task-local groovy variable definition script:

def colList = INSERT.getColumnList()
colListStr = ''
addSuffixes = physicalNode.getKMOptionValueBoolean("ADD_SUFFIXES")
for (Column col : colList) {
  if (colListStr.length() != 0) colListStr += ",\n"
  attrs = col.getSourceSelectItem().getSourceAttributes()
  String suffix = "";
  if (addSuffixes && attrs != null && attrs.size() > 0) {
    suffix = attrs.get(0).getFlexFieldValue("ATTR_SUFFIX")
  }
  colListStr += col.getTable().getQualifier() + '.' + col.getTable().getName() + "." + '\"' + col.getUnquotedName() + suffix + '\"'
}

KM target command text:

Column List string with suffixes from source flex fields: $[colListStr]

Generated code:

Column List string with suffixes from source flex fields:
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Stock_Name",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Stock_Symbol_CUSTOM_KEY2",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Stock_Type",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Industry_Type_CUSTOM_KEY3",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."SP_Rating",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Company_Name_CUSTOM_KEY1",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Registered_Address",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Registered_City",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Registered_State",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Postal_Code",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."Registered_County",
COMPKM_TEST_TGT_SCHEMA.Stock_Dim."FaceValue"

A.2 SqlInsertStatement.getQuery()

Usage

public SqlQuery getQuery()

Description

This method is used to get the main source query object from the target IKM or LKM API object. The SqlQueryobject contains all metadata necessary to construct a SQL query. A query may be recursively defined, so that the FromClauseobject owned by the query object may contain other SqlQuery objects that represent subqueries of the top-level query.

Example

The return value of the getQuery() methodon the top level INSERTobject for a SQL Insert KM should be the same as the value of the built-in QUERYvariable. A simple test for that is to create a task like this:

KM task-local groovy variable definition script:

sourceQuery = INSERT.getQuery() queryEqualsString = (sourceQuery.equals(QUERY) ? "equal to" : "not equal to")

KM target command:

The return value from INSERT.getQuery() is $[queryEqualsString] to the built-in QUERY variable.

Generated Code:

-- The first subquery is:
SELECT 
  EMP.EMP_NO AS EMP_NO ,
  EMP.LAST_NAME AS LAST_NAME ,
  EMP.FIRST_NAME AS FIRST_NAME ,
  EMP.DEPT_ID AS DEPT_ID   
FROM /* Top-level from clause */  
  ODI_SRC.EMP@NEXTGEN_TEST_ORACLE_SRC EMP  
GROUP BY
  EMP.EMP_NO,EMP.LAST_NAME,EMP.FIRST_NAME,EMP.DEPT_ID 
-- The source mapping physical node for the subquery is JOIN1  

A.3 SqlQuery.getSubqueries ()

Usage

public List<SqlQuery>getSubqueries ()

Description

This method is used to get the set of subqueries that are defined by a query. This method returns only the first level subqueries. Each subquery returned may also contain other subqueries, that can be retrieved using the same method

Example

The example retrieves the subqueries if found, and displays the subquery text of the first subquery.

KM task-local groovy variable definition script:

subqueries = QUERY.getSubqueries()
subquery = null;
if (subqueries != null && subqueries.size() > 0) {
  subquery = QUERY.getSubqueries().get(0)
} else {
  odiRef.warn("No subquery found")
}
println("In task groovy, subquery is " + subquery)
subquerySourceNodeName = (subquery == null ? "unknown" : subquery.getMapPhysicalNode().getName())
println("In task groovy, subquerySourceNodeName is" + subquerySourceNodeName)

KM target command:

The first subquery is: {#IF ($[subquery] != null) #} $[subquery.getText()] {#ELSE#} not found {# ENDIF #}. The source mapping physical node for the subquery is $[subquerySourceNodeName]

Generated Code:

-- The first subquery is:
SELECT 
  EMP.EMP_NO AS EMP_NO ,
  EMP.LAST_NAME AS LAST_NAME ,
  EMP.FIRST_NAME AS FIRST_NAME ,
  EMP.DEPT_ID AS DEPT_ID   
FROM /* Top-level from clause */  
  ODI_SRC.EMP@NEXTGEN_TEST_ORACLE_SRC EMP  
GROUP BY
  EMP.EMP_NO,EMP.LAST_NAME,EMP.FIRST_NAME,EMP.DEPT_ID 
-- The source mapping physical node for the subquery is JOIN1  

A.4 SqlInsertStatement.getTargetTable ()

Usage

public Table getTargetTable() ()

Description

This method is used to get the a substitution API object that represents a target table. The return type is Table, which provides methods to get the name and characteristics of the target table.

Example

The example generates a SQL DDL CREATEstatement to create the table.

KM task-local groovy variable definition script:

table = INSERT.getTargetTable()
tableName = table.getCreationName()
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
}

KM target command:

create table $[odiRuntimeAccessName]
(
  $[tgtColList.call()]
)

Generated Code:

create table COMPKM_TEST_TGT_SCHEMA."Stock_Dim"
(
  "Stock_Key_PK" NUMBER(30),
"Split_Key" NUMBER(30),
"Stock_Name" VARCHAR2(50),
"Stock_Symbol" VARCHAR2(50),
"Stock_Type" VARCHAR2(50),
"Industry_Type" VARCHAR2(50),
"SP_Rating" VARCHAR2(50),
"Company_Name" VARCHAR2(50),
"Registered_Address" VARCHAR2(50),
"Registered_City" VARCHAR2(50),
"Registered_State" VARCHAR2(50),
"Postal_Code" VARCHAR2(50),
"Registered_County" VARCHAR2(50),
"FaceValue" NUMBER(30)
)

A.5 SqlQuery.getFromList ()

Usage

public List<FromClause> getFromList()

Description

This method is used to get a list of the FROMclause structures that represent the FROMclauses of the SQL query object. Each individual FROM clause represents some source table or inline view. There are basically 3 types of FROM clause:

• a simple table reference

• a subquery reference

• an ANSI JoinTable reference

If simple tables or subqueries are used without an ANSI join table, then the WHERE clause must provide the joining relationship between the tables. If the JoinTable is used, it will include an ON clause. The ON clause is accessed though a method JoinTable.getPredicate()or JoinTable.getPredicateText(). Both the subquery case and the JoinTable case can have nested queries, which represent regular FROM list of subqueries, or a ANSI joined subquery sources.

Example

The example gets the main FROM clause objects and derives a list of all the source tables using the FromClause.getSourceTables() call.

KM task-local groovy variable definition script:

sourceTables = QUERY.getFromList().get(0).getSourceTables()
sourceTableNames = ''
for (sourceTable in sourceTables) {
  if (sourceTableNames.length() > 0) sourceTableNames += ", ";
  sourceTableNames += sourceTable.getName();
}

KM target command:

The source tables are: $[sourceTableNames]

Generated Code

The source tables are: EMP, DEPT

A.6 SqlQuery.getSelectList ()

Usage

public List<ISelectItem> getSelectList()

Description

This method is used to get the list of select items present in the SQL query object. Each item is represented by an interface instance of IselectItem. The implementations of ISelectItem are ArrayExpression, StringExpression, and CorrelatedSubquery, The type can be determined using the java or groovy “instanceof” call. The ArrayExpression represents an expression owned by a mapping attribute, which is possibly a complex expression with referenced sub-expressions. The StringExpression represents a simple string expression. The CorrelatedQuery object represents a correlated query, also called as a scalar subquery, which is a subquery that returns a column and a row, that can be used as a query select item.

Example

The example prints out the type and text for each select item.

KM task-local groovy variable definition script:

selectList = QUERY.getSelectList()
selectListString = ''
index = 0;
for (item in selectList) {
  if (selectListString.length() > 0) selectListString += ",\n"
  selectListString += sprintf('item %1$d: type=%2$s text=%3$s', index++, item.getClass().getName(), item.toString()) 
}

KM target command:

Select list string is:{#NL#} $[selectListString]

Generated Code:

Select list string is:
item 0: type=oracle.odi.mapping.generation.ArrayExpression text=EMP_1.EMP_NO,
item 1: type=oracle.odi.mapping.generation.ArrayExpression text=EMP_1.LAST_NAME,
item 2: type=oracle.odi.mapping.generation.ArrayExpression text=EMP_1.FIRST_NAME,
item 3: type=oracle.odi.mapping.generation.ArrayExpression text=DEPT.DEPT_NAME 

A.7 FromClause.getJoinTable ()

Usage

public JoinTable getJoinTable ()

Description

This method is used to get the ANSI Join Table object from a FROM clause object, if the FROM clause object represents an ANSI join. The JoinTable object is a holder for the left and right join sources, which can be either simple tables or other join tables. It also holds the join type (inner, left outer, right outer, etc.), and the join condition expression.

Example

The example prints out the join type, right and left sources, and ON clause for the join table, if any.

KM task-local groovy variable definition script:

joinTable = QUERY.getFromList().get(0).getJoinTable()
joinTableStr = ''
if (joinTable != null) {
  joinTableStr = sprintf('type=%1$s\nleft text=%2$s\nright text=%3$s\njoin condition=%4$s', 
    joinTable.getJoinType(), joinTable.getLeftText(), joinTable.getRightText(), joinTable.getPredicateText()) 
} else {
  joinTableStr = 'No join table found'
}

KM target command:

Join table information: $[joinTableStr]

Generated Code:

Join table information:
type=INNER
left text=(
SELECT 
  EMP.EMP_NO AS EMP_NO ,
  EMP.LAST_NAME AS LAST_NAME ,
  EMP.FIRST_NAME AS FIRST_NAME ,
  EMP.DEPT_ID AS DEPT_ID   
FROM 
  ODI_SRC.EMP@NEXTGEN_TEST_ORACLE_SRC EMP  
GROUP BY
  EMP.EMP_NO,EMP.LAST_NAME,EMP.FIRST_NAME,EMP.DEPT_ID 
  ) EMP_1
right text=ODI_SRC.DEPT@NEXTGEN_TEST_ORACLE_SRC DEPT
join condition=EMP_1.DEPT_ID = DEPT.DEPT_ID 

A.8 FromClause.getSourceTables ()

Usage

public List<Table> getSourceTables ()

Description

This method is used to get a list of all the simple source tables that are included in the FROM clause object. It does not include Join tables or subquery references.

Example

The below example provides the table name and logical schema location for each simple source table in the mapping.

KM task-local groovy variable definition script:

fromClause = QUERY.getFromList().get(0)
sourceTables = fromClause.getSourceTables()
sourceTableList = '';
context = physicalNode.getContext();
for (sourceTable in sourceTables) {
  sourceTableLocation = sourceTable.getBoundDatastore().getModel().getObjectLocation(context);
  sourceTableList += sprintf('Table name=%1$s, logical schema=%2$s\n', sourceTable.getName(), sourceTableLocation.getLogicalSchema().getName())
}

KM target command:

Source table list: $[sourceTableList]

Generated Code:

Source table list:

Table name=EMP, logical schema=NEXTGEN_TEST_ORACLE_SRC
Table name=DEPT, logical schema=NEXTGEN_TEST_ORACLE_SRC 

A.9 FromClause.getTableQuery ()

This method FromClause.getTableQuery () includes the following methods:

• JoinTable.getLeftTableQueryRef ()

• FromClause.getRightTableQueryRef ()

Usage

public TableQueryReference getTableQuery ()
public TableQueryReference getLeftTableQueryRef ()
public TableQueryReference getRightTableQueryRef ()

Description

These methods are used to get the table query object for a FROM clause or from a JoinTable object that contains a table query. A table query is a holder for a subquery object or a simple table. Depending on the KM that is used, the TableQuery may be contained by the FROM clause itself, or on the right or left side of a JoinTable object.

Example

The example finds whether the first FROM clause has a JoinTable, and if so, gets the subquery from the left-hand side of the join table, if there is a subquery on the left side.

KM task-local groovy variable definition script:

fromClause = QUERY.getFromList().get(0)
fromClauseHasJoinTable = (fromClause.getJoinTable() == null ? "does not have" : "has") + " a join table"
tableQueryRef = QUERY.getFromList().get(0).getJoinTable().getLeftTableQueryRef()
query = null
subqueryText = null
if (tableQueryRef != null) {  // Not every FROM clause has a table query object, so protect against null.
  query = tableQueryRef.getQuery()
  if (query != null) {
    subqueryText = query.getText()
  }
}

KM target command:

From clause $[fromClauseHasJoinTable]
{#IF $[subqueryText] != null #}
subquery text:
$[subqueryText]
{# ELSE #}
No subquery text found.
{# ENDIF #}

Generated Code:

From clause has a join table subquery text:

SELECT 
  EMP.EMP_NO AS EMP_NO ,
  EMP.LAST_NAME AS LAST_NAME ,
  EMP.FIRST_NAME AS FIRST_NAME ,
  EMP.DEPT_ID AS DEPT_ID   
FROM 
  ODI_SRC.EMP@NEXTGEN_TEST_ORACLE_SRC EMP  
GROUP BY
  EMP.EMP_NO,EMP.LAST_NAME,EMP.FIRST_NAME,EMP.DEPT_ID 

A.10 ArrayExpression.getTemplate()

Usage

public String getTemplate ()

Description

This method gets the code generation template for an ArrayExpression object. An ArrayExpression is a special code generation expression object that can handle nested expressions. For example, if an ODI mapping has multiple expression components connected one after the other, and the expressions for each one reference the expression attributes of the previous expression component, then there are nested expressions in the final query. For example, suppose the first expression EXPR has an attribute EMPSAL whose expression is “EMP.SAL + 100”, and then the next expression component has an expression like “EXPR.EMPSAL – 50”. In that case the final expression used in the extract query would be “(EMP.SAL + 100) – 50”. The ArrayExpression object handles this by having a top-level template, which has the template references to child objects in the template text. The template references in the text looks like this: “@{R0}”. This refers to a child expression whose key in the child hash table is “R0”. Each child object could be a simple String expression, or another ArrayExpression for multiple levels of nesting, or a source attribute. The getTemplate() method returns the textual template used to produce the ArrayExpression text.

Example

The example loops through all the select list items in the source query, finds the ones that are implemented as ArrayExpression objects, and displays the full text and the template text.

KM task-local groovy variable definition script:

selectList = QUERY.getSelectList()
arrayExprListString = ''
for (selectItem in selectList) {
  ArrayExpression arrayExpr = selectItem.getArrayExpression()
  if (arrayExpr != null) {
    if (arrayExprListString.length() != 0) arrayExprListString += '\n';
    arrayExprListString += sprintf('text=%1$s, template=%2$s', arrayExpr.getText(), arrayExpr.getTemplate())
  }
}

KM target command:

Here is the list of ArrayExpression templates:$[arrayExprListString]

Generated Code:

Here is the list of ArrayExpression templates:

text=(EMP.EMP_NO + 100)*2, template=(@{R0} + 100)*2
text=EMP.LAST_NAME, template=@{R0}
text=EMP.FIRST_NAME, template=@{R0}
text=(EMP.FIRST_NAME || EMP.LAST_NAME), template=@{R0}
text=DEPT.DEPT_NAME, template=@{R0} 

A.11 ArrayExpression.getChildMap()

Usage

public Map<String,Object> getChildMap ()

Description

This method gets a hash map that contains the child objects that are owned by this ArrayExpression object. The hash key is the matching key used in the ArrayExpression template, as described in the previous method.

Example

The example loops through all the select list items in the source query, finds the ones that are implemented as ArrayExpression objects, and displays the full text and the template text.

KM task-local groovy variable definition script:

selectList = QUERY.getSelectList()
arrayExprListString = ''
for (selectItem in selectList) {
  ArrayExpression arrayExpr = selectItem.getArrayExpression()
  if (arrayExpr != null) {
    if (arrayExprListString.length() != 0) arrayExprListString += '\n';
    arrayExprListString += sprintf('ArrayExpression text=%1$s, template=%2$s', arrayExpr.getText(), arrayExpr.getTemplate())
    childMap = arrayExpr.getChildMap()
    for (childKey in childMap.keySet()) {
      arrayExprListString += sprintf('\n\tChild item: key=%1$s, object=%2$s', childKey, childMap.get(childKey).toString())
    }
  }
} 

KM target command:

Here is the ArrayExpression list with child objects:$[arrayExprListString]

Generated Code:

Here is the ArrayExpression list with child objects:

ArrayExpression text=(EMP.EMP_NO + 100)*2, template=(@{R0} + 100)*2
        Child item: key=R0, object=EMP.EMP_NO
ArrayExpression text=EMP.LAST_NAME, template=@{R0}
        Child item: key=R0, object=EMP.LAST_NAME
ArrayExpression text=EMP.FIRST_NAME, template=@{R0}
        Child item: key=R0, object=EMP.FIRST_NAME
ArrayExpression text=(EMP.FIRST_NAME || EMP.LAST_NAME), template=@{R0}
        Child item: key=R0, object=EMP.FIRST_NAME || EMP.LAST_NAME
ArrayExpression text=DEPT.DEPT_NAME, template=@{R0}
        Child item: key=R0, object=DEPT.DEPT_NAME