Fusion Middleware Documentation
Advanced Search


Knowledge Module Developer's Guide for Oracle Data Integrator
Close Window

Table of Contents

Show All | Collapse

A Substitution API Reference

This appendix provides a list of the Oracle Data Integrator odiRef API.

See Chapter 2, "Introduction to the Substitution API" for introductory information about using this API.

This appendix includes the following sections:

A.1 Substitution Methods List

The substitution are listed below depending on the type of knowledge module into which they can be used. The "Global Methods" list lists the methods that can be used in any situation.

Refer to the description of a given method itself for more information about its behavior in a given knowledge module or action.

This section contains the following topics:

A.1.2 Journalizing Knowledge Modules

In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Journalizing Knowledge Modules (JKM):

A.1.4 Check Knowledge Modules

In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Check Knowledge Modules (CKM):

A.1.6 Reverse-Engineering Knowledge Modules

In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Reverse-engineering Knowledge Modules (RKM):

A.1.7 Service Knowledge Modules

In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Service Knowledge Modules (SKM):

A.2 Substitution Methods Reference

This section provides an alphabetical list of the substitution methods. Each method is detailed with usage, description, parameters and example code.

A.2.1 getAK() Method

Use to return information about an alternate key.

Usage

public java.lang.String getAK(java.lang.String pPropertyName)

Description

This method returns information relative to the alternate key of a datastore during a check procedure. It is only accessible from a Check Knowledge Module if the current task is tagged "alternate key".

In an action, this method returns information related to the alternate key currently handled by the DDL command.

Parameters

Parameter Type Description

pPropertyName

String

String containing the name of the requested property.


The following table lists the different possible values for pPropertyName.

Parameter Values Description

ID

Internal number of the AK constraint.

KEY_NAME

Name of the alternate key

MESS

Error message relative to the constraint of the alternate key

FULL_NAME

Full name of the AK generated with the local object mask.

<flexfield code>

Value of the flexfield for this AK.


Examples

The alternate key of my table is named: <%=odiRef.getAK("KEY_NAME")%>

A.2.2 getAKColList() Method

Use to return information about the attributes of an alternate key.

Usage

public java.lang.String getAKColList( java.lang.String pStart,
java.lang.String pPattern,java.lang.String pEnd)
java.lang.String pSeparator,
java.lang.String pEnd)

Alternative syntax:

public java.lang.String getAKColList(
java.lang.String pPattern,
java.lang.String pSeparator)

Description

Returns a list of attributes and expressions for the alternate key currently checked.

The pPattern parameter is interpreted and then repeated for each element of the list. It is separated from its predecessor by the pSeparator parameter. The generated string starts with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains an element for each attribute of the current alternate key. It is accessible from a Check Knowledge Module if the current task is tagged as an "alternate key".

In an action, this method returns the list of the attributes of the alternate key handled by the DDL command, ordered by their position in the key.

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameters Type Description

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of attributes that can be used in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern sequence is replaced with its value. The attributes must be between brackets. ([ and])

Example «My string [COL_NAME] is a attribute»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.


Pattern Attributes List

The following table lists the different values of the parameters as well as their associated description.

Parameter Value Description

Parameter value

Description

I_COL

Attribute internal identifier

COL_NAME

Name of the key attribute

COL_HEADING

Header of the key attribute

COL_DESC

Attribute description

POS

Position of the attribute

LONGC

Length (Precision) of the attribute

SCALE

Scale of the attribute

FILE_POS

Beginning position of the attribute (fixed file)

BYTES

Number of physical bytes of the attribute

FILE_END_POS

End of the attribute (FILE_POS + BYTES)

IND_WRITE

Write right flag of the attribute

COL_MANDATORY

Mandatory character of the attribute:

  • 0: null authorized

  • 1: non null

CHECK_FLOW

Flow control flag of the attribute:

  • 0: do not check

  • 1: check

CHECK_STAT

Static control flag of the attribute:

  • 0: do not check

  • 1: check

COL_FORMAT

Logical format of the attribute

COL_DEC_SEP

Decimal symbol for the attribute

REC_CODE_LIST

List of the record codes retained for the attribute

COL_NULL_IF_ERR

Processing flag for the attribute:

  • 0: Reject

  • 1: Set active trace to null

  • 2: Set inactive trace to null

DEF_VALUE

Default value for the attribute

EXPRESSION

Not used

CX_COL_NAME

Not used

ALIAS_SEP

Grouping symbol used for the alias (from the technology)

SOURCE_DT

Code of the attribute's datatype.

SOURCE_CRE_DT

Create table syntax for the attribute's datatype.

SOURCE_WRI_DT

Create table syntax for the attribute's writable datatype.

DEST_DT

Code of the attribute's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the attribute's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the attribute's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this attribute in the data model.

<flexfield code>

Flexfield value for the current attribute.


Examples

If the CUSTOMER table has an alternate key AK_CUSTOMER (CUST_ID, CUST_NAME) and you want to generate the following code:

create table T_AK_CUSTOMER
(CUST_ID numeric(10) not null, CUST_NAME varchar(50) not null)

You can use the following code:

create table T_<%=odiRef.getAK("KEY_NAME")%>
<%=odiRef.getAKColList("(", "[COL_NAME] [DEST_CRE_DT] not null", ", ", ")")%>

Explanation: the getAKColList function will be used to generate the (CUST_ID numeric(10) not null, CUST_NAME varchar(50) not null) part, which starts and stops with a parenthesis and repeats the pattern (attribute, a data type, and not null) separated by commas for each attribute of the alternate key. Thus

  • the first parameter "(" of the function indicates that we want to start the string with the string "("

  • the second parameter "[COL_NAME] [DEST_CRE_DT] not null" indicates that we want to repeat this pattern for each attribute of the alternate key. The keywords [COL_NAME] and [DEST_CRE_DT] reference valid keywords of the Pattern Attributes List table

  • the third parameter ", " indicates that we want to separate interpreted occurrences of the pattern with the string ", "

  • the fourth parameter ")" of the function indicates that we want to end the string with the string ")"

A.2.3 getAllTargetColList() Method

Use to return information about all attributes of the target table of a mapping, including active and non-active attributes. Active attributes are those having an active mapping.

This method has the same usage and parameters as the getTargetTable() Method. See Section A.2.59, "getTargetColList() Method" for more details.

A.2.4 getCatalogName() Method

Use to return a catalog name from the topology.

Usage

public java.lang.String getCatalogName(
 java.lang.String pLogicalSchemaName,
java.lang.String pLocation)

public java.lang.String getCatalogName(
 java.lang.String pLogicalSchemaName,
java.lang.String pContextCode,
pContextCode, java.lang.String pLocation)

public java.lang.String getCatalogName(
java.lang.String pLocation)

public java.lang.String getCatalogName()

Description

Allows you to retrieve the name of a physical data catalog or work catalog, from its logical schema.

If the first syntax is used, the returned catalog name matches the current context.

If the second syntax is used, the returned catalog name is that of the context specified in the pContextCode parameter.

The third syntax returns the name of the data catalog (D) or work catalog (W) for the current logical schema in the current context.

The fourth syntax returns the name of the data catalog (D) for the current logical schema in the current context.

Parameters

Parameter Type Description

pLogicalSchemaName

String

Name of the logical schema

pContextCode

String

Code of the enforced context of the schema

pLocation

String

The valid values are:

  • W: Returns the work catalog of the physical schema that corresponds to the tuple (context, logical schema)

  • D: Returns the data catalog of the physical schema that corresponds to the tuple (context, logical schema)


Examples

If you have defined the physical schema Pluton.db_odi.dbo

Data catalog:

db_odi

Data schema:

dbo

Work catalog:

tempdb

Work schema:

temp_owner


that you have associated with this physical schema: MSSQL_ODI in the context CTX_DEV

The Call To Returns

<%=odiRef.getCatalogName("MSSQL_ODI", "CTX_DEV", "W")%>

tempdb

<%=odiRef.getCatalogName("MSSQL_ODI", "CTX_DEV", "D")%>

db_odi


A.2.5 getCatalogNameDefaultPSchema() Method

Use to return a catalog name for the default physical schema from the topology.

Usage

public java.lang.String getCatalogNameDefaultPSchema(
java.lang.String pLogicalSchemaName,
java.lang.String pLocation)

public java.lang.String getCatalogNameDefaultPSchema(
java.lang.String pLogicalSchemaName,
java.lang.String pContextCode,
java.lang.String pLocation)

public java.lang.String getCatalogNameDefaultPSchema(
java.lang.String pLocation)

public java.lang.String getCatalogNameDefaultPSchema()

Description

Allows you to retrieve the name of the default physical data catalog or work catalog for the data server to which is associated the physical schema corresponding to the tuple (logical schema, context). If no context is specified, the current context is used. If no logical schema name is specified, then the current logical schema is used. If no pLocation is specified, then the data catalog is returned.

Parameters

Parameter Type Description

pLogicalSchemaName

String

Name of the logical schema

pContextCode

String

Code of the enforced context of the schema

pLocation

String

The valid values are:

  • W: Returns the work catalog of the default physical schema associate to the data server to which the physical schema corresponding to the tuple (context, logical schema) is also attached.

  • D: Returns the data catalog of the physical schema corresponding to the tuple (context, logical schema)


Examples

If you have defined the physical schema Pluton.db_odi.dbo

Data catalog:

db_odi

Data schema:

dbo

Work catalog:

tempdb

Work schema:

temp_odi

Default Schema

Yes


that you have associated with this physical schema: MSSQL_ODI in the context CTX_DEV, and Pluton.db_doc.doc

Data catalog:

db_doc

Data schema:

doc

Work catalog:

tempdb

Work schema:

temp_doc

Default Schema

No


that you have associated with this physical schema: MSSQL_DOC in the context CTX_DEV.

The Call To Returns

<%=odiRef.getCatalogNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "W")%>

tempdb

<%=odiRef.getCatalogNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "D")%>

db_odi


A.2.6 getCK() Method

Use to return information about a condition.

Usage

public java.lang.String getCK(java.lang.String pPropertyName)

Description

This method returns information relative to a condition of a datastore during a check procedure. It is accessible from a Check Knowledge Module only if the current task is tagged as "condition".

In an action, this method returns information related to the check constraint currently handled by the DDL command.

Parameters

Parameter Type Description

pPropertyName

String

Current string containing the name of the requested property.


The following table lists the different values accepted by pPropertyName:

Parameter Value Description

ID

Internal number of the check constraint.

COND_ALIAS

Alias of the table used in the SQL statement

COND_NAME

Name of the condition

COND_TYPE

Type of the condition

COND_SQL

SQL statement of the condition

MESS

Error message relative to the check constraint

FULL_NAME

Full name of the check constraint generated with the local object mask.

COND_SQL_DDL

SQL statement of the condition with no table alias.

<flexfield code>

Flexfield value for this check constraint.


Examples

The current condition is called: <%=snpRep.getCK("COND_NAME")%>

insert into MY_ERROR_TABLE
select *
from MY_CHECKED_TABLE
where (not (<%=odiRef.getCK("COND_SQL")%>))

A.2.7 getColDefaultValue() Method

Use to return the default value of a mapped attribute.

Usage

public java.lang.String getColDefaultValue()

Description

Returns the default value of the target attribute of the mapping.

This method can be used in a mapping expression without the <%%> tags. This method call will insert in the generate code the default value set in the attribute definition. Depending on the attribute type, this value should be protected with quotes.

Parameters

None.

Examples

The default value of my target attribute is '+ 'odiRef.getColDefaultValue()'

A.2.8 getColList() Method

Use to return properties for each attribute from a filtered list of attributes. The properties are organized according to a string pattern.

Usage

public java.lang.String getColList(
java.lang.int pDSIndex,
java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd,
java.lang.String pSelector)

Alternative syntaxes:

public java.lang.String getColList(
java.lang.int pDSIndex,
java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)
public java.lang.String getColList(
java.lang.int pDSIndex,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pSelector)
public java.lang.String getColList(
java.lang.int pDSIndex,
java.lang.String pPattern,
java.lang.String pSeparator)

Description

Returns a list of attributes and expressions for a given dataset. The attributes list depends on the phase during which this method is called.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The pPattern parameter is interpreted and then repeated for each element of the list (selected according to pSelector parameter) and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

In the alternative syntax, any parameters not set are set to an empty string.

Note that this method automatically generates lookups with no specific code required.

Loading (LKM)

All active mapping expressions that are executed in the current execution unit, as well as all the attributes from the current execution unit used in the mapping, filters and joins expressions executed in the staging area appear in this list. The list is sorted by POS, FILE_POS.

If there is a journalized datastore in the source of the mapping, the three journalizing pseudo attributes JRN_FLAG, JRN_DATE, and JRN_SUBSCRIBER are added as attributes of the journalized source datastore.

Integration (IKM)

All current active mapping expressions in the current mapping appear in the list.

The list contains one element for each attribute that is loaded in the target table of the current mapping. The list is sorted by POS, FILE_POS, except when the target table is temporary. In this case it is not sorted.

If there is a journalized datastore in the source of the mapping, and it is located in the staging area, the three journalizing pseudo attributes JRN_FLG, JRN_DATE,and JRN_SUBSCRIBER are added as attributes of the journalized source datastore.

Check (CKM)

All the attributes of the target table (with static or flow control) appear in this list.

To distinguish attributes mapped in the current mapping, you must use the MAP selector.

Actions

All the attributes of the table handles by the DDL command appear in this list.

In the case of modified, added or deleted attributes, the NEW and OLD selectors are used to retrieve either the new version or the old version of the modified attribute being processed by the DDL command. The list is sorted by POS, FILE_POS when the table loaded is not temporary.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of the attributes usable in a pattern is detailed in the Pattern Attributes List below.

Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a attribute»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.

pSelector

String

String that designates a Boolean expression that allows to filter the elements of the initial list with the following format:

<SELECTOR> <Operator> <SELECTOR> etc. Parenthesis are authorized.

Authorized operators:

  1. No: NOT or!

  2. Or: OR or ||

  3. And: AND or &&

Example: (INS AND UPD) OR TRG

The description of valid selectors is provided below.


Pattern Attributes List

The following table lists different parameters values as well as their associated description.

Parameter Value Description

I_COL

Internal identifier of the attribute

COL_NAME

Name of the attribute

COL_HEADING

Header of the attribute

COL_DESC

Description of the attribute

POS

Position of the attribute

LONGC

Attribute length (Precision)

SCALE

Scale of the attribute

FILE_POS

Beginning (index) of the attribute

BYTES

Number of physical bytes in the attribute

FILE_END_POS

End of the attribute (FILE_POS + BYTES)

IND_WRITE

Write right flag of the attribute

COL_MANDATORY

Mandatory character of the attribute. Valid values are:

  • 0: null authorized

  • 1: not null

CHECK_FLOW

Flow control flag of the attribute. Valid values are:

  • 0: do not check

  • 1: check

CHECK_STAT

Static control flag of the attribute. Valid values are:

  • 0: do not check

  • 1: check

COL_FORMAT

Logical format of the attribute

COL_DEC_SEP

Decimal symbol of the attribute

REC_CODE_LIST

List of the record codes retained in the attribute

COL_NULL_IF_ERR

Processing flag of the attribute. Valid values are:

  • 0: Reject

  • 1: Set to null active trace

  • 2: Set to null inactive trace

DEF_VALUE

Default value of the attribute

EXPRESSION

Text of the expression executed on the source (expression as typed in the attribute mapping or attribute name making an expression executed on the staging area).

CX_COL_NAME

Computed name of the attribute used as a container for the current expression on the staging area

ALIAS_SEP

Separator used for the alias (from the technology)

SOURCE_DT

Code of the attribute's datatype.

SOURCE_CRE_DT

Create table syntax for the attribute's datatype.

SOURCE_WRI_DT

Create table syntax for the attribute's writable datatype.

DEST_DT

Code of the attribute's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the attribute's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the attribute's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this attribute in the data model.

MANDATORY_CLAUSE

Returns NOT NULL is the attribute is mandatory. Otherwise, returns the null keyword for the technology.

DEFAULT_CLAUSE

Returns DEFAULT <default value> if any default value exists. Otherwise, returns and empty string.

JDBC_TYPE

Data Services - JDBC Type of the attribute returned by the driver.

<flexfield code>

Flexfield value for the current attribute.


Selectors Description

Parameter Value Description

INS

  • LKM: Not applicable (*)

  • IKM: Only for mapping expressions marked with insertion

  • CKM: Not applicable

UPD

  • LKM: Not applicable (*)

  • IKM: Only for mapping expressions marked with update

  • CKM: Not applicable

TRG

  • LKM: Not applicable (*)

  • IKM: Only for mapping expressions executed on the target

  • CKM: Not applicable

NULL

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading not nullable attributes

  • CKM: All target attributes that do not accept null values

PK

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the primary key attributes

  • CKM: All the target attributes that are part of the primary key

UK

  • LKM: Not applicable (*)

  • IKM: All the mapping expressions loading the update key attribute chosen for the current mapping

  • CKM: Not applicable

REW

  • LKM: Not applicable (*)

  • IKM: All the mapping expressions loading the attributes with read only flag not selected

  • CKM: All the target attributes with read only flag not selected

UD1

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the attributes marked UD1

  • CKM: Not applicable

UD2

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the attributes marked UD2

  • CKM: Not applicable

UD3

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the attributes marked UD3

  • CKM: Not applicable

UD4

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the attributes marked UD4

  • CKM: Not applicable

UD5

  • LKM: Not applicable (*)

  • IKM: All mapping expressions loading the attributes marked UD5

  • CKM: Not applicable

MAP

  • LKM: Not applicable

  • IKM: Not applicable

  • CKM:

Flow control: All attributes of the target table loaded with expressions in the current mapping

Static control: All attributes of the target table

SCD_SK

LKM, CKM, IKM: All attributes marked SCD Behavior: Surrogate Key in the data model definition.

SCD_NK

LKM, CKM, IKM: All attributes marked SCD Behavior: Natural Key in the data model definition.

SCD_UPD

LKM, CKM, IKM: All attributes marked SCD Behavior: Overwrite on Change in the data model definition.

SCD_INS

LKM, CKM, IKM: All attributes marked SCD Behavior: Add Row on Change in the data model definition.

SCD_FLAG

LKM, CKM, IKM: All attributes marked SCD Behavior: Current Record Flag in the data model definition.

SCD_START

LKM, CKM, IKM: All attributes marked SCD Behavior: Starting Timestamp in the data model definition.

SCD_END

LKM, CKM, IKM: All attributes marked SCD Behavior: Ending Timestamp in the data model definition.

NEW

Actions: the attribute added to a table, the new version of the modified attribute of a table.

OLD

Actions: The attribute dropped from a table, the old version of the modified attribute of a table.

WS_INS

SKM: The attribute is flagged as allowing INSERT using Data Services.

WS_UPD

SKM: The attribute is flagged as allowing UDATE using Data Services.

WS_SEL

SKM: The attribute is flagged as allowing SELECT using Data Services.


Note:

Using certain selectors in an LKM - indicated in the previous table with an * - is possible but not recommended. Only attributes mapped on the source in the mapping are returned. As a consequence, the result could be incorrect depending on the mapping. For example, for the UK selector, the attributes of the key that are not mapped or that are not executed on the source will not be returned with the selector.

Examples

If the CUSTOMER table contains the attributes (CUST_ID, CUST_NAME, AGE) and we want to generate the following code:

create table CUSTOMER (CUST_ID numeric(10) null,
CUST_NAME varchar(50) null, AGE numeric(3) null)

The following code is sufficient:

create table CUSTOMER
<%=odiRef.getColList("(", "[COL_NAME] [SOURCE_CRE_DT] null", ", ", ")", "")%>

Explanation: the getColList function will be used to generate (CUST_ID numeric(10) null, CUST_NAME varchar(50) null, AGE numeric(3) null). It will start and end with a parenthesis and repeat a pattern (attribute, data type, and null) separated by commas for each attribute. Thus,

  • the first character "(" of the function indicates that we want to start the string with the string "("

  • the second parameter "[COL_NAME] [SOURCE_CRE_DT] null" indicates that we want to repeat this pattern for each attribute. The keywords [COL_NAME] and [SOURCE_CRE_DT] are references to valid keywords of the table Pattern Attribute List

  • the third parameter ", " indicates that we want to separate the interpreted occurrences of the pattern with the string ", ".

  • the fourth parameter ")" of the function indicates that we want to end the string with the string ")"

  • the last parameter "" indicates that we want to repeat the pattern for each attribute (with no selection)

A.2.9 getColumn() Method

Use to return information about a specific attribute handled by an action.

Usage

public java.lang.String getColumn(
java.lang.String pPattern,
java.lang.String pSelector)

public java.lang.String getColumn(
java.lang.String pPattern)

Description

In an action, returns information on a attribute being handled by an the action.

Parameters

Parameters Type Description

pPattern

String

Pattern of values rendered for the attribute.

The list of the attributes usable in a pattern is detailed in the Pattern Attributes List below.

Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a attribute»

pSelector

String

The Selector may take one of the following value:

  • NEW: returns the new version of the modified attribute or the new attribute.

  • OLD: returns the old version of the modified attribute or the dropped attribute.

If the selector is omitted, it is set to OLD for all drop actions. Otherwise, it is set to NEW.


Pattern Attributes List

The following table lists different parameters values as well as their associated description.

Parameter Value Description

I_COL

Internal identifier of the attribute

COL_NAME

Name of the attribute

COL_HEADING

Header of the attribute

COL_DESC

Description of the attribute

POS

Position of the attribute

LONGC

Attribute length (Precision)

SCALE

Scale of the attribute

FILE_POS

Beginning (index) of the attribute

BYTES

Number of physical bytes in the attribute

FILE_END_POS

End of the attribute (FILE_POS + BYTES)

IND_WRITE

Write right flag of the attribute

COL_MANDATORY

Mandatory character of the attribute. Valid values are:

  • 0: null authorized

  • 1: not null

CHECK_FLOW

Flow control flag of the attribute. Valid values are:

  • 0: do not check

  • 1: check

CHECK_STAT

Static control flag of the attribute. Valid values are:

  • 0: do not check

  • 1: check

COL_FORMAT

Logical format of the attribute

COL_DEC_SEP

Decimal symbol of the attribute

REC_CODE_LIST

List of the record codes retained in the attribute

COL_NULL_IF_ERR

Processing flag of the attribute. Valid values are:

  • 0: Reject

  • 1: Set to null active trace

  • 2: Set to null inactive trace

DEF_VALUE

Default value of the attribute

EXPRESSION

Text of the expression executed on the source (expression as typed in the mapping or attribute name making an expression executed on the staging area).

CX_COL_NAME

Computed name of the attribute used as a container for the current expression on the staging area

ALIAS_SEP

Separator used for the alias (from the technology)

SOURCE_DT

Code of the attribute's datatype.

SOURCE_CRE_DT

Create table syntax for the attribute's datatype.

SOURCE_WRI_DT

Create table syntax for the attribute's writable datatype.

DEST_DT

Code of the attribute's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the attribute's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the attribute's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this attribute in the data model.

MANDATORY_CLAUSE

Returns NOT NULL if the attribute is mandatory. Otherwise, returns the null keyword for the technology.

DEFAULT_CLAUSE

Returns DEFAULT <default value> if any default value exists. Otherwise, returns and empty string.

<flexfield code>

Flexfield value for the current attribute.


A.2.10 getContext() Method

Use to return information about the current context.

Usage

public java.lang.String getContext(java.lang.String pPropertyName)

Description

This method returns information about to the current execution context.

Parameters

Parameter Type Description

pPropertyName

String

String containing the name of the requested property.


The following table lists the different possible values for pPropertyName.

Parameter Value Description

ID

Internal ID of the context.

CTX_NAME

Name of the context.

CTX_CODE

Code of the context.

CTX_DEFAULT

Returns 1 for the default context, 0 for the other contexts.

<flexfield code>

Flexfield value for this reference.


Examples

Current Context = <%=getContext("CTX_NAME")%>

A.2.11 getDataSet() Method

Use to return information about a given dataset of a mapping.

Usage

public java.lang.String getDataSet(
java.lang.Int pDSIndex,
java.lang.String pPropertyName)

Description

Retrieves information about for a given dataset of a mapping.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.


The following table lists the different possible values for pPropertyName.

Parameter Value Description

OPERATOR

Operator that applies to the selected dataset. For the first dataset, an empty value is returned.

NAME

Dataset Name.

HAS_JRN

Returns "1" if the dataset one journalized datastore, "0" otherwise.


Examples

<%for (int i=0; i < odiRef.getDataSetCount(); i++){%><%=odiRef.getDataSet(i, "Operator")%>select  <%=odiRef.getPop("DISTINCT_ROWS")%>      <%=odiRef.getColList(i,"", "[EXPRESSION] [COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%> from  <%=odiRef.getFrom(i)%>where     <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %> JRN_FLAG <> 'D        '<%} else {%>    (1=1)    <% } %><%=odiRef.getJoin(i)%><%=odiRef.getFilter(i)%><%=odiRef.getJrnFilter(i)%><%=odiRef.getGrpBy(i)%><%=odiRef.getHaving(i)%>
<%}%>

A.2.12 getDataSetCount() Method

Use to return the number of datasets of a mapping.

Usage

public java.lang.Int getDataSetCount()

Description

Returns the number of datasets of a mapping.

Parameters

None

Examples

<%for (int i=0; i < odiRef.getDataSetCount(); i++){%><%=odiRef.getDataSet(i, "Operator")%>select  <%=odiRef.getPop("DISTINCT_ROWS")%>      <%=odiRef.getColList(i,"", "[EXPRESSION] [COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%> from  <%=odiRef.getFrom(i)%>where     <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %> JRN_FLAG <> 'D        '<%} else {%>    (1=1)    <% } %><%=odiRef.getJoin(i)%><%=odiRef.getFilter(i)%><%=odiRef.getJrnFilter(i)%><%=odiRef.getGrpBy(i)%><%=odiRef.getHaving(i)%>
<%}%>

A.2.13 getDataType() Method

Use to return the syntax creating a attribute of a given datatype.

Usage

public java.lang.String getDataType(
java.lang.String pDataTypeName,
java.lang.String pDataTypeLength,
java.lang.String pDataTypePrecision)

Description

Returns the creation syntax of the following SQL data types: varchar, numeric or date according to the parameters associated to the source or target technology.

Parameters

Parameters Type Description

Parameter

Type

Description

pDataTypeName

String

Name of the data type as listed in the table below

pDataTypeLength

String

Length of the data type

pDataTypePrecision

String

Precision of the data type


The following table lists all possible values for pDataTypeName.

Parameter Value Description

SRC_VARCHAR

Returns the syntax to the source data type varchar

SRC_NUMERIC

Returns the syntax to the source data type numeric

SRC_DATE

Returns the syntax to the source data type date

DEST_VARCHAR

Returns the syntax to the target data type varchar

DEST_NUMERIC

Returns the syntax to the target data type numeric

DEST_DATE

Returns the syntax to the target data type date


Examples

Given the following syntax for these technologies:

Technology Varchar Numeric Date

Oracle

varchar2(%L)

number(%L,%P)

date

Microsoft SQL Server

varchar(%L)

numeric(%L,%P)

datetime

Microsoft Access

Text(%L)

double

datetime


Here are some examples of call to getDataType:

Call Oracle SQL Server Access

<%=odiRef.getDataType("DEST_VARCHAR", "10", "")%>

varchar2(10)

varchar(10)

Text(10)

<%=odiRef.getDataType("DEST_VARCHAR", "10", "5")%>

varchar2(10)

varchar(10)

Text(10)

<%=odiRef.getDataType("DEST_NUMERIC", "10", "")%>

number(10)

numeric(10)

double

<%=odiRef.getDataType("DEST_NUMERIC", "10", "2")%>

number(10,2)

numeric(10,2)

double

<%=odiRef.getDataType("DEST_NUMERIC", "", "")%>

number

numeric

double

<%=odiRef.getDataType("DEST_DATE", "", "")%>

date

datetime

datetime

<%=odiRef.getDataType("DEST_DATE", "10", "2")%>

date

datetime

datetime


A.2.14 getFilter() Method

Use to return the entire WHERE clause section generated for the filters of a mapping.

Usage

public java.lang.String getFilter(java.lang.Int pDSIndex)

Description

Returns the SQL filters sequence (on the source while loading, on the staging area while integrating) for a given dataset.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.


None

Examples

insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

A.2.15 getFilterList() Method

Use to return properties for each filter of a mapping. The properties are organized according to a string pattern.

Usage

public java.lang.String getFilterList( 
java.lang.Int pDSIndex,
java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

Alternative syntax:

public java.lang.String getFilterList(
java.lang.Int pDSIndex,
java.lang.String pPattern,
java.lang.String pSeparator)

Description

Returns a list of occurrences of the SQL filters of a given dataset of a mapping.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The parameter pPattern is interpreted and repeated for each element of the list and separated from its predecessor with parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains an element for each filter expression executed on the source or target (depending on the Knowledge Module in use).

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern will be repeated for each occurrence of the list.

The list of possible in a list is available in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. Attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a attribute»

pSeparator

String

This parameter is used to separate a pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.


Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Parameter Value Description

ID

Filter internal identifier

EXPRESSION

Text of the filter expression.


Examples

insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilterList("and ","([EXPRESSION])"," and ","")%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

Explanation: the getFilterList function will be used to generate the filter of the SELECT clause that must begin with "and" and repeats the pattern (expression of each filter) separated with "and" for each filter. Thus

  • The first parameter "and" of the function indicates that we want to start the string with the string "and"

  • the second parameter "([EXPRESSION])" indicates that we want to repeat this pattern for each filter. The keywords [EXPRESSION] references a valid keyword of the table Pattern Attribute List

  • the third parameter " and " indicates that we want to separate each interpreted occurrence of the pattern with the string "and ".

  • the fourth parameter "" of the function indicates that we want to end the string with no specific character.

A.2.16 getFK() Method

Use to return information about a foreign key.

Usage

public java.lang.String getFK(java.lang.String pPropertyName)

Description

This method returns information relative to the foreign key (or join or reference) of a datastore during a check procedure. It is accessible from a Knowledge Module only if the current task is tagged as a "reference".

In an action, this method returns information related to the foreign key currently handled by the DDL command.

Parameters

Parameter Type Description

pPropertyName

String

String containing the name of the requested property.


The following table lists the different possible values for pPropertyName.

Parameter Value Description

ID

Internal number of the reference constraint.

FK_NAME

Name of the reference constraint.

FK_TYPE

Type of the reference constraint.

FK_ALIAS

Alias of the reference table (only used in case of a complex expression)

PK_ALIAS

Alias of the referenced table (only used in case of a complex expression)

ID_TABLE_PK

Internal number of the referenced table.

PK_I_MOD

Number of the referenced model.

PK_CATALOG

Catalog of the referenced table in the current context.

PK_SCHEMA

Physical schema of the referenced table in the current context.

PK_TABLE_NAME

Name of the referenced table.

COMPLEX_SQL

Complex SQL statement of the join clause (if appropriate).

MESS

Error message of the reference constraint

FULL_NAME

Full name of the foreign key generated with the local object mask.

<flexfield code>

Flexfield value for this reference.


Examples

The current reference key of my table is called: <%=odiRef.getFK("FK_NAME")%>. It references the table <%=odiRef.getFK("PK_TABLE_NAME")%> that is in the schema <%=odiRef.getFK("PK_SCHEMA")%>

A.2.17 getFKColList() Method

Use to return information about the attributes of a foreign key.

Usage

public java.lang.String getFKColList(java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

Alternative syntax:

public java.lang.String getFKColList(
java.lang.String pPattern,
java.lang.String pSeparator)

Description

Returns a list of attributes part of a reference constraint (foreign key).

The parameter pPattern in interpreted and repeated for each element of the list, and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains one element for each attribute of the current foreign key. It is accessible from a Check Knowledge Module only if the current task is tagged as a "reference".

In an action, this method returns the list of the attributes of the foreign key handled by the DDL command, ordered by their position in the key.

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameter Type Description

Parameter

Type

Description

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of possible attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a attribute»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to generate.


Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Parameter Value Description

I_COL

Attribute internal identifier

COL_NAME

Name of the attribute of the key

COL_HEADING

Header of the attribute of the key

COL_DESC

Description of the attribute of the key

POS

Position of the attribute of the key

LONGC

Length (Precision) of the attribute of the key

SCALE

Scale of the attribute of the key

FILE_POS

Beginning (index) of the attribute

BYTES

Number of physical octets of the attribute

FILE_END_POS

End of the attribute (FILE_POS + BYTES)

IND_WRITE

Write right flag of the attribute

COL_MANDATORY

Mandatory character of the attribute. Valid values are:

  • 0: not authorized

  • 1: not null

CHECK_FLOW

Flow control flag of the attribute. Valid values are:

  • 0: do not check

  • 1: check

CHECK_STAT

Static control flag of the attribute. Valid values are:

  • 0: do not check

  • 1: check

COL_FORMAT

Logical format of the attribute

COL_DEC_SEP

Decimal symbol for the attribute

REC_CODE_LIST

List of the record codes for the attribute

COL_NULL_IF_ERR

Attribute processing flag. Valid values are:

  • 0: Reject

  • 1: Set active trace to null

  • 2: Set inactive trace to null

DEF_VALUE

Default value of the attribute

EXPRESSION

Not used

CX_COL_NAME

Not used

ALIAS_SEP

Separator used for the alias (from the technology)

SOURCE_DT

Code of the attribute's datatype.

SOURCE_CRE_DT

Create table syntax for the attribute's datatype.

SOURCE_WRI_DT

Create table syntax for the attribute's writable datatype.

DEST_DT

Code of the attribute's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the attribute's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the attribute's writable datatype converted to a datatype on the target technology.

PK_I_COL

Internal identifier of the referenced attribute

PK_COL_NAME

Name of the referenced key attribute

PK_COL_HEADING

Header of the referenced key attribute

PK_COL_DESC

Description of the referenced key attribute

PK_POS

Position of the referenced attribute

PK_LONGC

Length of the referenced attribute

PK_SCALE

Precision of the referenced attribute

PK_FILE_POS

Beginning (index) of the referenced attribute

PK_BYTES

Number of physical octets of the referenced attribute

PK_FILE_END_POS

End of the referenced attribute (FILE_POS + BYTES)

PK_IND_WRITE

Write right flag of the referenced attribute

PK_COL_MANDATORY

Mandatory character of the referenced attribute. Valid values are:

  • 0: null authorized

  • 1: not null

PK_CHECK_FLOW

Flow control flag of the referenced attribute. Valid values are:

  • 0: do not check

  • 1: check

PK_CHECK_STAT

Static control flag of the referenced attribute. Valid values are:

  • 0: do not check

  • 1: check

PK_COL_FORMAT

Logical format of the referenced attribute

PK_COL_DEC_SEP

Decimal separator for the referenced attribute

PK_REC_CODE_LIST

List of record codes retained for the referenced attribute

PK_COL_NULL_IF_ERR

Processing flag of the referenced attribute. Valid values are:

  • 0: Reject

  • 1: Set active trace to null

  • 2: Set inactive trace to null

PK_DEF_VALUE

Default value of the referenced attribute

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this attribute in the data model.

<flexfield code>

Flexfield value for the current attribute of the referencing table.


Examples

If the CUSTOMER table references the CITY table on CUSTOMER.COUNTRY_ID = CITY.ID_COUNT and CUSTOMER.CITY_ID = CITY.ID_CIT

the clause:

(CUS.COUNTRY_ID = CITY.ID_COUNT and CUS.CITY_ID = CITY.ID_CIT)

can also be written:

<%=odiRef.getFKColList("(", "CUS.[COL_NAME] = CITY.[PK_COL_NAME]", " and
", ")" )%>

Explanation: the getFKColList function will be used to loop on each attribute of the foreign key to generate the clause that begins and ends with a parenthesis and that repeats a pattern separated by and for each attribute in the foreign key. Thus

  • The first parameter "(" of the function indicates that we want to begin the string with "("

  • The second parameter "CUS.[COL_NAME] = CITY.[PK_COL_NAME]" indicates that we want to repeat this pattern for each attribute of the foreign key. The keywords [COL_NAME] and [PK_COL_NAME] reference valid keywords in the table Pattern Attributes List

  • The third parameter" and " indicates that we want to separate the occurrences of the pattern with the string " and ".

  • The fourth parameter ")" of the function indicates that we want to end the string with ")".

A.2.18 getFlexFieldValue() Method

Use to return the value of a flexfield.

Usage

public java.lang.String getFlexFieldValue(java.lang.String pI_Instance,
java.lang.String pI_Object, java.lang.String pFlexFieldCode)

Description

This method returns the value of an Object Instance's Flexfield.

Parameters

Parameter Type Description

pI_Instance

String

Internal Identifier of the Object Instance, as it appears in the version tab of the object instance window.

pI_Object

String

Internal Identifier of the Object type, as it appears in the version tab of the object window for the object type.

pPropertyName

String

Flexfield Code which value should be returned.


Examples

<%=odiRef.getFlexFieldValue("32001","2400","MY_DATASTORE_FIELD")%>

Returns the value of the flexfield MY_DATASTORE_FIELD, for the object instance of type datastore (Internal ID for datastores is 2400), with the internal ID 32001.

A.2.19 getFrom() Method

Use to return the SQL FROM clause in the given context.

Usage

public java.lang.String getFrom(java.lang.Int pDSIndex)

Description

Allows the retrieval of the SQL string of the FROM in the source SELECT clause for a given dataset. The FROM statement is built from tables and joins (and according to the SQL capabilities of the technologies) that are used in this dataset.

For a technology that supports ISO outer joins and parenthesis, getFrom() could return a string such as:

((CUSTOMER as CUS inner join CITY as CIT on (CUS.CITY_ID = CIT.CITY_ID))
left outer join SALES_PERSON as SP on (CUS.SALES_ID = SP.SALE_ID))

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

If there is a journalized datastore in source of the mapping, the source table in the clause is replaced by the data view linked to the journalized source datastore.

If one of the source datastores is a temporary datastore with the Use Temporary Mapping as Derived Table (Sub-Select) box selected then a sub-select statement will be generated for this temporary source by the getFrom method.

If partitioning is used on source datastores, this method automatically adds the partitioning clauses when returning the object names.

Note that this method automatically generates lookups with no specific code required.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.


Examples

insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

A.2.20 getGrpBy() Method

Use to return the entire SQL GROUP BY clause in the given context.

Usage

public java.lang.String getGrpBy(java.lang.Int pDSIndex)

Description

Allows you to retrieve the SQL GROUP BY string (on the "source" during the loading phase, on the staging area during the integration phase) for a given dataset. This statement is automatically computed from the aggregation transformations detected in the mapping expressions.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.


Examples

insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

A.2.21 getGrpByList() Method

Use to return properties for each GROUP BY clause for a given dataset in a mapping. The properties are organized according to a string pattern.

Usage

public java.lang.String getGrpByList(
java.lang.Int pDSIndex,
java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

Alternative syntax:

public java.lang.String getGrpByList(
java.lang.Int pDSIndex,
java.lang.String pPattern,
java.lang.String pSeparator)

Description

Returns a list of occurrences of SQL GROUP BY for a given dataset of a mapping.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The pPattern parameter is interpreted, then repeated for each element of the list and separated from its predecessor with the pSeparator parameter. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains an element for each GROUP BY statement on the source or target (according to the Knowledge Module that used it).

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of possible attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a attribute»

pSeparator

String

This parameter is used to separate each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to be generated.


Pattern Attributes List

The following table lists the different values of the parameters as well as their associated description.

Parameter Value Description

ID

Internal identifier of the clause

EXPRESSION

Text of the grouping statement


Examples

insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpByList("group by ", "[EXPRESSION]", " , ", "")%>
<%=odiRef.getHaving()%>

Explanation: the getGrpByList function will be used to generate the group by clause of the select order that must start with "group by" and that repeats a pattern (each grouping expression) separated by commas for each expression.

  • The first parameter "group by" of the function indicates that we want to start the string with "group by"

  • The second parameter "[EXPRESSION]" indicates that we want to repeat this pattern for each group by expression. The keyword [EXPRESSION] references a valid keyword of the table Pattern Attributes List

  • The third parameter ", " indicates that we want to separate the interpreted occurrences of the pattern with a comma.

  • The fourth parameter "" of the function indicates that we want to end the string with no specific character

A.2.22 getHaving() Method

Use to return the entire SQL HAVING clause in the given context.

Usage

public java.lang.String getHaving(java.lang.Int pDSIndex)

Description

Allows the retrieval of the SQL statement HAVING (on the source during loading, on the staging area during integration) for a given dataset. This statement is automatically computed from the filter expressions containing detected aggregation functions.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.


Examples

insert into <%=odiRef.getTable(
"L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
 <%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

A.2.23 getHavingList() Method

Use to return properties for each HAVING clause of a mapping. The properties are organized according to a string pattern.

Usage

public java.lang.String getHavingList(
java.lang.Int pDSIndex,
java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

Alternative syntax:

public java.lang.String getHavingList(
java.lang.Int pDSIndex,
java.lang.String pPattern,
java.lang.String pSeparator)

Description

Returns a list of the occurrences of SQL HAVING of a given dataset in a mapping.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The parameter pPattern is interpreted and repeated for each element of the list, and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains one element for each HAVING expression to execute on the source or target (depends on the Knowledge module that uses it).

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameters Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of authorized attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a attribute»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to generate.


Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Parameter Value Description

Parameter value

Description

ID

Internal identifier of the clause

EXPRESSION

Text of the having expression


Examples

insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpByList("group by ", "[EXPRESSION]", " , ", "")%>
<%=odiRef.getHavingList("having ", "([EXPRESSION])", " and ", "")%>

Explanation: The getHavingList function will be used to generate the having clause of the select order that must start with "having" and that repeats a pattern (each aggregated filtered expression) separated by "and" for each expression.

  • The first parameter "having " of the function indicates that we want to start the string with "having"

  • The second parameter "([EXPRESSION])" indicates that we want to repeat this pattern for each aggregated filter. The keyword [EXPRESSION] references a valid keyword of the table Pattern Attributes List

  • The third parameter" and " indicates that we want to separate each interpreted occurrence of the pattern with the string " and ".

  • The fourth parameter "" of the function indicates that we want to end the string with no specific character

A.2.24 getIndex() Method

Use to return information about a specific index handled by an action.

Usage

public java.lang.String getIndex(java.lang.String pPropertyName)

Description

In an action, this method returns information related to the index currently handled by the DDL command.

Parameters

Parameter Type Description

pPropertyName

String

String containing the name of the requested property.


The following table lists the different possible values for pPropertyName.

Parameter Value Description

ID

Internal number of the index.

KEY_NAME

Name of the index

FULL_NAME

Full name of the index generated with the local object mask.

<flexfield code>

Value of the flexfield for this index.


A.2.25 getIndexColList() Method

Use to return information about the attributes of an index handled by an action.

Usage

public java.lang.String getIndexColList(java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

Description

In an action, this method returns the list of the attributes of the index handled by the DDL command, ordered by their position in the index.

The pPattern parameter is interpreted and then repeated for each element of the list. It is separated from its predecessor by the pSeparator parameter. The generated string starts with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains an element for each attribute of the current index.

Parameters

Parameters Type Description

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of attributes that can be used in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern sequence is replaced with its value. The attributes must be between brackets. ([ and ])

Example «My string [COL_NAME] is a attribute»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.


Pattern Attributes List

The following table lists the different values of the parameters as well as their associated description.

Parameter Value Description

I_COL

Attribute internal identifier

COL_NAME

Name of the index attribute

COL_HEADING

Header of the index attribute

COL_DESC

Attribute description

POS

Position of the attribute

LONGC

Length (Precision) of the attribute

SCALE

Scale of the attribute

FILE_POS

Beginning position of the attribute (fixed file)

BYTES

Number of physical bytes of the attribute

FILE_END_POS

End of the attribute (FILE_POS + BYTES)

IND_WRITE

Write right flag of the attribute

COL_MANDATORY

Mandatory character of the attribute. Valid values are:

  • 0: null authorized

  • 1: non null

CHECK_FLOW

Flow control flag for of the attribute. Valid values are:

  • 0: do not check

  • 1: check

CHECK_STAT

Static control flag of the attribute. Valid values are:

  • 0: do not check

  • 1: check

COL_FORMAT

Logical format of the attribute

COL_DEC_SEP

Decimal symbol for the attribute

REC_CODE_LIST

List of the record codes retained for the attribute

COL_NULL_IF_ERR

Processing flag for the attribute. Valid values are:

  • 0: Reject

  • 1: Set active trace to null

  • 2: Set inactive trace to null

DEF_VALUE

Default value for the attribute

EXPRESSION

Not used

CX_COL_NAME

Not used

ALIAS_SEP

Grouping symbol used for the alias (from the technology)

SOURCE_DT

Code of the attribute's datatype.

SOURCE_CRE_DT

Create table syntax for the attribute's datatype.

SOURCE_WRI_DT

Create table syntax for the attribute's writable datatype.

DEST_DT

Code of the attribute's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the attribute's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the attribute's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this attribute in the data model.

<flexfield code>

Flexfield value for the current attribute.


A.2.26 getInfo() Method

Use to return information about the current task.

Usage

public java.lang.String getInfo(java.lang.String pPropertyName)

Description

This method returns information about the current task. The list of available information is described in the pPropertyName values table.

Parameters

Parameter Type Description

pPropertyName

String

String containing the name of the requested property.


The following table lists the different values possible for pPropertyName:

Parameter Value Description

I_SRC_SET

Internal identifier of the current Execution Unit if the task belongs to a Loading Knowledge Module

SRC_SET_NAME

Name of the current Execution Unit if the task belongs to a Loading Knowledge Module

COLL_NAME

Name of the current loading resource (C$) if the task belongs to a Loading Knowledge Module

INT_NAME

Name of the current integration resource (I$) if the task belongs to a string Loading, Integration or Check Knowledge Module.

ERR_NAME

Name of the current error resource (E$) if the task is part of a Loading, Integration or Check Knowledge Module

TARG_NAME

Name of the target resource if the task is part of a Loading, Integration or Check Knowledge Module

SRC_CATALOG

Name of the data catalog in the source environment

SRC_SCHEMA

Name of the data schema in the source environment

SRC_WORK_CATALOG

Name of the work catalog in the source environment

SRC_WORK_SCHEMA

Name of the work schema in the source environment

DEST_CATALOG

Name of the data catalog in the target environment

DEST_SCHEMA

Name of the data schema in the target environment

DEST_WORK_CATALOG

Name of the work catalog in the target environment

DEST_WORK_SCHEMA

Name of the work schema in the target environment

SRC_TECHNO_NAME

Name of the source technology

SRC_CON_NAME

Name of the source connection

SRC_DSERV_NAME

Name of the data server of the source machine

SRC_CONNECT_TYPE

Connection type of the source machine

SRC_IND_JNDI

JNDI URL flag

SRC_JAVA_DRIVER

Name of the JDBC driver of the source connection

SRC_JAVA_URL

JDBC URL of the source connection

SRC_JNDI_AUTHENT

JNDI authentication type

SRC_JNDI_PROTO

JNDI source protocol

SRC_JNDI_FACTORY

JNDI source Factory

SRC_JNDI_URL

Source JNDI URL

SRC_JNDI_RESSOURCE

Accessed source JNDI resource

SRC_JNDI_USER

User name for JNDI authentication on the source.

SRC_JNDI_ENCODED_PASS

Encrypted password for JNDI authentication on the source.

SRC_USER_NAME

User name of the source connection

SRC_ENCODED_PASS

Encrypted password of the source connection

SRC_FETCH_ARRAY

Size of the source array fetch

SRC_BATCH_UPDATE

Size of the source batch update

SRC_EXE_CHANNEL

Execution canal of the source connection

SRC_COL_ALIAS_WORD

Term used to separated the attributes from their aliases for the source technology

SRC_TAB_ALIAS_WORD

Term used to separated the tables from their aliases for the source technology

SRC_DATE_FCT

Function returning the current date for the source technology

SRC_DDL_NULL

Returns the definition used for the keyword NULL during the creation of a table on the source

SRC_MAX_COL_NAME_LEN

Maximum number of characters for the attribute name on the source technology

SRC_MAX_TAB_NAME_LEN

Maximum number of characters for the table name on the source technology

SRC_REM_OBJ_PATTERN

Substitution model for a remote object on the source technology.

SRC_LOC_OBJ_PATTERN

Substitution model for a local object name on the source technology.

DEST_TECHNO_NAME

Name of the target technology

DEST_CON_NAME

Name of the target connection

DEST_DSERV_NAME

Name of the data server of the target machine

DEST_CONNECT_TYPE

Connection type of the target machine

DEST_IND_JNDI

Target JNDI URL flag

DEST_JAVA_DRIVER

Name of the JDBC driver of the target connection

DEST_JAVA_URL

JDBC URL of the target connection

DEST_JNDI_AUTHENT

JNDI authentication type of the target

DEST_JNDI_PROTO

JNDI target protocol

DEST_JNDI_FACTORY

JNDI target Factory

DEST_JNDI_URL

JNDI URL of the target

DEST_JNDI_RESSOURCE

Target JNDI resource that is accessed

DEST_JNDI_USER

User name for JNDI authentication on the target.

DEST_JNDI_ENCODED_PASS

Encrypted password for JNDI authentication on the target.

DEST_USER_NAME

Name of the user for the target connection

DEST_ENCODED_PASS

Encrypted password for the target connection

DEST_FETCH_ARRAY

Size of the target array fetch

DEST_BATCH_UPDATE

Size of the target batch update

DEST_EXE_CHANNEL

Execution canal of the target connection

DEST_COL_ALIAS_WORD

Term used to separate the attributes from their aliases on the target technology

DEST_TAB_ALIAS_WORD

Term used to separate the tables from their aliases on the target technology

DEST_DATE_FCT

Function returning the current date on the target technology

DEST_DDL_NULL

Function returning the definition used for the keyword NULL during the creation on a table on the target

DEST_MAX_COL_NAME_LEN

Maximum number of characters of the attribute in the target technology

DEST_MAX_TAB_NAME_LEN

Maximum number of characters of the table name on the target technology

DEST_REM_OBJ_PATTERN

Substitution model for a remote object on the target technology

DEST_LOC_OBJ_PATTERN

Substitution model for a local object name on the target technology

CT_ERR_TYPE

Error type (F: Flow, S: Static). Applies only in the case of a Check Knowledge Module

CT_ERR_ID

Error identifier (Table # for a static control or mapping number for flow control. Applies only in the case of a Check Knowledge Module

CT_ORIGIN

Name that identifies the origin of an error (Name of a table for static control, or name of a mapping prefixed with the project code). Applies only in the case of a Check Knowledge Module

JRN_NAME

Name of the journalized datastore.

JRN_VIEW

Name of the view linked to the journalized datastore.

JRN_DATA_VIEW

Name of the data view linked to the journalized datastore.

JRN_TRIGGER

Name of the trigger linked to the journalized datastore.

JRN_ITRIGGER

Name of the Insert trigger linked to the journalized datastore.

JRN_UTRIGGER

Name of the Update trigger linked to the journalized datastore.

JRN_DTRIGGER

Name of the Delete trigger linked to the journalized datastore.

SUBSCRIBER_TABLE

Name of the datastore containing the subscribers list.

CDC_SET_TABLE

Full name of the table containing list of CDC sets.

CDC_TABLE_TABLE

Full name of the table containing the list of tables journalized through CDC sets.

CDC_SUBS_TABLE

Full name of the table containing the list of subscribers to CDC sets.

CDC_OBJECTS_TABLE

Full name of the table containing the journalizing parameters and objects.

SRC_DEF_CATALOG

Default catalog for the source data server.

SRC_DEF_SCHEMA

Default schema for the source data server.

SRC_DEFW_CATALOG

Default work catalog for the source data server.

SRC_DEFW_SCHEMA

Default work schema for the source data server.

DEST_DEF_CATALOG

Default catalog for the target data server.

DEST_DEF_SCHEMA

Default schema for the target data server.

DEST_DEFW_CATALOG

Default work catalog for the target data server.

DEST_DEFW_SCHEMA

Default work schema for the target data server.

SRC_LSCHEMA_NAME

Source logical schema name.

DEST_LSCHEMA_NAME

Target logical schema name.

SRC_I_CONNECT

Internal ID of the source data server.

SRC_I_PSCHEMA

Internal ID of the source physical schema.

SRC_I_LSCHEMA

Internal ID of the source logical schema.

SRC_I_TECHNO

Internal ID of the source technology.

DEST_I_CONNECT

Internal ID of the target data server.

DEST_I_PSCHEMA

Internal ID of the target physical schema.

DEST_I_LSCHEMA

Internal ID of the target logical schema.

DEST_I_TECHNO

Internal ID of the target technology.


Examples

The current source condition is: <%=odiRef.getInfo("SRC_CON_NAME")%>  on server: <%=odiRef.getInfo("SRC_DSERV_NAME")%>

A.2.27 getJDBCConnection() Method

Use to return the source or target JDBC connection.

Usage

java.sql.Connection getJDBCConnection(
java.lang.String pPropertyName)

Description

This method returns the source or target JDBC connection for the current task.

Note:

  • This method does not return a string, but a JDBC connection object. This object may be used in your Java code within the task.

  • It is recommended to close the JDBC connections acquired using this method once you are done with the connection. This will improve the concurrency if your KM is used in ODI mappings.

Parameters

Parameter Type Description

pPropertyName

String

Name of connection to be returned.


The following table lists the different values possible for pPropertyName:

Parameter Value Description

SRC

Source connection for the current task.

DEST

Target connection for the current task.

WORKREP

Work Repository connection.


Examples

Gets the source connection and creates a statement for this connection.

java.sql.Connection sourceConnection = odiRef.getJDBCConnection("SRC");
java.sql.Statement s = sourceConnection.createStatement();

A.2.28 getJDBCConnectionFromLSchema() Method

Use to return a JDBC connection for a given logical schema.

Usage

public java.lang.String getJDBCConnectionFromLSchema(
java.lang.String pLogicalSchemaName,
java.lang.String pContextName)

public java.lang.String getJDBCConnectionFromLSchema(
java.lang.String pLogicalSchemaName)

Description

Returns a JDBC connection for a given logical schema. The pLogicalSchemaName identifies the logical schema.

The first syntax resolves the logical schema in the context provided in the pContextName parameter.

The second syntax resolves the logical schema in the current context.

Parameters

Parameter Type Description

pLogicalSchemaName

String

Name of the forced logical schema of the object.

pContextName

String

Forced context of the object


Note:

  • This method does not return a string, but a JDBC connection object. This object may be used in your Java code within the task.

  • It is recommended to close the JDBC connections acquired using this method once you are done with the connection. This will improve the concurrency if your KM is used in ODI mappings.

A.2.29 getJoin() Method

Use to return the entire WHERE clause section generated for the joins of a mapping.

Usage

public java.lang.String getJoin(java.lang.Int pDSIndex)

Description

Retrieves the SQL join string (on the source during the loading, on the staging area during the integration) for a given dataset of a mapping.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.


Examples

insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

A.2.30 getJoinList() Method

Use to return properties for each join of a mapping. The properties are organized according to a string pattern.

Usage

public java.lang.String getJoinList( 
java.lang.Int pDSIndex,
java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

Alternative syntax:

public java.lang.String getJoinList(
java.lang.Int pDSIndex,
java.lang.String pPattern,
java.lang.String pSeparator)

Description

Returns a list of the occurrences of the SQL joins in a given dataset of a mapping for the WHERE clause.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The pPattern parameter is interpreted and then repeated for each element in the list and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends up with pEnd.

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of authorized attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example My string [COL_NAME] is a attribute»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to generate.


Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Parameter Value Description

ID

Internal identifier of the join

EXPRESSION

Text of the join expression


Examples

insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoinList("and ","([EXPRESSION])"," and ","")%>
<%=odiRef.getFilterList("and ","([EXPRESSION])"," and ","")%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

Explanation: the getJoinList function will be used to generate join expressions to put in the WHERE part of the SELECT statement that must start with "and" and that repeats a pattern (the expression of each join) separated by " and " for each join. Thus:

  • The first parameter "and" of the function indicates that we want to start the string with "and"

  • The second parameter "([EXPRESSION])" indicates that we want to repeat this pattern for each join. The keyword [EXPRESSION] references a valid keyword of the table Pattern Attributes List

  • The third parameter " and " indicates that we want to separate each interpreted occurrence of the pattern with " and " (note the spaces before and after "and")

  • The fourth parameter "" of the function indicates that we want to end the string with no specific character

A.2.31 getJrnFilter() Method

Use to return the journalizing filter of a mapping.

Usage

public java.lang.String getJrnFilter(java.lang.Int pDSIndex)

Description

Returns the SQL Journalizing filter for a given dataset in the current mapping. If the journalized table in the source, this method can be used during the loading phase. If the journalized table in the staging area, this method can be used while integrating.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.


Examples

<%=odiRef.getJrnFilter()%>

A.2.32 getJrnInfo() Method

Use to return journalizing information about a datastore.

Usage

public java.lang.String getJrnInfo(java.lang.String pPropertyName)

Description

Returns information about a datastore's journalizing for a JKM while journalizing a model/datastore, or for a LKM/IKM in a mapping.

Parameters

Parameter Type Description

pPropertyName

String

String containing the name of the requested property.


The following table lists the different values possible for pPropertyName:

Parameter Value Description

FULL_TABLE_NAME

Full name of the journalized datastore.

JRN_FULL_NAME

Full name of the journal datastore.

JRN_FULL_VIEW

Full name of the view linked to the journalized datastore.

JRN_FULL_DATA_VIEW

Full name of the data view linked to the journalized datastore.

JRN_FULL_TRIGGER

Full name of the trigger linked to the journalized datastore.

JRN_FULL_ITRIGGER

Full name of the Insert trigger linked to the journalized datastore.

JRN_FULL_UTRIGGER

Full name of the Update trigger linked to the journalized datastore.

JRN_FULL_DTRIGGER

Full name of the Delete trigger linked to the journalized datastore.

SNP_JRN_SUBSCRIBER

Name of the subscriber table in the work schema.

JRN_NAME

Name of the journalized datastore.

JRN_VIEW

Name of the view linked to the journalized datastore.

JRN_DATA_VIEW

Name of the data view linked to the journalized datastore.

JRN_TRIGGER

Name of the trigger linked to the journalized datastore.

JRN_ITRIGGER

Name of the Insert trigger linked to the journalized datastore.

JRN_UTRIGGER

Name of the Update trigger linked to the journalized datastore.

JRN_DTRIGGER

Name of the Delete trigger linked to the journalized datastore.

JRN_SUBSCRIBER

Name of the subscriber.

JRN_COD_MOD

Code of the journalized data model.

JRN_METHOD

Journalizing Mode (consistent or simple).

CDC_SET_TABLE

Full name of the table containing list of CDC sets.

CDC_TABLE_TABLE

Full name of the table containing the list of tables journalized through CDC sets.

CDC_SUBS_TABLE

Full name of the table containing the list of subscribers to CDC sets.

CDC_OBJECTS_TABLE

Full name of the table containing the journalizing parameters and objects.


Examples

The table being journalized is <%=odiRef.getJrnInfo("FULL_TABLE_NAME")%>

A.2.33 getLoadPlanInstance() Method

Use to return the Load Plan instance information.

Usage

public java.lang.String getLoadPlanInstance (java.lang.String pPropertyName)

Description

This method returns the current execution instance information for a Load Plan.

Parameters

Parameter Type Description

pPropertyName

String

String that contains the name of the requested property.


The following table lists the possible values for pPropertyName:

Parameter Value Description

BATCH_ID

Load Plan instance identifier (also Instance ID). Every time a Load Plan is started, a new Load Plan instance with a unique identifier is created

RESTART_ATTEMPTS

Number of execution attempts of this Load Plan instance (also Run #). It starts at 1 when the Load Plan instance is first executed, and is incremented each time the Load Plan instance is restarted.

LOAD_PLAN_NAME

Name of the Load Plan

START_DATE

Starting date and time of the current Load Plan instance run


Examples

The current Load Plan <%=odiRef.getLoadPlanInstance("LOAD_PLAN_NAME")%> started execution at <%=odiRef.getLoadPlanInstance("START_DATE")%>

A.2.34 getModel() Method

Use to return information about a model.

Usage

public java.lang.String getModel(java.lang.String pPropertyName)

Description

This method returns information on the current data model during the processing of a personalized reverse engineering. The list of available data is described in the pPropertyName values table.

Note:

This method may be used on the source connection (data server being reverse-engineered) as well as on the target connection (repository). On the target connection, only the properties independent from the context can be specified (for example, the schema and catalog names cannot be used).

Parameters

Parameter Type Description

pPropertyName

String

String that contains the name of the requested property.


The following table lists the possible values for pPropertyName:

Parameter Value Description

ID

Internal identifier of the current model

MOD_NAME

Name of the current model

LSCHEMA_NAME

Name of the logical schema of the current model

MOD_TEXT

Description of the current model

REV_TYPE

Reverse engineering type: S for standard reverse, C for customize

REV_UPDATE

Update flag of the model

REV_INSERT

Insert flag for the model

REV_OBJ_PATT

Mask for the objects to reverse.

REV_OBJ_TYPE

List of object types to reverse-engineer for this model. This is a semicolon separated list of object types codes. Valid codes are:

  • T: Table

  • V: View

  • Q: Queue

  • SY: System table

  • AT: Table alias

  • SY: Synonym

TECH_INT_NAME

Internal name of the technology of the current model.

LAGENT_NAME

Name of the logical execution agent for the reverse engineering.

REV_CONTEXT

Execution context of the reverse

REV_ALIAS_LTRIM

Characters to be suppressed for the alias generation

CKM

Check Knowledge Module

RKM

Reverse-engineering Knowledge Module

SCHEMA_NAME

Physical Name of the data schema in the current reverse context

WSCHEMA_NAME

Physical Name of the work schema in the current reverse context

CATALOG_NAME

Physical Name of the data catalog in the current reverse context

WCATALOG_NAME

Physical Name of the work catalog in the current reverse context

<flexfield code>

Value of the flexfield for the current model.


Examples

Retrieve the list of tables that are part of the mask of objects to reverse:

select TABLE_NAME,
         RES_NAME,
     replace(TABLE_NAME, '<%=odiRef.getModel("REV_ALIAS_LTRIM")%>' , '')
          ALIAS,
          TABLE_DESC
from MY_TABLES
where
TABLE_NAME like '<%=odiRef.getModel("REV_OBJ_PATT")%>'

A.2.35 getNbInsert(), getNbUpdate(), getNbDelete(), getNbErrors() and getNbRows() Methods

Use to get the number of inserted, updated, deleted or erroneous rows for the current task.

Usage

public java.lang.Long getNbInsert()

public java.lang.Long getNbUpdate()

public java.lang.Long getNbDelete()

public java.lang.Long getNbErrors()

public java.lang.Long getNbRows()

Description

These methods get for the current task the values for:

  • the number of rows inserted (getNbInsert)

  • the number of rows updated (getNbUpdate)

  • the number of rows deleted (getNbDelete)

  • the number of rows in error (getNbErrors)

  • total number of rows handled during this task (getNbRows)

These numbers can be set independently from the real number of lines processed using the setNbInsert(), setNbUpdate(), setNbDelete(), setNbErrors() and setNbRows() Methods.

Examples

In the Jython example below, we set the number of inserted rows to the constant value of 50, and copy this value in the number of errors.

InsertNumber=50

odiRef.setNbInsert(InsertNumber)

odiRef.setNbErrors(odiRef.getNbInsert())

A.2.36 getNewColComment() Method

Use to return the new comment for a specific attribute handled by an action.

Usage

public java.lang.String getNewColComment()

Description

In an action, this method returns the new comment for the attribute being handled by the DDL command, in a Modify column comment action.

A.2.37 getNewTableComment() Method

Use to return the new comment for a specific table handled by an action.

Usage

public java.lang.String getNewTableComment()

Description

In an action, this method returns the new comment for the table being handled by the DDL command, in a Modify table comment action.

A.2.38 getNotNullCol() Method

Use to return information about a attribute that is checked for not null.

Usage

public java.lang.String getNotNullCol(java.lang.String pPropertyName)

Description

This method returns information relative to a not null attribute of a datastore during a check procedure. It is accessible from a Check Knowledge Module if the current task is tagged as "mandatory".

Parameters

Parameter Type Description

Parameter

Type

Description

pPropertyName

String

String that contains the name of the requested property.


The following table lists the different possible values for pPropertyName:

Parameter Value Description

ID

Internal identifier for the current attribute.

COL_NAME

Name of the Not null attribute.

MESS

Standard error message.

<flexfield code>

Flexfield value for the current not null attribute.


Examples

insert into...
select *
from ...
<%=odiRef.getNotNullCol("COL_NAME")%> is null

A.2.39 getObjectName() Method

Use to return the fully qualified named of an object.

Usage

public java.lang.String getObjectName(
java.lang.String pMode,
java.lang.String pObjectName,
java.lang.String pLocation)

public java.lang.String getObjectName(
java.lang.String pMode,
java.lang.String pObjectName,
java.lang.String pLogicalSchemaName,
java.lang.String pLocation)

public java.lang.String getObjectName(
java.lang.String pMode,
java.lang.String pObjectName,
java.lang.String pLogicalSchemaName,
java.lang.String pContextName,
java.lang.String pLocation)

public java.lang.String getObjectName(
java.lang.String pObjectName,
java.lang.String pLocation)

public java.lang.String getObjectName(
java.lang.String pObjectName)

public java.lang.String getObjectName(java.lang.String pMode,java.lang.String pObjectName,java.lang.String pLogicalSchemaName,java.lang.String pContextName,java.lang.String pLocation,
java.lang.String pPartitionType,
java.lang.String pPartitionName)

Description

Returns the fully qualified name of a physical object, including its catalog and schema. The pMode parameter indicates the substitution mask to use.

Note:

The getObjectName methods truncates automatically object names to the maximum object length allowed for the technology. In versions before ODI 11g, object names were not trucated. To prevent object names truncation and reproduce the 10g behavior, add in the properties tab of the data server a property called OBJECT_NAME_LENGTH_CHECK_OLD and set its value to true.

The first syntax builds the object name according to the current logical schema in the current context.

The second syntax builds the name of the object according to the logical schema indicated in the pLogicalSchemaName parameter in the current context.

The third syntax builds the name from the logical schema and the context indicated in the pLogicalSchemaName and pContextName parameters.

The fourth syntax builds the object name according to the current logical schema in the current context, with the local object mask (pMode = "L").

The fifth syntax is equivalent to the fourth with pLocation = "D".

The last syntax is equivalent to the third syntax but qualifies the object name specifically on a given partition, using the pPartitionType and pPartitionName parameters.

Parameters

Parameter Type Description

pMode

String

"L" use the local object mask to build the complete path of the object. "R" use the remote object mask to build the complete path of the object.

Note: When using the remote object mask, getObjectName always resolved the object name using the default physical schema of the remote server.

pObjectName

String

Every string that represents a valid resource name (table or file). This object name may be prefixed by a prefix code that will be replaced at run-time by the appropriate temporary object prefix defined for the physical schema.

pLogicalSchemaName

String

Name of the forced logical schema of the object.

pContextName

String

Forced context of the object

pLocation

String

The valid values are:

  • W: Returns the complete name of the object in the physical catalog and the "work" physical schema that corresponds to the specified tuple (context, logical schema)

  • D: Returns the complete name of the object in the physical catalog and the data physical schema that corresponds to the specified tuple (context, logical schema)

pPartitionType

String

Specify whether to qualify the object name for a specific partition or sub-partition. The valid values are:

  • P: Qualify object for the partition provided in pPartitionName

  • S: Qualify object for the sub-partition provided in pPartitionName

pPartitionName

String

Name of the partition of sub-partition to qualify the object name.


Prefixes

It is possible to prefix the resource name specified in the pObjectName parameter by a prefix code to generate a Oracle Data Integrator temporary object name (Error or Integration table, journalizing trigger, etc.).

The list of prefixes are given in the table below.

Prefix Description

Prefix

Description

%INT_PRF

Prefix for integration tables (default value is "I$_").

%COL_PRF

Prefix for Loading tables (default value is "C$_").

%ERR_PRF

Prefix for error tables (default value is "E$_").

%JRN_PRF_TAB

Prefix for journalizing tables (default value is "J$_").

%INT_PRF_VIE

Prefix for journalizing view (default value is "JV$_").

%JRN_PRF_TRG

Prefix for journalizing triggers (default value is "T$_").

%IDX_PRF

Prefix for temporary indexes (default value is "IX$_").


Note:

Temporary objects are usually created in the work physical schema. Therefore, pLocation should be set to "W" when using a prefix to create or access a temporary object.

Examples

You have defined a physical schema as shown below.

Data catalog:

db_odi

Data schema:

dbo

Work catalog:

tempdb

Work schema:

temp_owner


You have associated this physical schema to the logical schema MSSQL_ODI in the context CTX_DEV.

A Call To Returns

<%=odiRef.getObjectName("L", "EMP", "MSSQL_ODI", "CTX_DEV", "W")%>

tempdb.temp_owner.EMP

<%=odiRef.getObjectName("L", "EMP", "MSSQL_ODI", "CTX_DEV", "D")%>

db_odi.dbo.EMP

<%=odiRef.getObjectName("R", "%ERR_PRFEMP", "MSSQL_ODI", "CTX_DEV", "W")%>

MyServer.tempdb.temp_owner.E$_EMP

<%=odiRef.getObjectName("R", "EMP", "MSSQL_ODI", "CTX_DEV", "D")%>

MyServer.db_odi.dbo.EMP


A.2.40 getObjectNameDefaultPSchema() Method

Use to return the fully qualified named of an object in the default physical schema for the data server.

Usage

public java.lang.String getObjectNameDefaultPSchema(
java.lang.String pMode,
java.lang.String pObjectName,
java.lang.String pLocation)

public java.lang.String getObjectNameDefaultPSchema(
java.lang.String pMode,
java.lang.String pObjectName,
java.lang.String pLogicalSchemaName,
java.lang.String pLocation)

public java.lang.String getObjectNameDefaultPSchema(
java.lang.String pMode,
java.lang.String pObjectName,
java.lang.String pLogicalSchemaName,
java.lang.String pContextName,
java.lang.String pLocation)

public java.lang.String getObjectNameDefaultPSchema(
java.lang.String pObjectName,
java.lang.String pLocation)

public java.lang.String getObjectNameDefaultPSchema(
java.lang.String pObjectName)

public java.lang.String getObjectNameDefaultPSchema(java.lang.String pMode,java.lang.String pObjectName,java.lang.String pLogicalSchemaName,java.lang.String pContextName,java.lang.String pLocation,
java.lang.String pPartitionType,
java.lang.String pPartitionName)

Description

The method is similar to the getObjectName method. However, the object name is computed for the default physical schema of the data server to which the physical schema is attached. In getObjectName, the object name is computed for the physical schema itself.

For more information, see "getObjectName() Method".

A.2.41 getOption() Method

Use to return the value of a KM or procedure option.

Usage

public java.lang.String getOption(java.lang.String pOptionName)
public java.lang.String getUserExit(java.lang.String pOptionName)

Description

Returns the value of a KM or procedure option.

The getUserExit syntax is deprecated and is only kept for compatibility reasons.

Parameters

Parameter Type Description

pOptionName

String

String that contains the name of the requested option.


Examples

The value of my MY_OPTION_1 option is <%=odiRef.getOption("MY_OPTION_1")%>

A.2.42 getPackage() Method

Use to return information about the current package.

Usage

public java.lang.String getPackage(java.lang.String pPropertyName)

Description

This method returns information about the current package. The list of available properties is described in the pPropertyName values table.

Parameters

Parameters Type Description

pPropertyName

String

String that contains the name of the requested property.


The following table lists the different possible values for pPropertyName:

Parameter Value Description

I_PACKAGE

Internal ID of the package

PACKAGE_NAME

Name of the package

<flexfield code>

Value of the flexfield for this package.


Examples

Package <%=odiRef.getPackage("PACKAGE_NAME")%> is running.

A.2.43 getParentLoadPlanStepInstance() Method

Use to return the parent Load Plan step instance of this session.

Usage

public java.lang.String getParentLoadPlanStepInstance(java.lang.String pPropertyName)

Description

This method returns the step execution instance information of the parent of the current step for a Load Plan instance. It will return an empty string if the parent step is the root step.

Parameters

Parameter Type Description

pPropertyName

String

String that contains the name of the requested property.


The following table lists the different possible values for pPropertyName.

Parameter Value Description

BATCH_ID

Load Plan instance identifier (also Instance ID). Every time a Load Plan is started, a new Load Plan instance with a unique identifier is created.

RESTART_ATTEMPTS

Number of execution attempts of this Load Plan parent step instance. It starts at 1 when the Load Plan parent step instance is first started, and is incremented each time the Load Plan parent step instance is restarted.

STEP_NAME

Name of the Load Plan parent step

STEP_TYPE

Type of the Load Plan parent step

START_DATE

Starting date and time of the parent step instance of the current step of the current Load Plan instance run.


Examples

Step <%=odiRef.getParentLoadPlanStepInstance("STEP_NAME")%> has been executed <%=odiRef.getParentLoadPlanStepInstance("RESTART_ATTEMPTS")%> times

A.2.44 getPK() Method

Use to return information about a primary key.

Usage

public java.lang.String getPK(java.lang.String pPropertyName)

Description

This method returns information relative to the primary key of a datastore during a check procedure.

In an action, this method returns information related to the primary key currently handled by the DDL command.

Parameters

Parameter Type Description

pPropertyName

String

String that contains the name of the requested property.


The following table lists the different possible values for pPropertyName.

Parameter Value Description

ID

Internal number of the PK constraint.

KEY_NAME

Name of the primary key

MESS

Error message relative to the primary key constraint.

FULL_NAME

Full name of the PK generated with the local object mask.

<flexfield code>

Flexfield value for the primary key.


Examples

The primary key of my table is called: <%=odiRef.getPK("KEY_NAME")%>

A.2.45 getPKColList() Method

Use to return information about the attributes of a primary key.

Usage

public java.lang.String getPKColList( java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

Description

Returns a list of attributes and expressions for the primary key being checked.

The pPattern parameter is interpreted and then repeated for each element of the list. It is separated from its predecessor by the pSeparator parameter. The generated string starts with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains an element for each attribute of the current primary key. It is accessible from a Check Knowledge Module if the current task is tagged as an "primary key".

In an action, this method returns the list of the attributes of the primary key handled by the DDL command, ordered by their position in the key.

Parameters

Parameter Type Description

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of attributes that can be used in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern sequence is replaced with its value. The attributes must be between brackets. ([ and ])

Example «My string [COL_NAME] is a attribute»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.


Pattern Attributes List

The following table lists the different values of the parameters as well as their associated description.

Parameter Value Description

I_COL

Attribute internal identifier

COL_NAME

Name of the key attribute

COL_HEADING

Header of the key attribute

COL_DESC

Attribute description

POS

Position of the attribute

LONGC

Length (Precision) of the attribute

SCALE

Scale of the attribute

FILE_POS

Beginning position of the attribute (fixed file)

BYTES

Number of physical bytes of the attribute

FILE_END_POS

End of the attribute (FILE_POS + BYTES)

IND_WRITE

Write right flag of the attribute

COL_MANDATORY

Mandatory character of the attribute. Valid values are:

  • 0: null authorized

  • 1: not null

CHECK_FLOW

Flow control flag for of the attribute. Valid values are:

  • 0: do not check

  • 1: check

CHECK_STAT

Static control flag of the attribute. Valid values are:

  • 0: do not check

  • 1: check

COL_FORMAT

Logical format of the attribute

COL_DEC_SEP

Decimal symbol for the attribute

REC_CODE_LIST

List of the record codes retained for the attribute

COL_NULL_IF_ERR

Processing flag for the attribute. Valid values are:

  • 0: Reject

  • 1: Set active trace to null

  • 2: Set inactive trace to null

DEF_VALUE

Default value for the attribute

EXPRESSION

Not used

CX_COL_NAME

Not used

ALIAS_SEP

Grouping symbol used for the alias (from the technology)

SOURCE_DT

Code of the attribute's datatype.

SOURCE_CRE_DT

Create table syntax for the attribute's datatype.

SOURCE_WRI_DT

Create table syntax for the attribute's writable datatype.

DEST_DT

Code of the attribute's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the attribute's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the attribute's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this attribute in the data model.

<flexfield code>

Flexfield value for the current attribute.


Examples

If the CUSTOMER table has an primary key PK_CUSTOMER (CUST_ID, CUST_NAME) and you want to generate the following code:

create table T_PK_CUSTOMER (CUST_ID numeric(10) not null, CUST_NAME
varchar(50) not null)

You can use the following code:

create table T_<%=odiRef.getPK("KEY_NAME")%>
<%=odiRef.getPKColList("(", "[COL_NAME] [DEST_CRE_DT] not null", ", ", ")")%>

Explanation: the getPKColList function will be used to generate the (CUST_ID numeric(10) not null, CUST_NAME varchar(50) not null) part, which starts and stops with a parenthesis and repeats the pattern (attribute, a data type, and not null) separated by commas for each attribute of the primary key. Thus

  • the first parameter "(" of the function indicates that we want to start the string with the string "("

  • the second parameter "[COL_NAME] [DEST_CRE_DT] not null" indicates that we want to repeat this pattern for each attribute of the primary key. The keywords [COL_NAME] and [DEST_CRE_DT] reference valid keywords of the Pattern Attributes List table

  • the third parameter ", " indicates that we want to separate interpreted occurrences of the pattern with the string ", "

  • the forth parameter ")" of the function indicates that we want to end the string with the string ")"

A.2.46 getPop() Method

Use to return information about a mapping.

Usage

public java.lang.String getPop(java.lang.String pPropertyName)

Description

This method returns information about the current mapping. The list of available information is described in the pPropertyName values table.

Parameters

Parameter Type Description

pPropertyName

String

String that contains the name of the requested property.


The following table lists the different possible values for pPropertyName:

Parameter Value Description

I_POP

Internal number of the mapping.

FOLDER

Name of the folder of the mapping.

POP_NAME

Name of the mapping.

IND_WORK_TARG

Position flag of the staging area.

LSCHEMA_NAME

Name of the logical schema which is the staging area of the mapping.

DESCRIPTION

Description of the mapping.

WSTAGE

Flag indicating the nature of the target datastore:

  • E - target datastore is an existing table (not a temporary table).

  • N - target datastore is a temporary table in the data schema.

  • W - target datastore is a temporary table in the work schema.

TABLE_NAME

Name of the target table.

KEY_NAME

Name of the update key.

DISTINCT_ROWS

Flag for doubles suppression.

OPT_CTX

Name of the optimization context of the mapping.

TARG_CTX

Name of the execution context of the mapping.

MAX_ERR

Maximum number of accepted errors.

MAX_ERR_PRCT

Error indicator in percentage.

IKM

Name of the Integration Knowledge Module used in this mapping.

LKM

Name of the Loading Knowledge Module specified to load data from the staging area to the target if a single-technology IKM is selected for the staging area.

CKM

Name of the Check Knowledge Module used in this mapping.

HAS_JRN

Returns 1 if there is a journalized table in source of the mapping, 0 otherwise.

PARTITION_NAME

Name of the partition or sub-partition selected for the target datastore. If no partition is selected, returns an empty string.

PARTITION_TYPE

Type of the partition or sub-partition selected for the target datastore. If no partition is selected, returns an empty string.

  • P: Partition

  • S: Sub-partition

<flexfield code>

Flexfield value for the mapping.


Examples

The current mapping is: <%=odiRef.getPop("POP_NAME")%> and runs on the logical schema: <%=odiRef.getInfo("L_SCHEMA_NAME")%>

A.2.47 getPrevStepLog() Method

Use to return information about the previous step executed in the package.

Usage

public java.lang.String getPrevStepLog(java.lang.String pPropertyName)

Description

Returns information about the most recently executed step in a package. The information requested is specified through the pPropertyName parameter. If there is no previous step (for example, if the getPrevStepLog step is executed from outside a package), the exception "No previous step" is raised.

Parameters

Parameter Type Description

pPropertyName

String

String that contains the name of the requested property about the previous step. See the list of valid properties below.


The following table lists the different possible values for pPropertyName:

Parameter Value Description

SESS_NO

The number of the session.

NNO

The number of the step within a package. The first step executed is 0.

STEP_NAME

The name of the step.

STEP_TYPE

A code indicating the type of step. The following values may be returned:

  • F: Mapping

  • VD: Variable declaration

  • VS: Set/Increment variable

  • VE: Evaluate variable

  • V: Refresh variable

  • T: Procedure

  • OE: OS command

  • SE: ODI Tool

  • RM: Reverse-engineer model

  • CM: Check model

  • CS: Check sub-model

  • CD: Check datastore

  • JM: Journalize model

  • JD: Journalize datastore

CONTEXT_NAME

The name of the context in which the step was executed.

MAX_ERR

The maximum number or percentage of errors tolerated.

MAX_ERR_PRCT

Returns 1 if the maximum number of errors is expressed as a percentage, 0 otherwise.

RUN_COUNT

The number of times this step has been executed.

BEGIN

The date and time that the step began.

END

The date and time that the step terminated.

DURATION

Time the step took to execute in seconds.

STATUS

Returns the one-letter code indicating the status with which the previous step terminated. The state R (Running) is never returned.

  • D: Done (success)

  • E: Error

  • Q: Queued

  • W: Waiting

  • M: Warning

RC

Return code. 0 indicates no error.

MESSAGE

Error message returned by previous step, if any. Blank string if no error.

INSERT_COUNT

Number of rows inserted by the step.

DELETE_COUNT

Number of rows deleted by the step.

UPDATE_COUNT

Number of rows updated by the step.

ERROR_COUNT

Number of erroneous rows detected by the step, for quality control steps.


Examples

Previous step '<%=odiRef.getPrevStepLog("STEP_NAME")%>' executed
in '<%=odiRef.getPrevStepLog("DURATION")%>' seconds.

A.2.48 getQuotedString() Method

Use to return a quoted string.

Usage

public java.lang.String getQuotedString(java.lang.String pString)

Description

This method returns a string surrounded with quotes. It preserves quotes and escape characters such as \n, \t that may appear in the string.

This method is useful to protect a string passed as a value in Java, Groovy or Jython code.

Parameters

Parameter Type Description

Parameter

Type

Description

pString

String

String that to be protected with quotes.


Examples

In the following Java code, the getQuotedString method is used to generate a valid string value.

String condSqlOK = <%=odiRef.getQuotedString(odiRef.getCK("MESS"))%>;
String condSqlKO = <%=odiRef.getCK("MESS")%>;

If the message for the condition is "Error:\n Zero is not a valid value", the generated code is as shown below. Without the getQuotedString, the code is incorrect, as the \n is not preserved and becomes a carriage return.

String condSqlOK = "Error:\n Zero is not a valid value";
String condSqlKO = "Error:
Zero is not a valid value";

A.2.49 getSchemaName() Method

Use to return a schema name from the topology.

Usage

public java.lang.String getSchemaName(
java.lang.String pLogicalSchemaName,
java.lang.String pLocation)

public java.lang.String getSchemaName(
java.lang.String pLogicalSchemaName,
java.lang.String pContextCode,
java.lang.String pLocation)

public java.lang.String getSchemaName( java.lang.String pLocation)


public java.lang.String getSchemaName()

Description

Retrieves the physical name of a data schema or work schema from its logical schema.

If the first syntax is used, the returned schema corresponds to the current context.

If the second syntax is used, the returned schema corresponds to context specified in the pContextCode parameter.

The third syntax returns the name of the data schema (D) or work schema (W) for the current logical schema in the current context.

The fourth syntax returns the name of the data schema (D) for the current logical schema in the current context.

Parameters

Parameter Type Description

pLogicalSchemaName

String

Name of the logical schema of the schema

pContextCode

String

Forced context of the schema

pLocation

String

The valid values are:

  • D: Returns the data schema of the physical schema that corresponds to the tuple (context, logical schema)

  • W: Returns the work schema of the physical schema that corresponds to the tuple (context, logical schema)


Examples

If you have defined the physical schema: Pluton.db_odi.dbo

Data catalog:

db_odi

Data schema:

dbo

Work catalog:

tempdb

Work schema:

temp_owner


and you have associated this physical schema to the logical schema: MSSQL_ODI in the context CTX_DEV

The Call To Returns

<%=odiRef.getSchemaName("MSSQL_ODI", "CTX_DEV", "W")%>

temp_owner

<%=odiRef.getSchemaName("MSSQL_ODI", "CTX_DEV", "D")%>

dbo


A.2.50 getSchemaNameDefaultPSchema() Method

Use to return a catalog name for the default physical schema from the topology.

Usage

public java.lang.String getSchemaNameDefaultPSchema(
java.lang.String pLogicalSchemaName,
java.lang.String pLocation)

public java.lang.String getSchemaNameDefaultPSchema(
java.lang.String pLogicalSchemaName,
java.lang.String pContextCode,
java.lang.String pLocation)

public java.lang.String getSchemaNameDefaultPSchema( 
java.lang.String pLocation)

public java.lang.String getSchemaNameDefaultPSchema(

Description

Allows you to retrieve the name of the default physical data schema or work schema for the data server to which is associated the physical schema corresponding to the tuple (logical schema, context). If no context is specified, the current context is used. If no logical schema name is specified, then the current logical schema is used. If no pLocation is specified, then the data schema is returned.

Parameters

Parameter Type Description

pLogicalSchemaName

String

Name of the logical schema

pContextCode

String

Code of the enforced context of the schema

pLocation

String

The valid values are:

  • D: Returns the data schema of the physical schema corresponding to the tuple (context, logical schema)

  • W: Returns the work schema of the default physical schema associate to the data server to which the physical schema corresponding to the tuple (context, logical schema) is also attached.


Examples

If you have defined the physical schemas: Pluton.db_odi.dbo

Data catalog:

db_odi

Data schema:

dbo

Work catalog:

tempdb

Work schema:

temp_odi

Default Schema

Yes


that you have associated with this physical schema: MSSQL_ODI in the context CTX_DEV, and Pluton.db_doc.doc

Data catalog:

db_doc

Data schema:

doc

Work catalog:

tempdb

Work schema:

temp_doc

Default Schema

No


that you have associated with this physical schema: MSSQL_DOC in the context CTX_DEV

The Call To Returns

<%=odiRef.getSchemaNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "W")%>

temp_odi

<%=odiRef.getSchemaNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "D")%>

dbo


A.2.51 getSession() Method

Use to return information about the current session.

Usage

public java.lang.String getSession(java.lang.String pPropertyName)

Description

This method returns information about the current session. The list of available properties is described in the pPropertyName values table.

Parameters

Parameters Type Description

pPropertyName

String

String that contains the name of the requested property.


The following table lists the different possible values for pPropertyName:

Parameter Value Description

SESS_NO

Internal number of the session

SESS_NAME

Name of the session

SCEN_NAME

Name of the scenario

SCEN_VERSION

Current scenario version

CONTEXT_NAME

Name of the execution context

CONTEXT_CODE

Code of the execution context

AGENT_NAME

Name of the physical agent in charge of the execution

SESS_BEG

Date and time of the beginning of the session

USER_NAME

ODI User running the session.


Examples

The current session is: <%=odiRef.getSession("SESS_NAME")%>

A.2.52 getSessionVarList() Method

Reserved for future use.

Usage

public java.lang.String getSessionVarList( java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd,
java.lang.String pSelector)

Description

Reserved for future use.

Parameters

Reserved for future use.

Examples

Reserved for future use.

A.2.53 getSrcColList() Method

Use to return properties for each attribute from a filtered list of source attributes involved in a loading or integration phase. The properties are organized according to a string pattern.

Usage

public java.lang.String getSrcColList(
java.lang.Int pDSIndex,
java.lang.String pStart,
java.lang.String pUnMappedPattern,
java.lang.String pMappedPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

Description

This method available in LKMs and IKMs, returns properties for a list of attributes in a given dataset. This list includes all the attributes of the sources processed by the LKM (from the source) or the IKM (from the staging area). The list is sorted by the attribute position in the source tables.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The properties displayed depend on whether the attribute is mapped or not. If the attribute is mapped, the properties returned are defined in the pMappedPattern pattern. If the attribute is not mapped, the properties returned are defined in the pUnMappedPattern pattern.

The attributes usable in a pattern are detailed in "Pattern Attributes List". Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ]). Example: "My string [COL_NAME] is a attribute".

The pMappedPattern or pUnMappedPattern parameter is interpreted and then repeated for each element of the list. Patterns are separated with pSeparator. The generated string begins with pStart and ends with pEnd.

If there is a journalized datastore in the source of the mapping, the three journalizing pseudo attributes JRN_FLG, JRN_DATE and JRN_SUBSCRIBER are added as attributes of the journalized source datastore.

Parameters

Parameter Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This sequence marks the beginning of the string to generate.

pUnMappedPattern

String

The pattern is repeated for each occurrence in the list if the attribute is not mapped.

pMappedPattern

String

The pattern is repeated for each occurrence in the list, if the attribute is mapped.

pSeparator

String

This parameter separates patterns.

pEnd

String

This sequence marks the end of the string to generate.


Pattern Attributes List

The following table lists different parameters values as well as their associated description.

Parameter Value Description

I_COL

Internal identifier of the attribute

COL_NAME

Name of the attribute

ALIAS_NAME

Name of the attribute. Unlike COL_NAME, the attribute name without the optional technology delimiters is returned. These delimiters appear when the attribute name contains for instance spaces.

COL_HEADING

Header of the attribute

COL_DESC

Description of the attribute

POS

Position of the attribute

LONGC

Attribute length (Precision)

SCALE

Scale of the attribute

FILE_POS

Beginning (index) of the attribute

BYTES

Number of physical bytes in the attribute

FILE_END_POS

End of the attribute (FILE_POS + BYTES)

IND_WRITE

Write right flag of the attribute

COL_MANDATORY

Mandatory character of the attribute. Valid values are: (0: null authorized, 1: not null)

  • 0: null authorized

  • 1: not null

CHECK_FLOW

Flow control flag of the attribute. Valid values are: (0: do not check, 1: check)

  • 0: do not check

  • 1: check

CHECK_STAT

Static control flag of the attribute. Valid values are: (0: do not check, 1: check)

  • 0: do not check

  • 1: check

COL_FORMAT

Logical format of the attribute

COL_DEC_SEP

Decimal symbol of the attribute

REC_CODE_LIST

List of the record codes retained in the attribute

COL_NULL_IF_ERR

Processing flag of the attribute. Valid values are:

  • 0: Reject

  • 1: Set to null active trace

  • 2: Set to null inactive trace

DEF_VALUE

Default value of the attribute

EXPRESSION

Text of the expression (as typed in the mapping field) executed on the source (LKM) or the staging area (IKM). If the attribute is not mapped, this parameter returns an empty string.

CX_COL_NAME

Not supported.

ALIAS_SEP

Separator used for the alias (from the technology)

SOURCE_DT

Code of the attribute's datatype.

SOURCE_CRE_DT

Create table syntax for the attribute's datatype.

SOURCE_WRI_DT

Create table syntax for the attribute's writable datatype.

DEST_DT

Code of the attribute's datatype converted to a datatype on the target (IKM) or staging area (LKM) technology.

DEST_CRE_DT

Create table syntax for the attribute's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the attribute's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this attribute in the data model.

MANDATORY_CLAUSE

Returns NOT NULL if the attribute is mandatory. Otherwise, returns the null keyword for the technology.

DEFAULT_CLAUSE

Returns DEFAULT <default value> if any default value exists. Otherwise, returns and empty string.

<flexfield code>

Flexfield value for the current attribute.

POP_ALIAS

Alias of the datastore used in the mapping.


Examples

To create a table similar to a source file:

create table <%=odiRef.getTable("L","COLL_NAME", "D")%>_F
(
<%=odiRef.getSrcColList("","[COL_NAME] [DEST_CRE_DT]","[COL_NAME]
[DEST_CRE_DT]",",\n","")%>
)

A.2.54 getSrcTablesList() Method

Use to return properties for each source table of a mapping. The properties are organized according to a string pattern.

Usage

public java.lang.String getSrcTablesList( 
java.lang.Int pDSIndex,
java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

Alternative syntax:

public java.lang.String getSrcTablesList(
java.lang.Int pDSIndex,
java.lang.String pPattern,
java.lang.String pSeparator)

Description

Returns a list of source tables of a given dataset in a mapping. This method can be used to build a FROM clause in a SELECT order. However, it is advised to use the getFrom() method instead.

In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.

Note:

The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.

The pPattern pattern is interpreted and then repeated for each element of the list and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameters Type Description

pDSIndex

Int

Index identifying which of the datasets is taken into account by this command.

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of possible attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a attribute»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to generate.


Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Attribute Description

I_TABLE

Internal identifier of the current source table if available.

MODEL_NAME

Name of the model of the current source table, if available.

SUB_MODEL_NAME

Name of the sub-model of the current source table, if available

TECHNO_NAME

Name of the technology of the source datastore

LSCHEMA_NAME

Logical schema of the source table

TABLE_NAME

Logical name of the source datastore

RES_NAME

Physical access name of the resource (file name or JMS queue, physical name of the table, etc.). If there is a journalized datastore in source of the mapping, the source table is the clause is replaced by the data view linked to the journalized source datastore.

CATALOG

Catalog of the source datastore (resolved at runtime)

WORK_CATALOG

Work catalog of the source datastore

SCHEMA

Schema of the source datastore (resolved at runtime)

WORK_SCHEMA

Work schema of the source datastore

TABLE_ALIAS

Alias of the datastore as it appears in the tables list, if available

POP_TAB_ALIAS

Alias of the datastore as it appears in the current mapping, if available.

TABLE_TYPE

Type of the datastore source, if available.

DESCRIPTION

Description of the source datastore, if available.

R_COUNT

Number of records of the source datastore, if available.

FILE_FORMAT

File format, if available.

FILE_SEP_FIELD

Field separator (file)

XFILE_SEP_FIELD

Hexadecimal field separator (file)

SFILE_SEP_FIELD

Field separator string (file)

FILE_ENC_FIELD

Field beginning and ending character (file)

FILE_SEP_ROW

Record separator (file)

XFILE_SEP_ROW

Hexadecimal record separator (file)

SFILE_SEP_ROW

Record separator string (file)

FILE_FIRST_ROW

Number of header lines to ignore, if available.

FILE_DEC_SEP

Default decimal separator for the datastore, if available.

METADATA

Description in ODI format of the metadata of the current resource, if available.

OLAP_TYPE

OLAP type specified in the datastore definition

IND_JRN

Flag indicating that the datastore is including in CDC.

JRN_ORDER

Order of the datastore in the CDC set for consistent journalizing.

PARTITION_NAME

Name of the partition or sub-partition selected for the source datastore. If no partition is selected, returns an empty string.

PARTITION_TYPE

Type of the partition or sub-partition selected for the source datastore. If no partition is selected, returns an empty string.

  • P: Partition

  • S: Sub-partition

<flexfield code>

Flexfield value for the current table.


Examples

insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getSrcTablesList("", "[CATALOG].[SCHEMA].[TABLE_NAME] AS [POP_TAB_ALIAS]", ", ", "")%>
where (1=1)
<%=odiRef.getJoinList("and ","([EXPRESSION])"," and ","")%>
<%=odiRef.getFilterList("and ","([EXPRESSION])"," and ","")%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>

Explanation: the getSrcTablesList function will be used to generate the FROM clause of the SELECT STATEMENT that repeats the pattern (CATALOG.SCHEMA.TABLE_NAME as POP_TAB_ALIAS) separated by commas for each table in source.

  • The first parameter "" of the function indicates that we want do not want to start the string with any specific character.

  • The second parameter "[CATALOG].[SCHEMA].[TABLE_NAME] as [POP_TAB_ALIAS]" indicates that we want to repeat this pattern for each source table. The keywords [CATALOG], [SCHEMA], [TABLE_NAME] and [POP_TAB_ALIAS] reference valid keywords of the table Pattern Attributes List

  • The third parameter", " indicates that we want to separate each interpreted occurrence of the pattern with the string ", "

  • The fourth parameter "" of the function indicates that we want to end the string with no specific character

A.2.55 getStep() Method

Use to return information about the current step.

Usage

public java.lang.String getStep(java.lang.String pPropertyName)

Description

This method returns information about the current step. The list of available information is described in the pPropertyName values table.

Parameters

Parameter Type Description

pPropertyName

String

String that contains the name of the requested property.


The following table lists the possible values for pPropertyName:

Parameter Value Description

SESS_NO

Number of the session to which the step belongs.

NNO

Number of the step in the session

NB_RUN

Number of execution attempts

STEP_NAME

Step name

STEP_TYPE

Step type

CONTEXT_NAME

Name of the execution context

VAR_INCR

Step variable increment

VAR_OP

Operator used to compare the variable

VAR_VALUE

Forced value of the variable

OK_EXIT_CODE

Exit code in case of success

OK_EXIT

End the package in case of success

OK_NEXT_STEP

Next step in case of success.

OK_NEXT_STEP_NAME

Name of the next step in case of success

KO_RETRY

Number of retry attempts in case of failure.

KO_RETRY_INTERV

Interval between each attempt in case of failure

KO_EXIT_CODE

Exit code in case of failure.

KO_EXIT

End the package in case of failure.

KO_NEXT_STEP

Next step in case of failure.

KO_NEXT_STEP_NAME

Name of the next step in case of failure


Examples

The current step is: <%=odiRef.getStep("STEP_NAME")%>

A.2.56 getSubscriberList() Method

Use to return properties for each of the subscribers of a journalized table. The properties are organized according to a string pattern.

Usage

public java.lang.String getSubscriberList( java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

Alternative syntax:

public java.lang.String getSubscriberList( 
java.lang.String pPattern,
java.lang.String pSeparator,

Description

Returns a list of subscribers for a journalized table. The pPattern parameter is interpreted and then repeated for each element of the list, and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

In the alternative syntax, any parameters not set are set to an empty string.

Parameters

Parameter Type Description

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of the attributes usable in a pattern is detailed in the Pattern Attributes List below.

Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ])

Example «My name is [SUBSCRIBER]»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.


Pattern Attributes List

The following table lists different parameters values as well as their associated description.

Parameter Value Description

SUBSCRIBER

Name of the Subscriber


Examples

Here is list of Subscribers: <%=odiRef.getSubscriberList("\nBegin List\n", "- [SUBSCRIBER]", "\n", "\nEnd of List\n")%>

A.2.57 getSysDate() Method

Use to return the system date of the machine running the session in a given format.

Usage

public java.lang.String getSysDate()

public java.lang.String getSysDate(pDateFormat)

Description

This method returns the system date of the machine running the session.

Parameters

Parameter Type Description

pDateFormat

String

Date format used to return the system date. This pattern should follow the Java Date and Time pattern. For more information, see http://download.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html


Examples

Current year is: <%=odiRef.getSysDate("y")%>

A.2.58 getTable() Method

Use to return the fully qualified named of a table. This table may be a source or target table, or one of the temporary or infrastructure table handled by Oracle Data Integrator.

Usage

public java.lang.String getTable(
java.lang.String pMode,
java.lang.String pProperty,
java.lang.String pLocation)

public java.lang.String getTable(
java.lang.String pProperty,
java.lang.String pLocation)

public java.lang.String getTable(
java.lang.String pProperty)

Description

Allows the retrieval of the fully qualified name of temporary and permanent tables handled by Oracle Data Integrator.

Parameters

Parameters Type Description

pMode

String

"L": Uses the local object mask to build the complete path of the object. This value is used when pMode is not specified.

"R": Uses the object mask to build the complete path of the object

"A" Automatic: Defines automatically the adequate mask to use.

pProperty

String

Parameter that indicates the name of the table to be built. The list of possible values is:

  • ID: Datastore identifier.

  • TARG_NAME: Full name of the target datastore. In actions, this parameter returns the name of the current table handled by the DDL command. If partitioning is used on the target datastore of a mapping, this property automatically includes the partitioning clause in the datastore name.

  • COLL_NAME: Full name of the loading datastore.

  • INT_NAME: Full name of the integration datastore.

  • ERR_NAME: Full name of the error datastore.

  • CHECK_NAME: Name of the error summary datastore.

  • CT_NAME: Full name of the checked datastore.

  • FK_PK_TABLE_NAME: Full name of the datastore referenced by a foreign key.

  • JRN_NAME: Full name of the journalized datastore.

  • JRN_VIEW: Full name of the view linked to the journalized datastore.

  • JRN_DATA_VIEW: Full name of the data view linked to the journalized datastore.

  • JRN_TRIGGER: Full name of the trigger linked to the journalized datastore.

  • JRN_ITRIGGER: Full name of the Insert trigger linked to the journalized datastore.

  • JRN _UTRIGGER: Full name of the Update trigger linked to the journalized datastore.

  • JRN_DTRIGGER: Full name of the Delete trigger linked to the journalized datastore.

  • SUBSCRIBER_TABLE: Full name of the datastore containing the subscribers list.

  • CDC_SET_TABLE: Full name of the table containing list of CDC sets.

  • CDC_TABLE_TABLE: Full name of the table containing the list of tables journalized through CDC sets.

  • CDC_SUBS_TABLE: Full name of the table containing the list of subscribers to CDC sets.

  • CDC_OBJECTS_TABLE: Full name of the table containing the journalizing parameters and objects.

  • <flexfield_code>: Flexfield value for the current target table.

pLocation

String

W: Returns the full name of the object in the physical catalog and the physical work schema that corresponds to the current tuple (context, logical schema)

D: Returns the full name of the object in the physical catalog and the physical data schema that corresponds to the current tuple (context, logical schema)

A: Lets Oracle Data Integrator determine the default location of the object. This value is used if pLocation is not specified.


Examples

If you have defined a physical schema called Pluton.db_odi.dbo as shown below:

Data catalog:

db_odi

Data schema:

dbo

Work catalog:

tempdb

Work schema:

temp_owner

Local Mask:

%CATALOG.%SCHEMA.%OBJECT

Remote mask:

%DSERVER:%CATALOG.%SCHEMA.%OBJECT

Loading prefix:

CZ_

Error prefix:

ERR_

Integration prefix:

I$_


You have associated this physical schema to the logical schema called MSSQL_ODI in the context CTX_DEV and your working with a table is named CUSTOMER.

A Call To Returns

<%=odiRef.getTable("L", "COLL_NAME", "W")%>

tempdb.temp_owner.CZ_0CUSTOMER

<%=odiRef.getTable("R", "COLL_NAME", "D")%>

MyServer:db_odi.dbo.CZ_0CUSTOMER

<%=odiRef.getTable("L", "INT_NAME", "W")%>

tempdb.temp_owner.I$_CUSTOMER

<%=odiRef.getTable("R", "ERR_NAME", "D")%>

MyServer:db_odi.dbo.ERR_CUSTOMER


A.2.59 getTargetColList() Method

Use to return information about the active attributes of the target table of a mapping. Active attributes are those having an active mapping. To return information about all attributes of the target table, including active and non-active attributes, use the getAllTargetColList() Method.

Usage

public java.lang.String getTargetColList( java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd,
java.lang.String pSelector)

Alternative syntaxes:

public java.lang.String getTargetColList( java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

public java.lang.String getTargetColList( java.lang.String pPattern,
java.lang.String pSeparator)

Description

Provides a list of attributes for the mapping's target table.

The pPattern parameter is interpreted and then repeated for each element of the list (selected according to pSelector parameter) and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

In the alternative syntaxes, any parameters not set are set to an empty string.

Parameters

Parameters Type Description

pStart

String

This sequence marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of the attributes usable in a pattern is detailed in the Pattern Attributes List below.

Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a attribute of the target»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This sequence marks the end of the string to generate.

pSelector

String

String that designates a Boolean expression that allows to filter the elements of the initial list with the following format:

<SELECTOR> <Operator> <SELECTOR> etc. Parenthesis are authorized.

Authorized operators:

  1. No: NOT or!

  2. Or: OR or ||

  3. And: AND or &&

Example: (INS AND UPD) OR TRG

The description of valid selectors is provided below.


Pattern Attributes List

The following table lists different parameters values as well as their associated description.

Parameter Value Description

I_COL

Internal identifier of the attribute

COL_NAME

Name of the attribute

COL_HEADING

Header of the attribute

COL_DESC

Description of the attribute

POS

Position of the attribute

LONGC

Attribute length (Precision)

SCALE

Scale of the attribute

FILE_POS

Beginning (index) of the attribute

BYTES

Number of physical bytes in the attribute

FILE_END_POS

End of the attribute (FILE_POS + BYTES)

IND_WRITE

Write right flag of the attribute

COL_MANDATORY

Mandatory character of the attribute. Valid values are:

  • 0: null authorized

  • 1: not null

CHECK_FLOW

Flow control flag of the attribute. Valid values are: (0: do not check, 1: check)

  • 0: do not check

  • 1: check

CHECK_STAT

Static control flag of the attribute. Valid values are: (0: do not check, 1: check)

  • 0: do not check

  • 1: check

COL_FORMAT

Logical format of the attribute

COL_DEC_SEP

Decimal symbol of the attribute

REC_CODE_LIST

List of the record codes retained in the attribute

COL_NULL_IF_ERR

Processing flag of the attribute. Valid values are: (0 = Reject, 1 = Set to null active trace, 2= set to null inactive trace)

  • 0: Reject

  • 1: Set to null active trace

  • 2: Set to null inactive trace

DEF_VALUE

Default value of the attribute

ALIAS_SEP

Separator used for the alias (from the technology)

SOURCE_DT

Code of the attribute's datatype.

SOURCE_CRE_DT

Create table syntax for the attribute's datatype.

SOURCE_WRI_DT

Create table syntax for the attribute's writable datatype.

DEST_DT

Code of the attribute's datatype converted to a datatype on the target technology.

DEST_CRE_DT

Create table syntax for the attribute's datatype converted to a datatype on the target technology.

DEST_WRI_DT

Create table syntax for the attribute's writable datatype converted to a datatype on the target technology.

SCD_COL_TYPE

Behavior defined for the Slowly Changing Dimensions for this attribute in the data model.

MANDATORY_CLAUSE

Returns NOT NULL is the attribute is mandatory. Otherwise, returns the null keyword for the technology.

DEFAULT_CLAUSE

Returns DEFAULT <default value> if any default value exists. Otherwise, returns and empty string.

JDBC_TYPE

Data Services - JDBC Type of the attribute returned by the driver.

<flexfield code>

Flexfield value for the current attribute.


Selectors Description

Parameter Value Description

INS

  • LKM: Not applicable

  • IKM: Only for mapping expressions marked with insertion

  • CKM: Not applicable

UPD

  • LKM: Not applicable

  • IKM: Only for the mapping expressions marked with update

  • CKM: Non applicable

TRG

  • LKM: Not applicable

  • IKM: Only for the mapping expressions executed on the target

  • CKM: Mapping expressions executed on the target.

NULL

  • LKM: Not applicable

  • IKM: All mapping expressions loading not nullable attributes

  • CKM: All target attributes that do not accept null values

PK

  • LKM: Not applicable

  • IKM: All mapping expressions loading the primary key attributes

  • CKM: All the target attributes that are part of the primary key

UK

  • LKM: Not applicable.

  • IKM: All the mapping expressions loading the update key attribute chosen for the current mapping.

  • CKM: Not applicable.

REW

  • LKM: Not applicable.

  • IKM: All the mapping expressions loading the attributes with read only flag not selected.

  • CKM: All the target attributes with read only flag not selected.

MAP

  • LKM: Not applicable

  • IKM: Not applicable

  • CKM:

Flow control: All attributes of the target table loaded with expressions in the current mapping.

Static control: All attributes of the target table.

SCD_SK

LKM, CKM, IKM: All attributes marked SCD Behavior: Surrogate Key in the data model definition.

SCD_NK

LKM, CKM, IKM: All attributes marked SCD Behavior: Natural Key in the data model definition.

SCD_UPD

LKM, CKM, IKM: All attributes marked SCD Behavior: Overwrite on Change in the data model definition.

SCD_INS

LKM, CKM, IKM: All attributes marked SCD Behavior: Add Row on Change in the data model definition.

SCD_FLAG

LKM, CKM, IKM: All attributes marked SCD Behavior: Current Record Flag in the data model definition.

SCD_START

LKM, CKM, IKM: All attributes marked SCD Behavior: Starting Timestamp in the data model definition.

SCD_END

LKM, CKM, IKM: All attributes marked SCD Behavior: Ending Timestamp in the data model definition.

WS_INS

SKM: The attribute is flagged as allowing INSERT using Data Services.

WS_UPD

SKM: The attribute is flagged as allowing UDATE using Data Services.

WS_SEL

SKM: The attribute is flagged as allowing SELECT using Data Services.


Examples

create table TARGET_COPY <%=odiRef.getTargetColList("(", "[COL_NAME] [DEST_DT] null", ", ", ")", "")%>

A.2.60 getTableName() Method

Use to return the name of the loading or integration table.

Usage

public java.lang.String getTableName(
java.lang.String pProperty)

Description

This method returns the name of the temporary table used for loading or integration. This name is not qualified.

Parameters

Parameters Type Description

pProperty

String

Parameter that indicates the name of the table to retreive. The list of possible values is:

  • INT_SHORT_NAME: Name of the integration table.

  • COLL_SHORT_NAME: Name of the loading table.


Examples

<%= odiRef.getTableName("COLL_SHORT_NAME") %>

A.2.61 getTargetTable() Method

Use to return information about the target table of a mapping.

Usage

public java.lang.String getTargetTable(java.lang.String pPropertyName)

Description

This method returns information about the current target table. The list of available data is described in the pPropertyName values table.

In an action, this method returns information on the table being processed by the DDL command.

Parameters

Parameter Type Description

pPropertyName

String

String that contains the name of the requested property.


The following table lists the possible values for pPropertyName:

Parameter Value Description

I_TABLE

Internal identifier of the datastore

MODEL_NAME

Name of the model of the current datastore.

SUB_MODEL_NAME

Name of the sub-model of the current datastore.

TECHNO_NAME

Name of the target technology.

LSCHEMA_NAME

Name of the target logical schema.

TABLE_NAME

Name of the target datastore.

RES_NAME

Physical name of the target resource.

CATALOG

Catalog name.

WORK_CATALOG

Name of the work catalog.

SCHEMA

Schema name

WORK_SCHEMA

Name of the work schema.

TABLE_ALIAS

Alias of the current datastore.

TABLE_TYPE

Type of the datastore.

DESCRIPTION

Description of the current mapping.

TABLE_DESC

Description of the current mapping's target datastore. For a DDL command, description of the current table.

R_COUNT

Number of lines of the current datastore.

FILE_FORMAT

Format of the current datastore (file)

FILE_SEP_FIELD

Field separator (file)

XFILE_SEP_FIELD

Hexadecimal field separator (file)

SFILE_SEP_FIELD

Field separator string (file)

FILE_ENC_FIELD

Field beginning and ending character (file)

FILE_SEP_ROW

Record separator (file)

XFILE_SEP_ROW

Hexadecimal record separator (file)

SFILE_SEP_ROW

Record separator string (file)

FILE_FIRST_ROW

Number of lines to ignore at the beginning of the file (file)

FILE_DEC_SEP

Decimal symbol (file)

METADATA_DESC

Description of the metadata of the datastore (file)

OLAP_TYPE

OLAP type specified in the datastore definition

IND_JRN

Flag indicating that the datastore is including in CDC.

JRN_ORDER

Order of the datastore in the CDC set for consistent journalizing.

WS_NAME

Data Services - Name of the Web service generated for this datastore's model.

WS_NAMESPACE

Data Services - XML namespace of the web Service.

WS_JAVA_PACKAGE

Data Services - Java package generated for the web Service.

WS_ENTITY_NAME

Data Services - Entity name used for this datastore in the web service.

WS_DATA_SOURCE

Data Services - Datasource specified for this datastore's web service.

PARTITION_NAME

Name of the partition or sub-partition selected for the target datastore. If no partition is selected, returns an empty string.

PARTITION_TYPE

Type of the partition or sub-partition selected for the target datastore. If no partition is selected, returns an empty string.

  • P: Partition

  • S: Sub-partition

<flexfield code>

Flexfield value for the current table.


Examples

The current table is: <%=odiRef.getTargetTable("RES_NAME")%>

A.2.62 getTemporaryIndex() Method

Use to return information about a temporary index defined for optimizing a join or a filter in a mapping.

Usage

public java.lang.String getTemporaryIndex(java.lang.String pPropertyName)

Description

This method returns information relative to a temporary index being created or dropped by a mapping.

It can be used in a Loading or Integration Knowledge Module task if the Create Temporary Index option is set to On Source or On Target for this task.

Parameters

Parameter Type Description

pPropertyName

String

String containing the name of the requested property.


The following table lists the different possible values for pPropertyName.

Parameter Value Description

IDX_NAME

Name of the index. This name is computed and prefixed with the temporary index prefix defined for the physical schema.

FULL_IDX_NAME

Fully qualified name of the index. On the target tab, this name is qualified to create the index in the work schema of the staging area. On the source tab, this name is qualified to create the index in the source default work schema (LKM) or in the work schema of the staging area (IKM).

COLL_NAME

Fully qualified name of the loading table for an LKM. This property does not apply to IKMs.

CATALOG

Catalog containing the table to be indexed.

SCHEMA

Schema containing the table to be indexed.

WORK_CATALOG

Work catalog for the table to be indexed.

WORK_SCHEMA

Work schema for the table to be indexed.

DEF_CATALOG

Default catalog containing the table to be indexed.

DEF_SCHEMA

Default schema containing the table to be indexed.

DEF_WORK_CATALOG

Default work catalog for the table to be indexed.

DEF_WORK_SCHEMA

Default work schema for the table to be indexed.

DEF_WORK_SCHEMA

Default work schema for the table to be indexed.

LSCHEMA_NAME

Logical schema of the table to be indexed.

TABLE_NAME

Name of the table to be indexed.

FULL_TABLE_NAME

Fully qualified name of the table to be indexed.

INDEX_TYPE_CODE

Code representing the index type.

INDEX_TYPE_CLAUSE

Clause for creating an index of this type.

POP_TYPE_CLAUSE

Type of the clause for which the index is generated:

  • J: Join.

  • F: Filter.

EXPRESSION

Expression of the join or filer clause. Use for debug purposes.


Examples

Create <%=odiRef.getTemporaryIndex (" [INDEX_TYPE_CLAUSE] index [FULL_IDX_NAME] on [FULL_TABLE_NAME] " )%><%=odiRef.getTemporaryIndexColList("(", "[COL_NAME]", ", ", ")")%>

A.2.63 getTemporaryIndexColList() Method

Use to return information about the columns of a temporary index for a mapping.

Usage

public java.lang.String getTemporaryIndexColList(java.lang.String pStart,
java.lang.String pPattern,
java.lang.String pSeparator,
java.lang.String pEnd)

Description

Returns a list of columns of a temporary index.

The parameter pPattern is interpreted and repeated for each element of the list, and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.

This list contains one element for each column of the temporary index.

It can be used in a Loading or Integration Knowledge Module task if the Create Temporary Index option is set to On Source or On Target for this task.

Parameters

Parameter Type Description

Parameter

Type

Description

pStart

String

This parameter marks the beginning of the string to generate.

pPattern

String

The pattern is repeated for each occurrence in the list.

The list of possible attributes in a pattern is detailed in the Pattern Attributes List below.

Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ])

Example «My string [COL_NAME] is a attribute»

pSeparator

String

This parameter separates each pattern from its predecessor.

pEnd

String

This parameter marks the end of the string to generate.


Pattern Attributes List

The following table lists the different values of the parameters as well as the associated description.

Parameter Value Description

CX_COL_NAME

Computed name of the attribute used as a container for the current expression on the staging area

COL_NAME

Name of the attribute participating to the index.

POS

Position of the first occurrence of this attribute in the join or filter clause this index optimizes.


Examples

Create <%=odiRef.getTemporaryIndex (" [INDEX_TYPE_CLAUSE] index [FULL_IDX_NAME] on [FULL_TABLE_NAME] " )%><%=odiRef.getTemporaryIndexColList("(", "[COL_NAME]", ", ", ")")%>

A.2.64 getUser() Method

Use to return information about the user running the current session.

Usage

public java.lang.String getUser(java.lang.String pPropertyName)

Description

This method returns information about the user executing the current session. The list of available properties is described in the pPropertyName values table.

Parameters

Parameter Type Description

pPropertyName

String

String that contains the name of the requested property.


The following table lists the different possible values for pPropertyName:

Parameter Value Description

Parameter Value

Description

I_USER

User identifier

USER_NAME

User name

IS_SUPERVISOR

Boolean flag indicating if the user is supervisor (1) or not (0).


Examples

This execution is performed by <%=odiRef.getUser("USER_NAME")%>

A.2.65 hasPK() Method

Use to return whether if the current datastore has a primary key.

Usage

public java.lang.Boolean hasPK()

Description

This method returns a boolean. The returned value is true if the datastore for which a web service is being generated has a primary key.

This method can only be used in SKMs.

Examples

<% if (odiRef.hasPK()) { %>

         There is a PK :

          <%=odiRef.getPK("KEY_NAME")%> : <%=odiRef.getPKColList("{",
           "\u0022[COL_NAME]\u0022", ", ", "}")%>

<% } else {%>

         There is NO PK.

<% } %>

A.2.66 isColAttrChanged() Method

Use to return whether a column attribute or comment is changed.

Usage

public java.lang.Boolean 
isColAttrChanged(java.lang.String pPropertyName)

Description

This method is usable in a column action for altering a column attribute or comment. It returns a boolean indicating if the column attribute passed as a parameter has changed.

Parameters

Parameter Type Description

pPropertyName

String

Attribute code (see below).


The following table lists the different possible values for pPropertyName

Parameter Value Description

DATATYPE

Column datatype, length or precision change.

LENGTH

Column length change (for example, VARCHAR(10) changes to VARCHAR(12)).

PRECISION

Column precision change (for example, DECIMAL(10,3) changes to DECIMAL(10,4)).

COMMENT

Column comment change.

NULL_TO_NOTNULL

Column nullable attribute change from NULL to NOT NULL.

NOTNULL_TO_NULL

Column nullable attribute change from NOT NULL to NULL.

NULL

Column nullable attribute change.

DEFAULT

Column default value change.


Examples

<% if (odiRef.IsColAttrChanged("DEFAULT") ) { %>
          /* Column default attribute has changed. */
<% } %>

A.2.67 nextAK() Method

Use to move to the next alternate key for a datastore.

Usage

public java.lang.Boolean nextAK()

Description

This method moves to the next alternate key (AK) of the datastore for which a Web service is being generated.

When first called, this method returns true and positions the current AK to the first AK of the datastore. If there is no AK for the datastore, it returns false.

Subsequent calls position the current AK to the next AKs of the datastore, and return true. If the is no next AK, the method returns false.

This method can be used only in SKMs.

Examples

In the example below, we iterate of all the AKs of the datastore. In each iteration of the while loop, the getAK and getAKColList methods return information on the various AKs of the datastore.

<% while (odiRef.nextAK()) { %>
         <%=odiRef.getAK("KEY_NAME")%>
    Columns <%=odiRef.getAKColList("{", "\u0022[COL_NAME]\u0022", ",
 ", "}")%>
         Message : <%=odiRef.getAK("MESS")%>
<% } %>

A.2.68 nextCond() Method

Use to move to the next condition for a datastore.

Usage

public java.lang.Boolean nextCond()

Description

This method moves to the next condition (check constraint) of the datastore for which a Web service is being generated.

When first called, this method returns true and positions the current condition to the first condition of the datastore. If there is no condition for the datastore, it returns false.

Subsequent calls position the current condition to the next conditions of the datastore, and return true. If the is no next condition, the method returns false.

This method can be used only in SKMs.

Examples

In the example below, we iterate of all the conditions of the datastore. In each iteration of the while loop, the getCK method return information on the various conditions of the datastore.

<% while (odiRef.nextCond()) { %>
        <%=odiRef.getCK("COND_NAME")%>
                 SQL :<%=odiRef.getCK("COND_SQL")%>
                 MESS :<%=odiRef.getCK("MESS")%>
<% } %>

A.2.69 nextFK() Method

Use to move to the next foreign key for a datastore.

Usage

public java.lang.Boolean nextFK()

Description

This method moves to the next foreign key (FK) of the datastore for which a Web service is being generated.

When first called, this method returns true and positions the current FK to the first FK of the datastore. If there is no FK for the datastore, it returns false.

Subsequent calls position the current FK to the next FKs of the datastore, and return true. If the is no next FK, the method returns false.

This method can be used only in SKMs.

Examples

In the example below, we iterate of all the FKs of the datastore. In each iteration of the while loop, the getFK and getFKColList methods return information on the various FKs of the datastore.

<% while (odiRef.nextFK()) { %>
        FK : <%=odiRef.getFK("FK_NAME")%>
                Referenced Table : <%=odiRef.getFK("PK_TABLE_NAME")%>
                Columns <%=odiRef.getFKColList("{", "\u0022[COL_NAME]\u0022", ",
 ", "}")%>
                Message : <%=odiRef.getFK("MESS")%>
<% } %>

A.2.70 setNbInsert(), setNbUpdate(), setNbDelete(), setNbErrors() and setNbRows() Methods

Use to set the number of inserted, updated, deleted or erroneous rows for the current task.

Usage

public java.lang.Void setNbInsert(public java.lang.Long)

public java.lang.Void setNbUpdate(public java.lang.Long)

public java.lang.Void setNbDelete(public java.lang.Long)

public java.lang.Void setNbErrors(public java.lang.Long)

public java.lang.Void setNbRows(public java.lang.Long)

Description

These methods set for the current task report the values for:

  • the number of rows inserted (setNbInsert)

  • the number of rows updated (setNbUpdate)

  • the number of rows deleted (setNbDelete)

  • the number of rows in error (setNbErrors)

  • total number of rows handled during this task (setNbRows)

These numbers can be set independently from the real number of lines processed.

Note:

This method can be used only within scripting engine commands, such as in Jython code, and should not be enclosed in <%%> tags.

Examples

In the Jython example below, we set the number of inserted rows to the constant value of 50, and the number of erroneous rows to a value coming from an ODI variable called #DEMO.NbErrors.

InsertNumber=50

odiRef.setNbInsert(InsertNumber)

ErrorNumber=#DEMO.NbErrors

odiRef.setNbErrors(ErrorNumber)

A.2.71 setTableName() Method

Use to set the name of the loading or integration table.

Usage

public java.lang.Void setTableName(
java.lang.String pProperty,
java.lang.String pTableName)

Description

This method sets the name of temporary table used for loading or integration. this name can be any value.

When using the method, the loading or integration table name is no longer generated by ODI and does not follow the standard naming convention (for example, a loading table will not be prefixed with a C$ prefix). Yet, other methods using this table name will return the newly set value.

The fist parameter pProperty indicates the temporary table name to set. The second parameter can be any valid table name.

Parameters

Parameters Type Description

pProperty

String

Parameter that indicates the table name to set. The list of possible values is:

  • INT_SHORT_NAME: Name of the integration table.

  • COLL_SHORT_NAME: Name of the loading table.

pTableName

String

New name for the temporary table.


Examples

<% odiRef.setTableName("COLL_SHORT_NAME", "C" + getInfo("I_SRC_SET")) %>

<% odiRef.setTableName("COLL_SHORT_NAME", odiRef.getOption("Flow # ") + odiRef.getTable("ID")) %> 

A.2.72 setTaskName() Method

Use to set the name of a session task in a Knowledge Module, Procedure, or action.

Usage

public java.lang.String setTaskName(
java.lang.String taskName)

Description

This method sets the name of a task to the taskName value. This value is set at run-time. This method is available in all Knowledge Modules, procedures, and actions (Global Methods).

Parameters

Parameters Type Description

taskName

String

Parameter that indicates the task name to set. If this vlaue is empty, the task remains the one defined in the Knowledge Module or Procedure task.


Examples

<$=odiRef.setTaskName("Create Error Table " + "<%=odiRef.getTable("L","ERR_NAME","W")%>") $> 
<$=odiRef.setTaskName("Insert Error for " + "<%=odiRef.getFK("FK_NAME")%>") $>
<$=odiRef.setTaskName("Loading " + "<%=odiRef.getTable("L", "COLL_NAME", "W")%>" + " from " + "<%=odiRef.getSrcTablesList("", "RES_NAME", ",", ".")%>" ) $>