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:
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:
The following methods can be used in all knowledge module and actions:
In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Journalizing Knowledge Modules (JKM):
In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Loading Knowledge Modules (LKM):
In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Check Knowledge Modules (CKM):
In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Integration Knowledge Modules IKM):
In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Reverse-engineering Knowledge Modules (RKM):
In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Service Knowledge Modules (SKM):
In addition to the methods from in the "Global Methods" list, the following methods can be used specifically in Actions.
This section provides an alphabetical list of the substitution methods. Each method is detailed with usage, description, parameters and example code.
Use to return information about an alternate key.
public java.lang.String getAK(java.lang.String pPropertyName)
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.
| 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. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| GUID | GUID of the Alternate Key |
| 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. |
The alternate key of my table is named: <%=odiRef.getAK("KEY_NAME")%>
Use to return information about the attributes of an alternate key.
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)
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 | 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. |
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:
|
| CHECK_FLOW | Flow control flag of the attribute:
|
| CHECK_STAT | Static control flag of the attribute:
|
| 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:
|
| 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. |
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 ")"
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.61, "getTargetColList() Method" for more details.
Use to return a catalog name from the topology.
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()
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.
| 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:
|
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 |
Use to return a catalog name for the default physical schema from the topology.
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()
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.
| 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:
|
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 |
Use to return information about a condition.
public java.lang.String getCK(java.lang.String pPropertyName)
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.
| 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 constraintThis parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| GUID | GUID of the check. |
| 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. |
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")%>))
Use to return the default value of a mapped attribute.
public java.lang.String getColDefaultValue()
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.
None.
The default value of my target attribute is '+ 'odiRef.getColDefaultValue()'
Use to return properties for each attribute from a filtered list of attributes. The properties are organized according to a string pattern.
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)
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.
Notes:
This method automatically generates lookups with no specific code required.
When an attribute name from a source exceeds the maximum allowed length of an attribute name for the target technology, it is automatically truncated.
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.
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.
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.
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.
| 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:
Example: (INS AND UPD) OR TRG The description of valid selectors is provided below. |
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:
|
| CHECK_FLOW | Flow control flag of the attribute. Valid values are:
|
| CHECK_STAT | Static control flag of the attribute. Valid values are:
|
| 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:
|
| 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. |
| Parameter Value | Description |
|---|---|
| INS |
|
| UPD |
|
| TRG |
|
| NULL |
|
| PK |
|
| UK |
|
| REW |
|
| UD1 |
|
| UD2 |
|
| UD3 |
|
| UD4 |
|
| UD5 |
|
| MAP |
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.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)
Use to return information about a specific attribute handled by an action.
public java.lang.String getColumn( java.lang.String pPattern, java.lang.String pSelector) public java.lang.String getColumn( java.lang.String pPattern)
In an action, returns information on a attribute being handled by an the action.
| 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:
If the selector is omitted, it is set to OLD for all drop actions. Otherwise, it is set to NEW. |
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:
|
| CHECK_FLOW | Flow control flag of the attribute. Valid values are:
|
| CHECK_STAT | Static control flag of the attribute. Valid values are:
|
| 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:
|
| 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. |
Use to return information about the current context.
public java.lang.String getContext(java.lang.String pPropertyName)
This method returns information about to the current execution context.
| 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. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| GLOBAL_ID | GUID 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. |
Current Context = <%=getContext("CTX_NAME")%>
Use to return information about a given dataset of a mapping.
public java.lang.String getDataSet( java.lang.Int pDSIndex, java.lang.String pPropertyName)
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.
| 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. |
<%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)%>
<%}%>
Use to return the number of datasets of a mapping.
public java.lang.Int getDataSetCount()
Returns the number of datasets of a mapping.
None
<%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)%>
<%}%>
Use to return the syntax creating a attribute of a given datatype.
public java.lang.String getDataType( java.lang.String pDataTypeName, java.lang.String pDataTypeLength, java.lang.String pDataTypePrecision)
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 | 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 |
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 |
Use to return the entire WHERE clause section generated for the filters of a mapping.
public java.lang.String getFilter(java.lang.Int pDSIndex)
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.| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
None
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()%>
Use to return properties for each filter of a mapping. The properties are organized according to a string pattern.
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)
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.
| 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. |
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. |
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.
Use to return information about a foreign key.
public java.lang.String getFK(java.lang.String pPropertyName)
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.
| 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. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| GUID | GUID of the foreign key. |
| 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. |
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")%>
Use to return information about the attributes of a foreign key.
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)
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.
| 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. |
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:
|
| CHECK_FLOW | Flow control flag of the attribute. Valid values are:
|
| CHECK_STAT | Static control flag of the attribute. Valid values are:
|
| 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:
|
| 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:
|
| PK_CHECK_FLOW | Flow control flag of the referenced attribute. Valid values are:
|
| PK_CHECK_STAT | Static control flag of the referenced attribute. Valid values are:
|
| 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:
|
| 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. |
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 ")".
Use to return the value of a flexfield.
public java.lang.String getFlexFieldValue(java.lang.String pI_Instance, java.lang.String pI_Object, java.lang.String pFlexFieldCode)
This method returns the value of an Object Instance's Flexfield.
| 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. |
<%=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.
Use to construct a name with text, and ODI prefixes.
public java.lang.String getFormattedName(java.lang.String pName) public java.lang.String getFormattedName(java.lang.String pName,java.lang.String pTechnology)
Use to construct a name that is based on some text, ODI prefixes, and is valid for an optional technology. The text can contain the prefixes available for getObjectName() Method, e.g. %INT_PRF, %COL_PRF, %ERR_PRF, %IDX_PRF along with %UNIQUE_STEP_TAG or %UNIQUE_SESSION_TAG. The latter tags will be expanded if unique names are enabled. Calls to this API within the same execution context are guaranteed to return the same unique name provided that the same parameters are passed to the call.
| Parameter | Type | Description |
|---|---|---|
| pName | String | Name that is used as the initial key, and can contain other ODI prefixes. |
| pTechnology | String | An optional technology that the returned name will be validated. For, e.g. name length. |
<%=odiRef.getFormattedName("%COL_PRFMY_TABLE%UNIQUE_STEP_TAG_AE", "ORACLE")%>
might result in
C$_MY_TAB7wDiBe80vBog1auacS1xB _AE
<?=getFormattedName( "<%=getTableName("COLL_SHORT_NAME")%>.ctl", "FILE" )%>"
might result in:-
C$_0DEFAULT.ctl
or, if unique names are enabled
C_0DEFAULTAhbxZoeJ2zznXYpnDzhkm.ctl
C$_0
Use to return the SQL FROM clause in the given context.
public java.lang.String getFrom(java.lang.Int pDSIndex)
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.
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
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()%>
Use to return the entire SQL GROUP BY clause in the given context.
public java.lang.String getGrpBy(java.lang.Int pDSIndex)
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.| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
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()%>
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.
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)
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.
| 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. |
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 |
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
Use to return the entire SQL HAVING clause in the given context.
public java.lang.String getHaving(java.lang.Int pDSIndex)
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.| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
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()%>
Use to return properties for each HAVING clause of a mapping. The properties are organized according to a string pattern.
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)
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 | 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. |
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 |
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
Use to return information about a specific index handled by an action.
public java.lang.String getIndex(java.lang.String pPropertyName)
In an action, this method returns information related to the index currently handled by the DDL command.
| 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. |
Use to return information about the attributes of an index handled by an action.
public java.lang.String getIndexColList(java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
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 | 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. |
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:
|
| CHECK_FLOW | Flow control flag for of the attribute. Valid values are:
|
| CHECK_STAT | Static control flag of the attribute. Valid values are:
|
| 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:
|
| 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. |
Use to return information about the current task.
public java.lang.String getInfo(java.lang.String pPropertyName)
This method returns information about the current task. The list of available information is described in the pPropertyName values table.
| 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 | Description |
| GUID_SRC_SET | Internal number of the reference constraint. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| SRC_SET_NAME | GUID of the foreign key. |
| COLL_NAME | Name of the reference constraint. |
| INT_NAME | Type of the reference constraint. |
| ERR_NAME | Alias of the reference table (only used in case of a complex expression) |
| TARG_NAME | Alias of the referenced table (only used in case of a complex expression) |
| SRC_CATALOG | Internal number of the referenced table. |
| SRC_SCHEMA | Number of the referenced model. |
| SRC_WORK_CATALOG | Catalog of the referenced table in the current context. |
| SRC_WORK_SCHEMA | Physical schema of the referenced table in the current context. |
| DEST_CATALOG | Name of the referenced table. |
| DEST_SCHEMA | Complex SQL statement of the join clause (if appropriate). |
| DEST_WORK_CATALOG | Error message of the reference constraint |
| DEST_WORK_SCHEMA | Full name of the foreign key generated with the local object mask. |
| SRC_TECHNO_NAME | Flexfield value for this reference. |
| 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 | Description |
| CT_ERR_GUID | Internal number of the reference constraint. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| CT_ORIGIN | GUID of the foreign key. |
| JRN_NAME | Name of the reference constraint. |
| JRN_VIEW | Type of the reference constraint. |
| JRN_DATA_VIEW | Alias of the reference table (only used in case of a complex expression) |
| JRN_TRIGGER | Alias of the referenced table (only used in case of a complex expression) |
| JRN_ITRIGGER | Internal number of the referenced table. |
| JRN_UTRIGGER | Number of the referenced model. |
| JRN_DTRIGGER | Catalog of the referenced table in the current context. |
| SUBSCRIBER_TABLE | Physical schema of the referenced table in the current context. |
| CDC_SET_TABLE | Name of the referenced table. |
| CDC_TABLE_TABLE | Complex SQL statement of the join clause (if appropriate). |
| CDC_SUBS_TABLE | Error message of the reference constraint |
| CDC_OBJECTS_TABLE | Full name of the foreign key generated with the local object mask. |
| SRC_DEF_CATALOG | Flexfield value for this reference. |
| 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 | Description |
| SRC_CONNECT_GUID | Internal number of the reference constraint. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| SRC_I_PSCHEMA | GUID of the foreign key. |
| SRC_PSCHEMA_GUID | Name of the reference constraint. |
| SRC_I_LSCHEMA | Type of the reference constraint. |
| SRC_LSCHEMA_GUID | Alias of the reference table (only used in case of a complex expression) |
| SRC_I_TECHNO | Alias of the referenced table (only used in case of a complex expression) |
| SRC_TECHNO_GUID | Internal number of the referenced table. |
| DEST_I_CONNECT | Number of the referenced model. |
| DEST_CONNECT_GUID | Catalog of the referenced table in the current context. |
| DEST_I_PSCHEMA | Physical schema of the referenced table in the current context. |
| DEST_PSCHEMA_GUID | Name of the referenced table. |
| DEST_I_LSCHEMA | Complex SQL statement of the join clause (if appropriate). |
| DEST_LSCHEMA_GUID | Error message of the reference constraint |
| DEST_I_TECHNO | Full name of the foreign key generated with the local object mask. |
| DEST_TECHNO_GUID | Flexfield value for this reference. |
| UNIQUE_STEP_TAG | Tag used to make names unique in the scope of the current Step. |
| UNIQUE_SESSION_TAG | Tag used to make names unique in the scope of the current Session. |
| IS_CONCURRENT | Returns 1 if the current task is using unique names for the temporary objects. |
| SRC_SET_GUID | GUID of the current Execution Unit if the task belongs to a Loading Knowledge Module. |
| ODI_MAJOR_VERSION | The major product version for the current ODI installation. For example, if your current release is 12.1.3.0.0, returns 12. |
The current source condition is: <%=odiRef.getInfo("SRC_CON_NAME")%> on server: <%=odiRef.getInfo("SRC_DSERV_NAME")%>
Use to return the source or target JDBC connection.
java.sql.Connection getJDBCConnection( java.lang.String pPropertyName)
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.
| 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. |
Gets the source connection and creates a statement for this connection.
java.sql.Connection sourceConnection = odiRef.getJDBCConnection("SRC");
java.sql.Statement s = sourceConnection.createStatement();
Use to return a JDBC connection for a given logical schema.
public java.lang.String getJDBCConnectionFromLSchema( java.lang.String pLogicalSchemaName, java.lang.String pContextName) public java.lang.String getJDBCConnectionFromLSchema( java.lang.String pLogicalSchemaName)
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.
| 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.
Use to return the entire WHERE clause section generated for the joins of a mapping.
public java.lang.String getJoin(java.lang.Int pDSIndex)
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.| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
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()%>
Use to return properties for each join of a mapping. The properties are organized according to a string pattern.
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)
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.
| 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. |
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 |
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
Use to return the journalizing filter of a mapping.
public java.lang.String getJrnFilter(java.lang.Int pDSIndex)
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.| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
<%=odiRef.getJrnFilter()%>
Use to return journalizing information about a datastore.
public java.lang.String getJrnInfo(java.lang.String pPropertyName)
Returns information about a datastore's journalizing for a JKM while journalizing a model or datastore, or for a LKM or IKM in a mapping.
Note:
Journalizing information is only available when the code is generated in ODI Studio. The same odiRef.getInfo() call returns an empty string if it is evaluated in the agent (when the call is wrapped inside<? ... ?> or <@ ... @>), because the metadata about journalizing is not available anymore in the runtime repository.| 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. |
| 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. |
The table being journalized is <%=odiRef.getJrnInfo("FULL_TABLE_NAME")%>
Use to return the Load Plan instance information.
public java.lang.String getLoadPlanInstance (java.lang.String pPropertyName)
This method returns the current execution instance information for a Load Plan.
| 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 | Description |
| BATCH_GUID | Internal number of the reference constraint. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| RESTART_ATTEMPTS | GUID of the foreign key. |
| LOAD_PLAN_NAME | Name of the reference constraint. |
| START_DATE | Type of the reference constraint. |
The current Load Plan <%=odiRef.getLoadPlanInstance("LOAD_PLAN_NAME")%> started execution at <%=odiRef.getLoadPlanInstance("START_DATE")%>
Use to return information about a model.
public java.lang.String getModel(java.lang.String pPropertyName)
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).| 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 | Description |
| GLOBAL_ID | Internal number of the reference constraint. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| MOD_NAME | GUID of the foreign key. |
| LSCHEMA_NAME | Name of the reference constraint. |
| MOD_TEXT | Type of the reference constraint. |
| REV_TYPE | Alias of the reference table (only used in case of a complex expression) |
| REV_UPDATE | Alias of the referenced table (only used in case of a complex expression) |
| REV_INSERT | Internal number of the referenced table. |
| REV_OBJ_PATT | Number of the referenced model. |
| REV_OBJ_TYPE | Catalog of the referenced table in the current context. |
| TECH_INT_NAME | Physical schema of the referenced table in the current context. |
| LAGENT_NAME | Name of the referenced table. |
| REV_CONTEXT | Complex SQL statement of the join clause (if appropriate). |
| REV_ALIAS_LTRIM | Error message of the reference constraint |
| CKM | Full name of the foreign key generated with the local object mask. |
| RKM | Flexfield value for this reference. |
| 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. |
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")%>'
Use to get the number of inserted, updated, deleted or erroneous rows for the current task.
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()
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.
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())
Use to return the new comment for a specific attribute handled by an action.
public java.lang.String getNewColComment()
In an action, this method returns the new comment for the attribute being handled by the DDL command, in a Modify column comment action.
Use to return the new comment for a specific table handled by an action.
public java.lang.String getNewTableComment()
In an action, this method returns the new comment for the table being handled by the DDL command, in a Modify table comment action.
Use to return information about a attribute that is checked for not null.
public java.lang.String getNotNullCol(java.lang.String pPropertyName)
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".
| 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. This The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| GLOBAL_ID | GUID 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. |
insert into...
select *
from ...
<%=odiRef.getNotNullCol("COL_NAME")%> is null
Use to return the fully qualified named of an object.
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)
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 truncated. To prevent object name 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 totrue.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.
| 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:
|
| pPartitionType | String | Specify whether to qualify the object name for a specific partition or sub-partition. The valid values are:
|
| pPartitionName | String | Name of the partition of sub-partition to qualify the object name. |
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$_"). |
| %UNIQUE_STEP_TAG | Prefix used to inform the final phase of code generation that unique names are enabled for temporary objects. |
| %UNIQUE_SESSION_TAG | Prefix used to inform the final phase of code generation that unique names are enabled for temporary objects. |
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.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 |
Use to return the fully qualified named of an object in the default physical schema for the data server.
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)
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".
Use to return the short name of an object.
public java.lang.String getObjectShortName( java.lang.String pMode, java.lang.String pObjectName, java.lang.String pLocation) public java.lang.String getObjectShortName( java.lang.String pMode, java.lang.String pObjectName, java.lang.String pLogicalSchemaName, java.lang.String pLocation) public java.lang.String getObjectShortName( java.lang.String pMode, java.lang.String pObjectName, java.lang.String pLogicalSchemaName, java.lang.String pContextName, java.lang.String pLocation)
Returns the object name without the schema and catalog prefix, but adds delimiters if necessary.
The pMode parameter indicates the substitution mask to use.
| 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, getObjectShortName always resolves 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:
|
The text can contain the prefixes available for getObjectName() Method, e.g. %INT_PRF, %COL_PRF, %ERR_PRF, %IDX_PRF and so on.
For example, if the logical schema is pointing to Oracle SCOTT schema:
<%= odiRef.getObjectShortName("L", "%COL_PRFEMP", "D") %>
returns
C$EMP
If the local work schema is Oracle technology:
<%= odiRef.getObjectShortName("L", "ABC", "W") %>
produces ABC in the generated code, while
<%= odiRef.getObjectShortName("L", "abc", "W") %>
produces "abc" in the generated code (with double quotes).
Use to return the value of a KM or procedure option.
public java.lang.String getOption(java.lang.String pOptionName) public java.lang.String getUserExit(java.lang.String pOptionName)
Returns the value of a KM or procedure option.
The getUserExit syntax is deprecated and is only kept for compatibility reasons.
| Parameter | Type | Description |
|---|---|---|
| pOptionName | String | String that contains the name of the requested option. |
The value of my MY_OPTION_1 option is <%=odiRef.getOption("MY_OPTION_1")%>
Use to return information about the current package.
public java.lang.String getPackage(java.lang.String pPropertyName)
This method returns information about the current package. The list of available properties is described in the pPropertyName values table.
| 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. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| PACKAGE_GUID | GUID of the package. |
| PACKAGE_NAME | Name of the package |
| <flexfield code> | Value of the flexfield for this package. |
Package <%=odiRef.getPackage("PACKAGE_NAME")%> is running.
Use to return the parent Load Plan step instance of this session.
public java.lang.String getParentLoadPlanStepInstance(java.lang.String pPropertyName)
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.
| 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. |
Step <%=odiRef.getParentLoadPlanStepInstance("STEP_NAME")%> has been executed <%=odiRef.getParentLoadPlanStepInstance("RESTART_ATTEMPTS")%> times
Use to return information about a primary key.
public java.lang.String getPK(java.lang.String pPropertyName)
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.
| 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. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| PACKAGE_GUID | GUID of the primary key. |
| 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. |
The primary key of my table is called: <%=odiRef.getPK("KEY_NAME")%>
Use to return information about the attributes of a primary key.
public java.lang.String getPKColList( java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
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.
| 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. |
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:
|
| CHECK_FLOW | Flow control flag for of the attribute. Valid values are:
|
| CHECK_STAT | Static control flag of the attribute. Valid values are:
|
| 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:
|
| 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. |
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 ")"
Use to return information about a mapping.
public java.lang.String getPop(java.lang.String pPropertyName)
This method returns information about the current mapping. The list of available information is described in the pPropertyName values table.
| 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:
|
| 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.
|
| <flexfield code> | Flexfield value for the mapping. |
| IS_CONCURRENT | Returns 1 if unique names are being used for the temporary objects, 0 otherwise. |
The current mapping is: <%=odiRef.getPop("POP_NAME")%> and runs on the logical schema: <%=odiRef.getInfo("L_SCHEMA_NAME")%>
Use to return information about the previous step executed in the package.
public java.lang.String getPrevStepLog(java.lang.String pPropertyName)
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.
| 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. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| SESS_GUID | GUID 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:
|
| 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.
|
| 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. |
Previous step '<%=odiRef.getPrevStepLog("STEP_NAME")%>' executed
in '<%=odiRef.getPrevStepLog("DURATION")%>' seconds.
Use to return a quoted string.
public java.lang.String getQuotedString(java.lang.String pString)
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.
| Parameter | Type | Description |
|---|---|---|
| Parameter | Type | Description |
| pString | String | String that to be protected with quotes. |
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";
Use to return a schema name from the topology.
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()
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.
| 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:
|
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 |
Use to return a catalog name for the default physical schema from the topology.
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(
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.
| 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:
|
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 |
Use to return information about the current session.
public java.lang.String getSession(java.lang.String pPropertyName)
This method returns information about the current session. The list of available properties is described in the pPropertyName values table.
| 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. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| SESS_GUID | GUID 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. |
The current session is: <%=odiRef.getSession("SESS_NAME")%>
Reserved for future use.
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)
Reserved for future use.
Reserved for future use.
Reserved for future use.
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.
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) public java.lang.String getSrcColList( java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd) public java.lang.String getSrcColList( java.lang.String pStart, java.lang.String pUnMappedPattern, java.lang.String pMappedPattern, java.lang.String pSeparator, java.lang.String pEnd) public java.lang.String getSrcColList( int dsIndex, java.lang.String pStart, java.lang.String pUnMappedPattern, java.lang.String pMappedPattern, java.lang.String pSeparator, java.lang.String pEnd)
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 pPattern parameter is used in the variant getSrcColList(String pStart, String pPattern, String pSeparator, String pEnd), this indicates that the same pPattern value is used as argument for both pUnMappedPattern and pMappedPattern parameters to call the variant getSrcColList(String pStart, String pUnMappedPattern, String pMappedPattern,String pSeparator, String 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.
| Parameter | Type | Description |
|---|---|---|
| dsIndex | 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 value of pPattern is used as an argument for both pUnMappedPattern and pMappedPattern. |
| 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. |
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)
|
| 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:
|
| 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. |
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","")%>
)
Use to return properties for each source table of a mapping. The properties are organized according to a string pattern.
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)
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 | 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. |
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. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| TABLE_GUID | GUID of the 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, if available: Q = Queue, S = Synonym, T = Table, V = View. |
| 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: D = Delimited, F = Fixed. |
| FILE_SEP_FIELD | File field separator in character text (for example, tab character). |
| XFILE_SEP_FIELD | File field separator in escaped text (for example, tab character). Line feed and carriage return is escaped to \r and \n, while unicode characters are escaped to \uXXXX. |
| SFILE_SEP_FIELD | File field separator in hexadecimal (for example, 09 for the tab character). The return string can be in two hexadecimal digits or four hexadecimal digits, and it is two hexadecimal digits while the higher byte of the field separator is 0. |
| FILE_ENC_FIELD | Text delimiter as shown in the datastore editor (two character maximum). |
| FILE_SEP_ROW | File record separator in character text. |
| XFILE_SEP_ROW | File record separator in escape text. Line feed and carriage return is escaped to \r and \n, while unicode characters are escaped to \uXXXX. |
| SFILE_SEP_ROW | File record separator in hexadecimal. Line feed and carriage return is escaped to \r and \n, while unicode characters are escaped to \uXXXX. |
| 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 of the datatstore: DH = Slowly Changing Dimension, DI = Dimension, FA = Fact Table, <empty> = Undefined. |
| IND_JRN | Flag indicating whether the datastore is included in CDC: 1 = Yes, 0 = No. |
| 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.
|
| <flexfield code> | Flexfield value for the current table. |
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
Use to return information about the current step.
public java.lang.String getStep(java.lang.String pPropertyName)
This method returns information about the current step. The list of available information is described in the pPropertyName values table.
| 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. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| SESS_GUID | GUID of the session. |
| 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 |
The current step is: <%=odiRef.getStep("STEP_NAME")%>
Use to return properties for each of the subscribers of a journalized table. The properties are organized according to a string pattern.
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,
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.
| 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. |
The following table lists different parameters values as well as their associated description.
| Parameter Value | Description |
|---|---|
| SUBSCRIBER | Name of the Subscriber |
Here is list of Subscribers: <%=odiRef.getSubscriberList("\nBegin List\n", "- [SUBSCRIBER]", "\n", "\nEnd of List\n")%>
Use to return the system date of the machine running the session in a given format.
public java.lang.String getSysDate() public java.lang.String getSysDate(pDateFormat)
This method returns the system date of the machine running the session.
| 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 https://download.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html |
Current year is: <%=odiRef.getSysDate("y")%>
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.
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)
Allows the retrieval of the fully qualified name of temporary and permanent tables handled by Oracle Data Integrator.
| 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:
|
| 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. |
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 |
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.
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)
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 | 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:
Example: (INS AND UPD) OR TRG The description of valid selectors is provided below. |
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:
|
| 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 |
| 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. |
| Parameter Value | Description |
|---|---|
| INS |
|
| UPD |
|
| TRG |
|
| NULL |
|
| PK |
|
| UK |
|
| REW |
|
| MAP |
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. |
create table TARGET_COPY <%=odiRef.getTargetColList("(", "[COL_NAME] [DEST_DT] null", ", ", ")", "")%>
Use to return the name of the loading or integration table.
public java.lang.String getTableName( java.lang.String pProperty)
This method returns the name of the temporary table used for loading or integration. This name is not qualified.
| Parameters | Type | Description |
|---|---|---|
| pProperty | String | Parameter that indicates the name of the table to retrieve. The list of possible values is:
|
<%= odiRef.getTableName("COLL_SHORT_NAME") %>
Use to return information about the target table of a mapping.
public java.lang.String getTargetTable(java.lang.String pPropertyName)
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.
| 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. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| TABLE_GUID | GUID of the target table. |
| 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.
|
| <flexfield code> | Flexfield value for the current table. |
The current table is: <%=odiRef.getTargetTable("RES_NAME")%>
Use to return information about a temporary index defined for optimizing a join or a filter in a mapping.
public java.lang.String getTemporaryIndex(java.lang.String pPropertyName)
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.
| 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:
|
| EXPRESSION | Expression of the join or filer clause. Use for debug purposes. |
Create <%=odiRef.getTemporaryIndex (" [INDEX_TYPE_CLAUSE] index [FULL_IDX_NAME] on [FULL_TABLE_NAME] " )%><%=odiRef.getTemporaryIndexColList("(", "[COL_NAME]", ", ", ")")%>
Use to return information about the columns of a temporary index for a mapping.
public java.lang.String getTemporaryIndexColList(java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
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.
| 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. |
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. |
Create <%=odiRef.getTemporaryIndex (" [INDEX_TYPE_CLAUSE] index [FULL_IDX_NAME] on [FULL_TABLE_NAME] " )%><%=odiRef.getTemporaryIndexColList("(", "[COL_NAME]", ", ", ")")%>
Use to return information about the user running the current session.
public java.lang.String getUser(java.lang.String pPropertyName)
This method returns information about the user executing the current session. The list of available properties is described in the pPropertyName values table.
| 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. This parameter is deprecated, and included for 11g compatibility only. The ID property works if the repository is in 11g compatibility mode, but an error message will be returned if the repository is in 12c mode. |
| USER_GUID | GUID of the user. |
| USER_NAME | User name |
| IS_SUPERVISOR | Boolean flag indicating if the user is supervisor (1) or not (0). |
This execution is performed by <%=odiRef.getUser("USER_NAME")%>
Use to return the current version of ODI.
public java.lang.String getVersion()
This method returns the full version number for the running ODI installation.
The KM text:
This execution is performed in <%=odiRef.getVersion()%>
would return:
This execution is performed in 12.1.3.0.0
Use to return whether if the current datastore has a primary key.
public java.lang.Boolean hasPK()
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.
<% if (odiRef.hasPK()) { %>
There is a PK :
<%=odiRef.getPK("KEY_NAME")%> : <%=odiRef.getPKColList("{",
"\u0022[COL_NAME]\u0022", ", ", "}")%>
<% } else {%>
There is NO PK.
<% } %>
Use to return whether a column attribute or comment is changed.
public java.lang.Boolean isColAttrChanged(java.lang.String pPropertyName)
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.
| 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. |
<% if (odiRef.IsColAttrChanged("DEFAULT") ) { %>
/* Column default attribute has changed. */
<% } %>
Use to check if the current version of ODI is compatible with the provided version. Version VER1 is compatible with version VER2 if VER1 > VER2 (for example, 12.1.3.0.0 > 11.1.1.7.0).
public boolean isVersionCompatible(java.lang.String compatibleVersion)
This method returns true if the given version is compatible with the current version of the product. Version VER1 is compatible with version VER2 if VER1 > VER2 (for example, 12.1.3.0.0 > 11.1.1.7.0).
The KM text:
This version is compatible to 11.1.1.7.0: <%=odiRef.isVersionCompatible("11.1.1.7.0") %>
would return:
This version is compatible to 11.1.1.7.0: true
Use to move to the next alternate key for a datastore.
public java.lang.Boolean nextAK()
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.
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")%>
<% } %>
Use to move to the next condition for a datastore.
public java.lang.Boolean nextCond()
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.
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")%>
<% } %>
Use to move to the next foreign key for a datastore.
public java.lang.Boolean nextFK()
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.
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")%>
<% } %>
Use to set the number of inserted, updated, deleted or erroneous rows for the current task.
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)
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.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)
Use to set the name of the loading or integration table.
public java.lang.Void setTableName( java.lang.String pProperty, java.lang.String pTableName)
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 | Type | Description |
|---|---|---|
| pProperty | String | Parameter that indicates the table name to set. The list of possible values is:
|
| pTableName | String | New name for the temporary table. |
<% odiRef.setTableName("COLL_SHORT_NAME", "C" + getInfo("I_SRC_SET")) %>
<% odiRef.setTableName("COLL_SHORT_NAME", odiRef.getOption("Flow # ") + odiRef.getTable("ID")) %>
Use to set the name of a session task in a Knowledge Module, Procedure, or action.
public java.lang.String setTaskName( java.lang.String taskName)
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 | 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. |
<$=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", ",", ".")%>" ) $>