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

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

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

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.

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.

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.

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

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.

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.