| Oracle® Fusion Middleware Knowledge Module Developer's Guide for Oracle Data Integrator 11g Release 1 (11.1.1) Part Number E12645-03 |
|
|
PDF · Mobi · ePub |
| Oracle® Fusion Middleware Knowledge Module Developer's Guide for Oracle Data Integrator 11g Release 1 (11.1.1) Part Number E12645-03 |
|
|
PDF · Mobi · ePub |
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.
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.
Usage
public java.lang.String getAK(java.lang.String pPropertyName)
Description
This method returns information relative to the alternate key of a datastore during a check procedure. It is only accessible from a Check Knowledge Module if the current task is tagged "alternate key".
In an action, this method returns information related to the alternate key currently handled by the DDL command.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String containing the name of the requested property. |
The following table lists the different possible values for pPropertyName.
| Parameter Values | Description |
|---|---|
| ID | Internal number of the AK constraint. |
| KEY_NAME | Name of the alternate key |
| MESS | Error message relative to the constraint of the alternate key |
| FULL_NAME | Full name of the AK generated with the local object mask. |
| <flexfield code> | Value of the flexfield for this AK. |
Examples
The alternate key of my table is named: <%=odiRef.getAK("KEY_NAME")%>
Use to return information about the columns of an alternate key.
Usage
public java.lang.String getAKColList( java.lang.String pStart, java.lang.String pPattern,java.lang.String pEnd) java.lang.String pSeparator, java.lang.String pEnd)
Alternative syntax:
public java.lang.String getAKColList( java.lang.String pPattern, java.lang.String pSeparator)
Description
Returns a list of columns 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 column 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 columns of the alternate key handled by the DDL command, ordered by their position in the key.
In the alternative syntax, any parameters not set are set to an empty string.
Parameters
| Parameters | Type | Description |
|---|---|---|
| pStart | String | This sequence marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of attributes that can be used in a pattern is detailed in the Pattern Attributes List below. Each attribute occurrence in the pattern sequence is replaced with its value. The attributes must be between brackets. ([ and]) Example «My string [COL_NAME] is a column» |
| pSeparator | String | This parameter separates each pattern from its predecessor. |
| pEnd | String | This sequence marks the end of the string to generate. |
Pattern Attributes List
The following table lists the different values of the parameters as well as their associated description.
| Parameter Value | Description |
|---|---|
| Parameter value | Description |
| I_COL | Column internal identifier |
| COL_NAME | Name of the key column |
| COL_HEADING | Header of the key column |
| COL_DESC | Column description |
| POS | Position of the column |
| LONGC | Length (Precision) of the column |
| SCALE | Scale of the column |
| FILE_POS | Beginning position of the column (fixed file) |
| BYTES | Number of physical bytes of the column |
| FILE_END_POS | End of the column (FILE_POS + BYTES) |
| IND_WRITE | Write right flag of the column |
| COL_MANDATORY | Mandatory character of the column:
|
| CHECK_FLOW | Flow control flag of the column:
|
| CHECK_STAT | Static control flag of the column:
|
| COL_FORMAT | Logical format of the column |
| COL_DEC_SEP | Decimal symbol for the column |
| REC_CODE_LIST | List of the record codes retained for the column |
| COL_NULL_IF_ERR | Processing flag for the column:
|
| DEF_VALUE | Default value for the column |
| EXPRESSION | Not used |
| CX_COL_NAME | Not used |
| ALIAS_SEP | Grouping symbol used for the alias (from the technology) |
| SOURCE_DT | Code of the column's datatype. |
| SOURCE_CRE_DT | Create table syntax for the column's datatype. |
| SOURCE_WRI_DT | Create table syntax for the column's writable datatype. |
| DEST_DT | Code of the column's datatype converted to a datatype on the target technology. |
| DEST_CRE_DT | Create table syntax for the column's datatype converted to a datatype on the target technology. |
| DEST_WRI_DT | Create table syntax for the column's writable datatype converted to a datatype on the target technology. |
| SCD_COL_TYPE | Behavior defined for the Slowly Changing Dimensions for this column in the data model. |
| <flexfield code> | Flexfield value for the current column. |
Examples
If the CUSTOMER table has an alternate key AK_CUSTOMER (CUST_ID, CUST_NAME) and you want to generate the following code:
create table T_AK_CUSTOMER (CUST_ID numeric(10) not null, CUST_NAME varchar(50) not null)
You can use the following code:
create table T_<%=odiRef.getAK("KEY_NAME")%>
<%=odiRef.getAKColList("(", "[COL_NAME] [DEST_CRE_DT] not null", ", ", ")")%>
Explanation: the getAKColList function will be used to generate the (CUST_ID numeric(10) not null, CUST_NAME varchar(50) not null) part, which starts and stops with a parenthesis and repeats the pattern (column, a data type, and not null) separated by commas for each column 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 column 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 a catalog name from the topology.
Usage
public java.lang.String getCatalogName( java.lang.String pLogicalSchemaName, java.lang.String pLocation) public java.lang.String getCatalogName( java.lang.String pLogicalSchemaName, java.lang.String pContextCode, pContextCode, java.lang.String pLocation) public java.lang.String getCatalogName( java.lang.String pLocation) public java.lang.String getCatalogName()
Description
Allows you to retrieve the name of a physical data catalog or work catalog, from its logical schema.
If the first syntax is used, the returned catalog name matches the current context.
If the second syntax is used, the returned catalog name is that of the context specified in the pContextCode parameter.
The third syntax returns the name of the data catalog (D) or work catalog (W) for the current logical schema in the current context.
The fourth syntax returns the name of the data catalog (D) for the current logical schema in the current context.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pLogicalSchemaName | String | Name of the logical schema |
| pContextCode | String | Code of the enforced context of the schema |
| pLocation | String | The valid values are:
|
Examples
If you have defined the physical schema Pluton.db_odi.dbo
| Data catalog: | db_odi |
| Data schema: | dbo |
| Work catalog: | tempdb |
| Work schema: | temp_owner |
that you have associated with this physical schema: MSSQL_ODI in the context CTX_DEV
| The Call To | Returns |
|---|---|
| <%=odiRef.getCatalogName("MSSQL_ODI", "CTX_DEV", "W")%> | tempdb |
| <%=odiRef.getCatalogName("MSSQL_ODI", "CTX_DEV", "D")%> | db_odi |
Use to return a catalog name for the default physical schema from the topology.
Usage
public java.lang.String getCatalogNameDefaultPSchema( java.lang.String pLogicalSchemaName, java.lang.String pLocation) public java.lang.String getCatalogNameDefaultPSchema( java.lang.String pLogicalSchemaName, java.lang.String pContextCode, java.lang.String pLocation) public java.lang.String getCatalogNameDefaultPSchema( java.lang.String pLocation) public java.lang.String getCatalogNameDefaultPSchema()
Description
Allows you to retrieve the name of the default physical data catalog or work catalog for the data server to which is associated the physical schema corresponding to the tuple (logical schema, context). If no context is specified, the current context is used. If no logical schema name is specified, then the current logical schema is used. If no pLocation is specified, then the data catalog is returned.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pLogicalSchemaName | String | Name of the logical schema |
| pContextCode | String | Code of the enforced context of the schema |
| pLocation | String | The valid values are:
|
Examples
If you have defined the physical schema Pluton.db_odi.dbo
| Data catalog: | db_odi |
| Data schema: | dbo |
| Work catalog: | tempdb |
| Work schema: | temp_odi |
| Default Schema | Yes |
that you have associated with this physical schema: MSSQL_ODI in the context CTX_DEV, and Pluton.db_doc.doc
| Data catalog: | db_doc |
| Data schema: | doc |
| Work catalog: | tempdb |
| Work schema: | temp_doc |
| Default Schema | No |
that you have associated with this physical schema: MSSQL_DOC in the context CTX_DEV.
| The Call To | Returns |
|---|---|
| <%=odiRef.getCatalogNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "W")%> | tempdb |
| <%=odiRef.getCatalogNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "D")%> | db_odi |
Use to return information about a condition.
Usage
public java.lang.String getCK(java.lang.String pPropertyName)
Description
This method returns information relative to a condition of a datastore during a check procedure. It is accessible from a Check Knowledge Module only if the current task is tagged as "condition".
In an action, this method returns information related to the check constraint currently handled by the DDL command.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | Current string containing the name of the requested property. |
The following table lists the different values accepted by pPropertyName:
| Parameter Value | Description |
|---|---|
| ID | Internal number of the check constraint. |
| COND_ALIAS | Alias of the table used in the SQL statement |
| COND_NAME | Name of the condition |
| COND_TYPE | Type of the condition |
| COND_SQL | SQL statement of the condition |
| MESS | Error message relative to the check constraint |
| FULL_NAME | Full name of the check constraint generated with the local object mask. |
| COND_SQL_DDL | SQL statement of the condition with no table alias. |
| <flexfield code> | Flexfield value for this check constraint. |
Examples
The current condition is called: <%=snpRep.getCK("COND_NAME")%>
insert into MY_ERROR_TABLE
select *
from MY_CHECKED_TABLE
where (not (<%=odiRef.getCK("COND_SQL")%>))
Use to return the default value of a mapped column.
Usage
public java.lang.String getColDefaultValue()
Description
Returns the default value of the target column 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 column definition. Depending on the column type, this value should be protected with quotes.
Parameters
None.
Examples
The default value of my target column is '+ 'odiRef.getColDefaultValue()'
Use to return properties for each column from a filtered list of columns. The properties are organized according to a string pattern.
Usage
public java.lang.String getColList( java.lang.int pDSIndex, java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd, java.lang.String pSelector)
Alternative syntaxes:
public java.lang.String getColList( java.lang.int pDSIndex, java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
public java.lang.String getColList( java.lang.int pDSIndex, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pSelector)
public java.lang.String getColList( java.lang.int pDSIndex, java.lang.String pPattern, java.lang.String pSeparator)
Description
Returns a list of columns and expressions for a given dataset. The columns list depends on the phase during which this method is called.
In IKMs only, In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Note:
The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.The pPattern parameter is interpreted and then repeated for each element of the list (selected according to pSelector parameter) and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.
In the alternative syntax, any parameters not set are set to an empty string.
Note that this method automatically generates lookups with no specific code required.
Loading (LKM)
All active mapping expressions that are executed in the current source set, as well as all the columns from the current source set 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 interface, the three journalizing pseudo columns JRN_FLAG, JRN_DATE, and JRN_SUBSCRIBER are added as columns of the journalized source datastore.
Integration (IKM)
All current active mapping expressionsin the current interface appear in the list.
The list contains one element for each column that is loaded in the target table of the current interface. 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 interface, and it is located in the staging area, the three journalizing pseudo columns JRN_FLG, JRN_DATE,and JRN_SUBSCRIBER are added as columns of the journalized source datastore.
Check (CKM)
All the columns of the target table (with static or flow control) appear in this list.
To distinguish columns mapped in the current interface, you must use the MAP selector.
Actions
All the columns of the table handles by the DDL command appear in this list.
In the case of modified, added or deleted columns, the NEW and OLD selectors are used to retrieve either the new version of the old version of the modified column being processed by the DDL command. The list is sorted by POS, FILE_POS when the table loaded is not temporary.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
| pStart | String | This sequence marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of the attributes usable in a pattern is detailed in the Pattern Attributes List below. Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ]) Example «My string [COL_NAME] is a column» |
| 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. |
Pattern Attributes List
The following table lists different parameters values as well as their associated description.
| Parameter Value | Description |
|---|---|
| I_COL | Internal identifier of the column |
| COL_NAME | Name of the column |
| COL_HEADING | Header of the column |
| COL_DESC | Description of the column |
| POS | Position of the column |
| LONGC | Column length (Precision) |
| SCALE | Scale of the column |
| FILE_POS | Beginning (index) of the column |
| BYTES | Number of physical bytes in the column |
| FILE_END_POS | End of the column (FILE_POS + BYTES) |
| IND_WRITE | Write right flag of the column |
| COL_MANDATORY | Mandatory character of the column. Valid values are:
|
| CHECK_FLOW | Flow control flag of the column. Valid values are:
|
| CHECK_STAT | Static control flag of the column. Valid values are:
|
| COL_FORMAT | Logical format of the column |
| COL_DEC_SEP | Decimal symbol of the column |
| REC_CODE_LIST | List of the record codes retained in the column |
| COL_NULL_IF_ERR | Processing flag of the column. Valid values are:
|
| DEF_VALUE | Default value of the column |
| EXPRESSION | Text of the expression executed on the source (expression as typed in the mapping or column name making an expression executed on the staging area). |
| CX_COL_NAME | Computed name of the column 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 column's datatype. |
| SOURCE_CRE_DT | Create table syntax for the column's datatype. |
| SOURCE_WRI_DT | Create table syntax for the column's writable datatype. |
| DEST_DT | Code of the column's datatype converted to a datatype on the target technology. |
| DEST_CRE_DT | Create table syntax for the column's datatype converted to a datatype on the target technology. |
| DEST_WRI_DT | Create table syntax for the column's writable datatype converted to a datatype on the target technology. |
| SCD_COL_TYPE | Behavior defined for the Slowly Changing Dimensions for this column in the data model. |
| MANDATORY_CLAUSE | Returns NOT NULL is the column 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 column returned by the driver. |
| <flexfield code> | Flexfield value for the current column. |
Selectors Description
| Parameter Value | Description |
|---|---|
| INS |
|
| UPD |
|
| TRG |
|
| NULL |
|
| PK |
|
| UK |
|
| REW |
|
| UD1 |
|
| UD2 |
|
| UD3 |
|
| UD4 |
|
| UD5 |
|
| MAP |
Flow control: All columns of the target table loaded with expressions in the current interface Static control: All columns of the target table |
| SCD_SK | LKM, CKM, IKM: All columns marked SCD Behavior: Surrogate Key in the data model definition. |
| SCD_NK | LKM, CKM, IKM: All columns marked SCD Behavior: Natural Key in the data model definition. |
| SCD_UPD | LKM, CKM, IKM: All columns marked SCD Behavior: Overwrite on Change in the data model definition. |
| SCD_INS | LKM, CKM, IKM: All columns marked SCD Behavior: Add Row on Change in the data model definition. |
| SCD_FLAG | LKM, CKM, IKM: All columns marked SCD Behavior: Current Record Flag in the data model definition. |
| SCD_START | LKM, CKM, IKM: All columns marked SCD Behavior: Starting Timestamp in the data model definition. |
| SCD_END | LKM, CKM, IKM: All columns marked SCD Behavior: Ending Timestamp in the data model definition. |
| NEW | Actions: the column added to a table, the new version of the modified column of a table. |
| OLD | Actions: The column dropped from a table, the old version of the modified column of a table. |
| WS_INS | SKM: The column is flagged as allowing INSERT using Data Services. |
| WS_UPD | SKM: The column is flagged as allowing UDATE using Data Services. |
| WS_SEL | SKM: The column 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 columns mapped on the source in the interface are returned. As a consequence, the result could be incorrect depending on the interface. For example, for the UK selector, the columns of the key that are not mapped or that are not executed on the source will not be returned with the selector.Examples
If the CUSTOMER table contains the columns (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 (column, data type, and null) separated by commas for each column. 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 column. 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 column (with no selection)
Use to return information about a specific column handled by an action.
Usage
public java.lang.String getColumn( java.lang.String pPattern, java.lang.String pSelector) public java.lang.String getColumn( java.lang.String pPattern)
Description
In an action, returns information on a column being handled by an the action.
Parameters
| Parameters | Type | Description |
|---|---|---|
| pPattern | String | Pattern of values rendered for the column.
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 column» |
| 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. |
Pattern Attributes List
The following table lists different parameters values as well as their associated description.
| Parameter Value | Description |
|---|---|
| I_COL | Internal identifier of the column |
| COL_NAME | Name of the column |
| COL_HEADING | Header of the column |
| COL_DESC | Description of the column |
| POS | Position of the column |
| LONGC | Column length (Precision) |
| SCALE | Scale of the column |
| FILE_POS | Beginning (index) of the column |
| BYTES | Number of physical bytes in the column |
| FILE_END_POS | End of the column (FILE_POS + BYTES) |
| IND_WRITE | Write right flag of the column |
| COL_MANDATORY | Mandatory character of the column. Valid values are:
|
| CHECK_FLOW | Flow control flag of the column. Valid values are:
|
| CHECK_STAT | Static control flag of the column. Valid values are:
|
| COL_FORMAT | Logical format of the column |
| COL_DEC_SEP | Decimal symbol of the column |
| REC_CODE_LIST | List of the record codes retained in the column |
| COL_NULL_IF_ERR | Processing flag of the column. Valid values are:
|
| DEF_VALUE | Default value of the column |
| EXPRESSION | Text of the expression executed on the source (expression as typed in the mapping or column name making an expression executed on the staging area). |
| CX_COL_NAME | Computed name of the column 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 column's datatype. |
| SOURCE_CRE_DT | Create table syntax for the column's datatype. |
| SOURCE_WRI_DT | Create table syntax for the column's writable datatype. |
| DEST_DT | Code of the column's datatype converted to a datatype on the target technology. |
| DEST_CRE_DT | Create table syntax for the column's datatype converted to a datatype on the target technology. |
| DEST_WRI_DT | Create table syntax for the column's writable datatype converted to a datatype on the target technology. |
| SCD_COL_TYPE | Behavior defined for the Slowly Changing Dimensions for this column in the data model. |
| MANDATORY_CLAUSE | Returns NOT NULL if the column 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 column. |
Use to return information about the current context.
Usage
public java.lang.String getContext(java.lang.String pPropertyName)
Description
This method returns information about to the current execution context.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String containing the name of the requested property. |
The following table lists the different possible values for pPropertyName.
| Parameter Value | Description |
|---|---|
| ID | Internal ID of the context. |
| CTX_NAME | Name of the context. |
| CTX_CODE | Code of the context. |
| CTX_DEFAULT | Returns 1 for the default context, 0 for the other contexts. |
| <flexfield code> | Flexfield value for this reference. |
Examples
Current Context = <%=getContext("CTX_NAME")%>
Use to return information about a given dataset of an interface.
Usage
public java.lang.String getDataSet( java.lang.Int pDSIndex, java.lang.String pPropertyName)
Description
Retrieves information about for a given dataset of an interface.
In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
The following table lists the different possible values for pPropertyName.
| Parameter Value | Description |
|---|---|
| OPERATOR | Operator that applies to the selected dataset. For the first dataset, an empty value is returned. |
| NAME | Dataset Name. |
| HAS_JRN | Returns "1" if the dataset one journalized datastore, "0" otherwise. |
Examples
<%for (int i=0; i < odiRef.getDataSetCount(); i++){%><%=odiRef.getDataSet(i, "Operator")%>select <%=odiRef.getPop("DISTINCT_ROWS")%> <%=odiRef.getColList(i,"", "[EXPRESSION] [COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%> from <%=odiRef.getFrom(i)%>where <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %> JRN_FLAG <> 'D '<%} else {%> (1=1) <% } %><%=odiRef.getJoin(i)%><%=odiRef.getFilter(i)%><%=odiRef.getJrnFilter(i)%><%=odiRef.getGrpBy(i)%><%=odiRef.getHaving(i)%><%}%>
Use to return the number of datasets of an interface.
Usage
public java.lang.Int getDataSetCount()
Description
Returns the number of datasets of an interface.
Parameters
None
Examples
<%for (int i=0; i < odiRef.getDataSetCount(); i++){%><%=odiRef.getDataSet(i, "Operator")%>select <%=odiRef.getPop("DISTINCT_ROWS")%> <%=odiRef.getColList(i,"", "[EXPRESSION] [COL_NAME]", ",\n\t", "", "((INS and !TRG) and REW)")%> from <%=odiRef.getFrom(i)%>where <% if (odiRef.getDataSet(i, "HAS_JRN").equals("1")) { %> JRN_FLAG <> 'D '<%} else {%> (1=1) <% } %><%=odiRef.getJoin(i)%><%=odiRef.getFilter(i)%><%=odiRef.getJrnFilter(i)%><%=odiRef.getGrpBy(i)%><%=odiRef.getHaving(i)%><%}%>
Use to return the syntax creating a column of a given datatype.
Usage
public java.lang.String getDataType( java.lang.String pDataTypeName, java.lang.String pDataTypeLength, java.lang.String pDataTypePrecision)
Description
Returns the creation syntax of the following SQL data types: varchar, numeric or date according to the parameters associated to the source or target technology.
Parameters
| Parameters | Type | Description |
|---|---|---|
| Parameter | Type | Description |
| pDataTypeName | String | Name of the data type as listed in the table below |
| pDataTypeLength | String | Length of the data type |
| pDataTypePrecision | String | Precision of the data type |
The following table lists all possible values for pDataTypeName.
| Parameter Value | Description |
|---|---|
| SRC_VARCHAR | Returns the syntax to the source data type varchar |
| SRC_NUMERIC | Returns the syntax to the source data type numeric |
| SRC_DATE | Returns the syntax to the source data type date |
| DEST_VARCHAR | Returns the syntax to the target data type varchar |
| DEST_NUMERIC | Returns the syntax to the target data type numeric |
| DEST_DATE | Returns the syntax to the target data type date |
Examples
Given the following syntax for these technologies:
| Technology | Varchar | Numeric | Date |
|---|---|---|---|
| Oracle | varchar2(%L) | number(%L,%P) | date |
| Microsoft SQL Server | varchar(%L) | numeric(%L,%P) | datetime |
| Microsoft Access | Text(%L) | double | datetime |
Here are some examples of call to getDataType:
| Call | Oracle | SQL Server | Access |
|---|---|---|---|
<%=odiRef.getDataType("DEST_VARCHAR", "10", "")%> |
varchar2(10) | varchar(10) | Text(10) |
<%=odiRef.getDataType("DEST_VARCHAR", "10", "5")%> |
varchar2(10) | varchar(10) | Text(10) |
<%=odiRef.getDataType("DEST_NUMERIC", "10", "")%> |
number(10) | numeric(10) | double |
<%=odiRef.getDataType("DEST_NUMERIC", "10", "2")%> |
number(10,2) | numeric(10,2) | double |
<%=odiRef.getDataType("DEST_NUMERIC", "", "")%> |
number | numeric | double |
<%=odiRef.getDataType("DEST_DATE", "", "")%> |
date | datetime | datetime |
<%=odiRef.getDataType("DEST_DATE", "10", "2")%> |
date | datetime | datetime |
Use to return the entire WHERE clause section generated for the filters of an interface.
Usage
public java.lang.String getFilter(java.lang.Int pDSIndex)
Description
Returns the SQL filters sequence (on the source while loading, on the staging area while integrating) for a given dataset.
In IKMs only, In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Note:
The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
None
Examples
insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>
Use to return properties for each filter of an interface. The properties are organized according to a string pattern.
Usage
public java.lang.String getFilterList( java.lang.Int pDSIndex, java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
Alternative syntax:
public java.lang.String getFilterList( java.lang.Int pDSIndex, java.lang.String pPattern, java.lang.String pSeparator)
Description
Returns a list of occurrences of the SQL filters of a given dataset of an interface.
In IKMs only, In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Note:
The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.The parameter pPattern is interpreted and repeated for each element of the list and separated from its predecessor with parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.
This list contains an element for each filter expression executed on the source or target (depending on the Knowledge Module in use).
In the alternative syntax, any parameters not set are set to an empty string.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
| pStart | String | This sequence marks the beginning of the string to generate. |
| pPattern | String | The pattern will be repeated for each occurrence of the list.
The list of possible in a list is available in the Pattern Attributes List below. Each attribute occurrence in the pattern string is substituted with its value. Attributes must be between brackets ([ and ]) Example «My string [COL_NAME] is a column» |
| pSeparator | String | This parameter is used to separate a pattern from its predecessor. |
| pEnd | String | This sequence marks the end of the string to generate. |
Pattern Attributes List
The following table lists the different values of the parameters as well as the associated description.
| Parameter Value | Description |
|---|---|
| ID | Filter internal identifier |
| EXPRESSION | Text of the filter expression. |
Examples
insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilterList("and ","([EXPRESSION])"," and ","")%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>
Explanation: the getFilterList function will be used to generate the filter of the SELECT clause that must begin with "and" and repeats the pattern (expression of each filter) separated with "and" for each filter. Thus
The first parameter "and" of the function indicates that we want to start the string with the string "and"
the second parameter "([EXPRESSION])" indicates that we want to repeat this pattern for each filter. The keywords [EXPRESSION] references a valid keyword of the table Pattern Attribute List
the third parameter " and " indicates that we want to separate each interpreted occurrence of the pattern with the string "and ".
the fourth parameter "" of the function indicates that we want to end the string with no specific character.
Use to return information about a foreign key.
Usage
public java.lang.String getFK(java.lang.String pPropertyName)
Description
This method returns information relative to the foreign key (or join or reference) of a datastore during a check procedure. It is accessible from a Knowledge Module only if the current task is tagged as a "reference".
In an action, this method returns information related to the foreign key currently handled by the DDL command.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String containing the name of the requested property. |
The following table lists the different possible values for pPropertyName.
| Parameter Value | Description |
|---|---|
| ID | Internal number of the reference constraint. |
| FK_NAME | Name of the reference constraint. |
| FK_TYPE | Type of the reference constraint. |
| FK_ALIAS | Alias of the reference table (only used in case of a complex expression) |
| PK_ALIAS | Alias of the referenced table (only used in case of a complex expression) |
| ID_TABLE_PK | Internal number of the referenced table. |
| PK_I_MOD | Number of the referenced model. |
| PK_CATALOG | Catalog of the referenced table in the current context. |
| PK_SCHEMA | Physical schema of the referenced table in the current context. |
| PK_TABLE_NAME | Name of the referenced table. |
| COMPLEX_SQL | Complex SQL statement of the join clause (if appropriate). |
| MESS | Error message of the reference constraint |
| FULL_NAME | Full name of the foreign key generated with the local object mask. |
| <flexfield code> | Flexfield value for this reference. |
Examples
The current reference key of my table is called: <%=odiRef.getFK("FK_NAME")%>. It references the table <%=odiRef.getFK("PK_TABLE_NAME")%> that is in the schema <%=odiRef.getFK("PK_SCHEMA")%>
Use to return information about the columns of a foreign key.
Usage
public java.lang.String getFKColList(java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
Alternative syntax:
public java.lang.String getFKColList( java.lang.String pPattern, java.lang.String pSeparator)
Description
Returns a list of columns 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 column 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 columns of the foreign key handled by the DDL command, ordered by their position in the key.
In the alternative syntax, any parameters not set are set to an empty string.
Parameters
| Parameter | Type | Description |
|---|---|---|
| Parameter | Type | Description |
| pStart | String | This parameter marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of possible attributes in a pattern is detailed in the Pattern Attributes List below. Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ]) Example «My string [COL_NAME] is a column» |
| pSeparator | String | This parameter separates each pattern from its predecessor. |
| pEnd | String | This parameter marks the end of the string to generate. |
Pattern Attributes List
The following table lists the different values of the parameters as well as the associated description.
| Parameter Value | Description |
|---|---|
| I_COL | Column internal identifier |
| COL_NAME | Name of the column of the key |
| COL_HEADING | Header of the column of the key |
| COL_DESC | Description of the column of the key |
| POS | Position of the column of the key |
| LONGC | Length (Precision) of the column of the key |
| SCALE | Scale of the column of the key |
| FILE_POS | Beginning (index) of the column |
| BYTES | Number of physical octets of the column |
| FILE_END_POS | End of the column (FILE_POS + BYTES) |
| IND_WRITE | Write right flag of the column |
| COL_MANDATORY | Mandatory character of the column. Valid values are:
|
| CHECK_FLOW | Flow control flag of the column. Valid values are:
|
| CHECK_STAT | Static control flag of the column. Valid values are:
|
| COL_FORMAT | Logical format of the column |
| COL_DEC_SEP | Decimal symbol for the column |
| REC_CODE_LIST | List of the record codes for the column |
| COL_NULL_IF_ERR | Column processing flag. Valid values are:
|
| DEF_VALUE | Default value of the column |
| EXPRESSION | Not used |
| CX_COL_NAME | Not used |
| ALIAS_SEP | Separator used for the alias (from the technology) |
| SOURCE_DT | Code of the column's datatype. |
| SOURCE_CRE_DT | Create table syntax for the column's datatype. |
| SOURCE_WRI_DT | Create table syntax for the column's writable datatype. |
| DEST_DT | Code of the column's datatype converted to a datatype on the target technology. |
| DEST_CRE_DT | Create table syntax for the column's datatype converted to a datatype on the target technology. |
| DEST_WRI_DT | Create table syntax for the column's writable datatype converted to a datatype on the target technology. |
| PK_I_COL | Internal identifier of the referenced column |
| PK_COL_NAME | Name of the referenced key column |
| PK_COL_HEADING | Header of the referenced key column |
| PK_COL_DESC | Description of the referenced key column |
| PK_POS | Position of the referenced column |
| PK_LONGC | Length of the referenced column |
| PK_SCALE | Precision of the referenced column |
| PK_FILE_POS | Beginning (index) of the referenced column |
| PK_BYTES | Number of physical octets of the referenced column |
| PK_FILE_END_POS | End of the referenced column (FILE_POS + BYTES) |
| PK_IND_WRITE | Write right flag of the referenced column |
| PK_COL_MANDATORY | Mandatory character of the referenced column. Valid values are:
|
| PK_CHECK_FLOW | Flow control flag of the referenced column. Valid values are:
|
| PK_CHECK_STAT | Static control flag of the referenced column. Valid values are:
|
| PK_COL_FORMAT | Logical format of the referenced column |
| PK_COL_DEC_SEP | Decimal separator for the referenced column |
| PK_REC_CODE_LIST | List of record codes retained for the referenced column |
| PK_COL_NULL_IF_ERR | Processing flag of the referenced column. Valid values are:
|
| PK_DEF_VALUE | Default value of the referenced column |
| SCD_COL_TYPE | Behavior defined for the Slowly Changing Dimensions for this column in the data model. |
| <flexfield code> | Flexfield value for the current column of the referencing table. |
Examples
If the CUSTOMER table references the CITY table on CUSTOMER.COUNTRY_ID = CITY.ID_COUNT and CUSTOMER.CITY_ID = CITY.ID_CIT
the clause:
(CUS.COUNTRY_ID = CITY.ID_COUNT and CUS.CITY_ID = CITY.ID_CIT)
can also be written:
<%=odiRef.getFKColList("(", "CUS.[COL_NAME] = CITY.[PK_COL_NAME]", " and
", ")" )%>
Explanation: the getFKColList function will be used to loop on each column 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 column 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 column 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.
Usage
public java.lang.String getFlexFieldValue(java.lang.String pI_Instance, java.lang.String pI_Object, java.lang.String pFlexFieldCode)
Description
This method returns the value of an Object Instance's Flexfield.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pI_Instance | String | Internal Identifier of the Object Instance, as it appears in the version tab of the object instance window. |
| pI_Object | String | Internal Identifier of the Object type, as it appears in the version tab of the object window for the object type. |
| pPropertyName | String | Flexfield Code which value should be returned. |
Examples
<%=odiRef.getFlexFieldValue("32001","2400","MY_DATASTORE_FIELD")%>
Returns the value of the flexfield MY_DATASTORE_FIELD, for the object instance of type datastore (Internal ID for datastores is 2400), with the internal ID 32001.
Use to return the SQL FROM clause in the given context.
Usage
public java.lang.String getFrom(java.lang.Int pDSIndex)
Description
Allows the retrieval of the SQL string of the FROM in the source SELECT clause for a given dataset. The FROM statement is built from tables and joins (and according to the SQL capabilities of the technologies) that are used in this dataset.
For a technology that supports ISO outer joins and parenthesis, getFrom() could return a string such as:
((CUSTOMER as CUS inner join CITY as CIT on (CUS.CITY_ID = CIT.CITY_ID)) left outer join SALES_PERSON as SP on (CUS.SALES_ID = SP.SALE_ID))
In IKMs only, 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 interface, 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 Interface as Derived Table (Sub-Select) box selected then a sub-select statement will be generated for this temporary source by the getFrom method.
If partitioning is used on source datastores, this method automatically adds the partitioning clauses when returning the object names.
Note that this method automatically generates lookups with no specific code required.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
Examples
insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>
Use to return the entire SQL GROUP BY clause in the given context.
Usage
public java.lang.String getGrpBy(java.lang.Int pDSIndex)
Description
Allows you to retrieve the SQL GROUP BY string (on the "source" during the loading phase, on the staging area during the integration phase) for a given dataset. This statement is automatically computed from the aggregation transformations detected in the mapping expressions.
In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Note:
The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
Examples
insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>
Use to return properties for each GROUP BY clause for a given dataset in an interface. The properties are organized according to a string pattern.
Usage
public java.lang.String getGrpByList( java.lang.Int pDSIndex, java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
Alternative syntax:
public java.lang.String getGrpByList( java.lang.Int pDSIndex, java.lang.String pPattern, java.lang.String pSeparator)
Description
Returns a list of occurrences of SQL GROUP BY for a given dataset of an interface.
In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Note:
The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.The pPattern parameter is interpreted, then repeated for each element of the list and separated from its predecessor with the pSeparator parameter. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.
This list contains an element for each GROUP BY statement on the source or target (according to the Knowledge Module that used it).
In the alternative syntax, any parameters not set are set to an empty string.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
| pStart | String | This parameter marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of possible attributes in a pattern is detailed in the Pattern Attributes List below. Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ]) Example «My string [COL_NAME] is a column» |
| pSeparator | String | This parameter is used to separate each pattern from its predecessor. |
| pEnd | String | This parameter marks the end of the string to be generated. |
Pattern Attributes List
The following table lists the different values of the parameters as well as their associated description.
| Parameter Value | Description |
|---|---|
| ID | Internal identifier of the clause |
| EXPRESSION | Text of the grouping statement |
Examples
insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpByList("group by ", "[EXPRESSION]", " , ", "")%>
<%=odiRef.getHaving()%>
Explanation: the getGrpByList function will be used to generate the group by clause of the select order that must start with "group by" and that repeats a pattern (each grouping expression) separated by commas for each expression.
The first parameter "group by" of the function indicates that we want to start the string with "group by"
The second parameter "[EXPRESSION]" indicates that we want to repeat this pattern for each group by expression. The keyword [EXPRESSION] references a valid keyword of the table Pattern Attributes List
The third parameter ", " indicates that we want to separate the interpreted occurrences of the pattern with a comma.
The fourth parameter "" of the function indicates that we want to end the string with no specific character
Use to return the entire SQL HAVING clause in the given context.
Usage
public java.lang.String getHaving(java.lang.Int pDSIndex)
Description
Allows the retrieval of the SQL statement HAVING (on the source during loading, on the staging area during integration) for a given dataset. This statement is automatically computed from the filter expressions containing detected aggregation functions.
In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Note:
The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
Examples
insert into <%=odiRef.getTable(
"L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>
Use to return properties for each HAVING clause of an interface. The properties are organized according to a string pattern.
Usage
public java.lang.String getHavingList( java.lang.Int pDSIndex, java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
Alternative syntax:
public java.lang.String getHavingList( java.lang.Int pDSIndex, java.lang.String pPattern, java.lang.String pSeparator)
Description
Returns a list of the occurrences of SQL HAVING of a given dataset in an interface.
In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Note:
The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.The parameter pPattern is interpreted and repeated for each element of the list, and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.
This list contains one element for each HAVING expression to execute on the source or target (depends on the Knowledge module that uses it).
In the alternative syntax, any parameters not set are set to an empty string.
Parameters
| Parameters | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
| pStart | String | This parameter marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of authorized attributes in a pattern is detailed in the Pattern Attributes List below. Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ]) Example «My string [COL_NAME] is a column» |
| pSeparator | String | This parameter separates each pattern from its predecessor. |
| pEnd | String | This parameter marks the end of the string to generate. |
Pattern Attributes List
The following table lists the different values of the parameters as well as the associated description.
| Parameter Value | Description |
|---|---|
| Parameter value | Description |
| ID | Internal identifier of the clause |
| EXPRESSION | Text of the having expression |
Examples
insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpByList("group by ", "[EXPRESSION]", " , ", "")%>
<%=odiRef.getHavingList("having ", "([EXPRESSION])", " and ", "")%>
Explanation: The getHavingList function will be used to generate the having clause of the select order that must start with "having" and that repeats a pattern (each aggregated filtered expression) separated by "and" for each expression.
The first parameter "having " of the function indicates that we want to start the string with "having"
The second parameter "([EXPRESSION])" indicates that we want to repeat this pattern for each aggregated filter. The keyword [EXPRESSION] references a valid keyword of the table Pattern Attributes List
The third parameter" and " indicates that we want to separate each interpreted occurrence of the pattern with the string " and ".
The fourth parameter "" of the function indicates that we want to end the string with no specific character
Use to return information about a specific index handled by an action.
Usage
public java.lang.String getIndex(java.lang.String pPropertyName)
Description
In an action, this method returns information related to the index currently handled by the DDL command.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String containing the name of the requested property. |
The following table lists the different possible values for pPropertyName.
| Parameter Value | Description |
|---|---|
| ID | Internal number of the index. |
| KEY_NAME | Name of the index |
| FULL_NAME | Full name of the index generated with the local object mask. |
| <flexfield code> | Value of the flexfield for this index. |
Use to return information about the columns of an index handled by an action.
Usage
public java.lang.String getIndexColList(java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
Description
In an action, this method returns the list of the columns 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 column of the current index.
Parameters
| Parameters | Type | Description |
|---|---|---|
| pStart | String | This sequence marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of attributes that can be used in a pattern is detailed in the Pattern Attributes List below. Each attribute occurrence in the pattern sequence is replaced with its value. The attributes must be between brackets. ([ and ]) Example «My string [COL_NAME] is a column» |
| pSeparator | String | This parameter separates each pattern from its predecessor. |
| pEnd | String | This sequence marks the end of the string to generate. |
Pattern Attributes List
The following table lists the different values of the parameters as well as their associated description.
| Parameter Value | Description |
|---|---|
| I_COL | Column internal identifier |
| COL_NAME | Name of the index column |
| COL_HEADING | Header of the index column |
| COL_DESC | Column description |
| POS | Position of the column |
| LONGC | Length (Precision) of the column |
| SCALE | Scale of the column |
| FILE_POS | Beginning position of the column (fixed file) |
| BYTES | Number of physical bytes of the column |
| FILE_END_POS | End of the column (FILE_POS + BYTES) |
| IND_WRITE | Write right flag of the column |
| COL_MANDATORY | Mandatory character of the column. Valid values are:
|
| CHECK_FLOW | Flow control flag for of the column. Valid values are:
|
| CHECK_STAT | Static control flag of the column. Valid values are:
|
| COL_FORMAT | Logical format of the column |
| COL_DEC_SEP | Decimal symbol for the column |
| REC_CODE_LIST | List of the record codes retained for the column |
| COL_NULL_IF_ERR | Processing flag for the column. Valid values are:
|
| DEF_VALUE | Default value for the column |
| EXPRESSION | Not used |
| CX_COL_NAME | Not used |
| ALIAS_SEP | Grouping symbol used for the alias (from the technology) |
| SOURCE_DT | Code of the column's datatype. |
| SOURCE_CRE_DT | Create table syntax for the column's datatype. |
| SOURCE_WRI_DT | Create table syntax for the column's writable datatype. |
| DEST_DT | Code of the column's datatype converted to a datatype on the target technology. |
| DEST_CRE_DT | Create table syntax for the column's datatype converted to a datatype on the target technology. |
| DEST_WRI_DT | Create table syntax for the column's writable datatype converted to a datatype on the target technology. |
| SCD_COL_TYPE | Behavior defined for the Slowly Changing Dimensions for this column in the data model. |
| <flexfield code> | Flexfield value for the current column. |
Use to return information about the current task.
Usage
public java.lang.String getInfo(java.lang.String pPropertyName)
Description
This method returns information about the current task. The list of available information is described in the pPropertyName values table.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String containing the name of the requested property. |
The following table lists the different values possible for pPropertyName:
| Parameter Value | Description |
|---|---|
| I_SRC_SET | Internal identifier of the current Source Set if the task belongs to a Loading Knowledge Module |
| SRC_SET_NAME | Name of the current Source Set if the task belongs to a Loading Knowledge Module |
| COLL_NAME | Name of the current loading resource (C$) if the task belongs to a Loading Knowledge Module |
| INT_NAME | Name of the current integration resource (I$) if the task belongs to a string Loading, Integration or Check Knowledge Module. |
| ERR_NAME | Name of the current error resource (E$) if the task is part of a Loading, Integration or Check Knowledge Module |
| TARG_NAME | Name of the target resource if the task is part of a Loading, Integration or Check Knowledge Module |
| SRC_CATALOG | Name of the data catalog in the source environment |
| SRC_SCHEMA | Name of the data schema in the source environment |
| SRC_WORK_CATALOG | Name of the work catalog in the source environment |
| SRC_WORK_SCHEMA | Name of the work schema in the source environment |
| DEST_CATALOG | Name of the data catalog in the target environment |
| DEST_SCHEMA | Name of the data schema in the target environment |
| DEST_WORK_CATALOG | Name of the work catalog in the target environment |
| DEST_WORK_SCHEMA | Name of the work schema in the target environment |
| SRC_TECHNO_NAME | Name of the source technology |
| SRC_CON_NAME | Name of the source connection |
| SRC_DSERV_NAME | Name of the data server of the source machine |
| SRC_CONNECT_TYPE | Connection type of the source machine |
| SRC_IND_JNDI | JNDI URL flag |
| SRC_JAVA_DRIVER | Name of the JDBC driver of the source connection |
| SRC_JAVA_URL | JDBC URL of the source connection |
| SRC_JNDI_AUTHENT | JNDI authentication type |
| SRC_JNDI_PROTO | JNDI source protocol |
| SRC_JNDI_FACTORY | JNDI source Factory |
| SRC_JNDI_URL | Source JNDI URL |
| SRC_JNDI_RESSOURCE | Accessed source JNDI resource |
| SRC_JNDI_USER | User name for JNDI authentication on the source. |
| SRC_JNDI_ENCODED_PASS | Encrypted password for JNDI authentication on the source. |
| SRC_USER_NAME | User name of the source connection |
| SRC_ENCODED_PASS | Encrypted password of the source connection |
| SRC_FETCH_ARRAY | Size of the source array fetch |
| SRC_BATCH_UPDATE | Size of the source batch update |
| SRC_EXE_CHANNEL | Execution canal of the source connection |
| SRC_COL_ALIAS_WORD | Term used to separated the columns 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 column 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 columns 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 column in the target technology |
| DEST_MAX_TAB_NAME_LEN | Maximum number of characters of the table name on the target technology |
| DEST_REM_OBJ_PATTERN | Substitution model for a remote object on the target technology |
| DEST_LOC_OBJ_PATTERN | Substitution model for a local object name on the target technology |
| CT_ERR_TYPE | Error type (F: Flow, S: Static). Applies only in the case of a Check Knowledge Module |
| CT_ERR_ID | Error identifier (Table # for a static control or interface number for flow control. Applies only in the case of a Check Knowledge Module |
| CT_ORIGIN | Name that identifies the origin of an error (Name of a table for static control, or name of an interface prefixed with the project code). Applies only in the case of a Check Knowledge Module |
| JRN_NAME | Name of the journalized datastore. |
| JRN_VIEW | Name of the view linked to the journalized datastore. |
| JRN_DATA_VIEW | Name of the data view linked to the journalized datastore. |
| JRN_TRIGGER | Name of the trigger linked to the journalized datastore. |
| JRN_ITRIGGER | Name of the Insert trigger linked to the journalized datastore. |
| JRN_UTRIGGER | Name of the Update trigger linked to the journalized datastore. |
| JRN_DTRIGGER | Name of the Delete trigger linked to the journalized datastore. |
| SUBSCRIBER_TABLE | Name of the datastore containing the subscribers list. |
| CDC_SET_TABLE | Full name of the table containing list of CDC sets. |
| CDC_TABLE_TABLE | Full name of the table containing the list of tables journalized through CDC sets. |
| CDC_SUBS_TABLE | Full name of the table containing the list of subscribers to CDC sets. |
| CDC_OBJECTS_TABLE | Full name of the table containing the journalizing parameters and objects. |
| SRC_DEF_CATALOG | Default catalog for the source data server. |
| SRC_DEF_SCHEMA | Default schema for the source data server. |
| SRC_DEFW_CATALOG | Default work catalog for the source data server. |
| SRC_DEFW_SCHEMA | Default work schema for the source data server. |
| DEST_DEF_CATALOG | Default catalog for the target data server. |
| DEST_DEF_SCHEMA | Default schema for the target data server. |
| DEST_DEFW_CATALOG | Default work catalog for the target data server. |
| DEST_DEFW_SCHEMA | Default work schema for the target data server. |
| SRC_LSCHEMA_NAME | Source logical schema name. |
| DEST_LSCHEMA_NAME | Target logical schema name. |
| SRC_I_CONNECT | Internal ID of the source data server. |
| SRC_I_PSCHEMA | Internal ID of the source physical schema. |
| SRC_I_LSCHEMA | Internal ID of the source logical schema. |
| SRC_I_TECHNO | Internal ID of the source technology. |
| DEST_I_CONNECT | Internal ID of the target data server. |
| DEST_I_PSCHEMA | Internal ID of the target physical schema. |
| DEST_I_LSCHEMA | Internal ID of the target logical schema. |
| DEST_I_TECHNO | Internal ID of the target technology. |
Examples
The current source condition is: <%=odiRef.getInfo("SRC_CON_NAME")%> on server: <%=odiRef.getInfo("SRC_DSERV_NAME")%>
Use to return the source or target JDBC connection.
Usage
java.sql.Connection getJDBCConnection( java.lang.String pPropertyName)
Description
This method returns the source or target JDBC connection for the current task.
Note:
This method does not return a string, but a JDBC connection object. This object may be used in your Java code within the task.Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | Name of connection to be returned. |
The following table lists the different values possible for pPropertyName:
| Parameter Value | Description |
|---|---|
| SRC | Source connection for the current task. |
| DEST | Target connection for the current task. |
| WORKREP | Work Repository connection. |
Examples
Gets the source connection and creates a statement for this connection.
java.sql.Connection sourceConnection = odiRef.getJDBCConnection("SRC");
java.sql.Statement s = sourceConnection.createStatement();
Use to return a JDBC connection for a given logical schema.
Usage
public java.lang.String getJDBCConnectionFromLSchema( java.lang.String pLogicalSchemaName, java.lang.String pContextName) public java.lang.String getJDBCConnectionFromLSchema( java.lang.String pLogicalSchemaName)
Description
Returns a JDBC connection for a given logical schema. The pLogicalSchemaName identifies the logical schema.
The first syntax resolves the logical schema in the context provided in the pContextName parameter.
The second syntax resolves the logical schema in the current context.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pLogicalSchemaName | String | Name of the forced logical schema of the object. |
| pContextName | String | Forced context of the object |
Note:
This method does not return a string, but a JDBC connection object. This object may be used in your Java code within the task.Use to return the entire WHERE clause section generated for the joins of an interface.
Usage
public java.lang.String getJoin(java.lang.Int pDSIndex)
Description
Retrieves the SQL join string (on the source during the loading, on the staging area during the integration) for a given dataset of an interface.
In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Note:
The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
Examples
insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoin()%>
<%=odiRef.getFilter()%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>
Use to return properties for each join of an interface. The properties are organized according to a string pattern.
Usage
public java.lang.String getJoinList( java.lang.Int pDSIndex, java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
Alternative syntax:
public java.lang.String getJoinList( java.lang.Int pDSIndex, java.lang.String pPattern, java.lang.String pSeparator)
Description
Returns a list of the occurrences of the SQL joins in a given dataset of an interface for the WHERE clause.
In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Note:
The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.The pPattern parameter is interpreted and then repeated for each element in the list and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends up with pEnd.
In the alternative syntax, any parameters not set are set to an empty string.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
| pStart | String | This parameter marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of authorized attributes in a pattern is detailed in the Pattern Attributes List below. Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ]) Example My string [COL_NAME] is a column» |
| pSeparator | String | This parameter separates each pattern from its predecessor. |
| pEnd | String | This parameter marks the end of the string to generate. |
Pattern Attributes List
The following table lists the different values of the parameters as well as the associated description.
| Parameter Value | Description |
|---|---|
| ID | Internal identifier of the join |
| EXPRESSION | Text of the join expression |
Examples
insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getFrom()%>
where (1=1)
<%=odiRef.getJoinList("and ","([EXPRESSION])"," and ","")%>
<%=odiRef.getFilterList("and ","([EXPRESSION])"," and ","")%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>
Explanation: the getJoinList function will be used to generate join expressions to put in the WHERE part of the SELECT statement that must start with "and" and that repeats a pattern (the expression of each join) separated by " and " for each join. Thus:
The first parameter "and" of the function indicates that we want to start the string with "and"
The second parameter "([EXPRESSION])" indicates that we want to repeat this pattern for each join. The keyword [EXPRESSION] references a valid keyword of the table Pattern Attributes List
The third parameter " and " indicates that we want to separate each interpreted occurrence of the pattern with " and " (note the spaces before and after "and")
The fourth parameter "" of the function indicates that we want to end the string with no specific character
Use to return the journalizing filter of an interface.
Usage
public java.lang.String getJrnFilter(java.lang.Int pDSIndex)
Description
Returns the SQL Journalizing filter for a given dataset in the current interface. If the journalized table in the source, this method can be used during the loading phase. If the journalized table in the staging area, this method can be used while integrating.
In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Note:
The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
Examples
<%=odiRef.getJrnFilter()%>
Use to return journalizing information about a datastore.
Usage
public java.lang.String getJrnInfo(java.lang.String pPropertyName)
Description
Returns information about a datastore's journalizing for a JKM while journalizing a model/datastore, or for a LKM/IKM in an interface.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String containing the name of the requested property. |
The following table lists the different values possible for pPropertyName:
| Parameter Value | Description |
|---|---|
| FULL_TABLE_NAME | Full name of the journalized datastore. |
| JRN_FULL_NAME | Full name of the journal datastore. |
| JRN_FULL_VIEW | Full name of the view linked to the journalized datastore. |
| JRN_FULL_DATA_VIEW | Full name of the data view linked to the journalized datastore. |
| JRN_FULL_TRIGGER | Full name of the trigger linked to the journalized datastore. |
| JRN_FULL_ITRIGGER | Full name of the Insert trigger linked to the journalized datastore. |
| JRN_FULL_UTRIGGER | Full name of the Update trigger linked to the journalized datastore. |
| JRN_FULL_DTRIGGER | Full name of the Delete trigger linked to the journalized datastore. |
| SNP_JRN_SUBSCRIBER | Name of the subscriber table in the work schema. |
| JRN_NAME | Name of the journalized datastore. |
| JRN_VIEW | Name of the view linked to the journalized datastore. |
| JRN_DATA_VIEW | Name of the data view linked to the journalized datastore. |
| JRN_TRIGGER | Name of the trigger linked to the journalized datastore. |
| JRN_ITRIGGER | Name of the Insert trigger linked to the journalized datastore. |
| JRN_UTRIGGER | Name of the Update trigger linked to the journalized datastore. |
| JRN_DTRIGGER | Name of the Delete trigger linked to the journalized datastore. |
| JRN_SUBSCRIBER | Name of the subscriber. |
| JRN_COD_MOD | Code of the journalized data model. |
| JRN_METHOD | Journalizing Mode (consistent or simple). |
| CDC_SET_TABLE | Full name of the table containing list of CDC sets. |
| CDC_TABLE_TABLE | Full name of the table containing the list of tables journalized through CDC sets. |
| CDC_SUBS_TABLE | Full name of the table containing the list of subscribers to CDC sets. |
| CDC_OBJECTS_TABLE | Full name of the table containing the journalizing parameters and objects. |
Examples
The table being journalized is <%=odiRef.getJrnInfo("FULL_TABLE_NAME")%>
Use to return the Load Plan instance information.
Usage
public java.lang.String getLoadPlanInstance (java.lang.String pPropertyName)
Description
This method returns the current execution instance information for a Load Plan.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String that contains the name of the requested property. |
The following table lists the possible values for pPropertyName:
| Parameter Value | Description |
|---|---|
| BATCH_ID | Load Plan instance identifier (also Instance ID). Every time a Load Plan is started, a new Load Plan instance with a unique identifier is created |
| RESTART_ATTEMPTS | Number of execution attempts of this Load Plan instance (also Run #). It starts at 1 when the Load Plan instance is first executed, and is incremented each time the Load Plan instance is restarted. |
| LOAD_PLAN_NAME | Name of the Load Plan |
| START_DATE | Starting date and time of the current Load Plan instance run |
Examples
The current Load Plan <%=odiRef.getLoadPlanInstance("LOAD_PLAN_NAME")%> started execution at <%=odiRef.getLoadPlanInstance("START_DATE")%>
Use to return information about a model.
Usage
public java.lang.String getModel(java.lang.String pPropertyName)
Description
This method returns information on the current data model during the processing of a personalized reverse engineering. The list of available data is described in the pPropertyName values table.
Note:
This method may be used on the source connection (data server being reverse-engineered) as well as on the target connection (repository). On the target connection, only the properties independent from the context can be specified (for example, the schema and catalog names cannot be used).Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String that contains the name of the requested property. |
The following table lists the possible values for pPropertyName:
| Parameter Value | Description |
|---|---|
| ID | Internal identifier of the current model |
| MOD_NAME | Name of the current model |
| LSCHEMA_NAME | Name of the logical schema of the current model |
| MOD_TEXT | Description of the current model |
| REV_TYPE | Reverse engineering type: S for standard reverse, C for customize |
| REV_UPDATE | Update flag of the model |
| REV_INSERT | Insert flag for the model |
| REV_OBJ_PATT | Mask for the objects to reverse. |
| REV_OBJ_TYPE | List of object types to reverse-engineer for this model. This is a semicolon separated list of object types codes. Valid codes are:
|
| TECH_INT_NAME | Internal name of the technology of the current model. |
| LAGENT_NAME | Name of the logical execution agent for the reverse engineering. |
| REV_CONTEXT | Execution context of the reverse |
| REV_ALIAS_LTRIM | Characters to be suppressed for the alias generation |
| CKM | Check Knowledge Module |
| RKM | Reverse-engineering Knowledge Module |
| SCHEMA_NAME | Physical Name of the data schema in the current reverse context |
| WSCHEMA_NAME | Physical Name of the work schema in the current reverse context |
| CATALOG_NAME | Physical Name of the data catalog in the current reverse context |
| WCATALOG_NAME | Physical Name of the work catalog in the current reverse context |
| <flexfield code> | Value of the flexfield for the current model. |
Examples
Retrieve the list of tables that are part of the mask of objects to reverse:
select TABLE_NAME,
RES_NAME,
replace(TABLE_NAME, '<%=odiRef.getModel("REV_ALIAS_LTRIM")%>' , '')
ALIAS,
TABLE_DESC
from MY_TABLES
where
TABLE_NAME like '<%=odiRef.getModel("REV_OBJ_PATT")%>'
Use to get the number of inserted, updated, deleted or erroneous rows for the current task.
Usage
public java.lang.Long getNbInsert() public java.lang.Long getNbUpdate() public java.lang.Long getNbDelete() public java.lang.Long getNbErrors() public java.lang.Long getNbRows()
Description
These methods get for the current task the values for:
the number of rows inserted (getNbInsert)
the number of rows updated (getNbUpdate)
the number of rows deleted (getNbDelete)
the number of rows in error (getNbErrors)
total number of rows handled during this task (getNbRows)
These numbers can be set independently from the real number of lines processed using the setNbInsert(), setNbUpdate(), setNbDelete(), setNbErrors() and setNbRows() Methods.
Examples
In the Jython example below, we set the number of inserted rows to the constant value of 50, and copy this value in the number of errors.
InsertNumber=50 odiRef.setNbInsert(InsertNumber) odiRef.setNbErrors(odiRef.getNbInsert())
Use to return the new comment for a specific column handled by an action.
Usage
public java.lang.String getNewColComment()
Description
In an action, this method returns the new comment for the column 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.
Usage
public java.lang.String getNewTableComment()
Description
In an action, this method returns the new comment for the table being handled by the DDL command, in a Modify table comment action.
Use to return information about a column that is checked for not null.
Usage
public java.lang.String getNotNullCol(java.lang.String pPropertyName)
Description
This method returns information relative to a not null column of a datastore during a check procedure. It is accessible from a Check Knowledge Module if the current task is tagged as "mandatory".
Parameters
| Parameter | Type | Description |
|---|---|---|
| Parameter | Type | Description |
| pPropertyName | String | String that contains the name of the requested property. |
The following table lists the different possible values for pPropertyName:
| Parameter Value | Description |
|---|---|
| ID | Internal identifier for the current column. |
| COL_NAME | Name of the Not null column. |
| MESS | Standard error message. |
| <flexfield code> | Flexfield value for the current not null column. |
Examples
insert into...
select *
from ...
<%=odiRef.getNotNullCol("COL_NAME")%> is null
Use to return the fully qualified named of an object.
Usage
public java.lang.String getObjectName( java.lang.String pMode, java.lang.String pObjectName, java.lang.String pLocation) public java.lang.String getObjectName( java.lang.String pMode, java.lang.String pObjectName, java.lang.String pLogicalSchemaName, java.lang.String pLocation) public java.lang.String getObjectName( java.lang.String pMode, java.lang.String pObjectName, java.lang.String pLogicalSchemaName, java.lang.String pContextName, java.lang.String pLocation) public java.lang.String getObjectName( java.lang.String pObjectName, java.lang.String pLocation) public java.lang.String getObjectName( java.lang.String pObjectName) public java.lang.String getObjectName(java.lang.String pMode,java.lang.String pObjectName,java.lang.String pLogicalSchemaName,java.lang.String pContextName,java.lang.String pLocation, java.lang.String pPartitionType, java.lang.String pPartitionName)
Description
Returns the fully qualified name of a physical object, including its catalog and schema. The pMode parameter indicates the substitution mask to use.
Note:
The getObjectName methods truncates automatically object names to the maximum object length allowed for the technology. In versions before ODI 11g, object names were not trucated. To prevent object names truncation and reproduce the 10g behavior, add in the properties tab of the data server a property called OBJECT_NAME_LENGTH_CHECK_OLD and set its value 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.
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. |
Prefixes
It is possible to prefix the resource name specified in the pObjectName parameter by a prefix code to generate a Oracle Data Integrator temporary object name (Error or Integration table, journalizing trigger, etc.).
The list of prefixes are given in the table below.
| Prefix | Description |
|---|---|
| Prefix | Description |
| %INT_PRF | Prefix for integration tables (default value is "I$_"). |
| %COL_PRF | Prefix for Loading tables (default value is "C$_"). |
| %ERR_PRF | Prefix for error tables (default value is "E$_"). |
| %JRN_PRF_TAB | Prefix for journalizing tables (default value is "J$_"). |
| %INT_PRF_VIE | Prefix for journalizing view (default value is "JV$_"). |
| %JRN_PRF_TRG | Prefix for journalizing triggers (default value is "T$_"). |
| %IDX_PRF | Prefix for temporary indexes (default value is "IX$_"). |
Note:
Temporary objects are usually created in the work physical schema. Therefore, pLocation should be set to "W" when using a prefix to create or access a temporary object.Examples
You have defined a physical schema as shown below.
| Data catalog: | db_odi |
| Data schema: | dbo |
| Work catalog: | tempdb |
| Work schema: | temp_owner |
You have associated this physical schema to the logical schema MSSQL_ODI in the context CTX_DEV.
| A Call To | Returns |
|---|---|
<%=odiRef.getObjectName("L", "EMP", "MSSQL_ODI", "CTX_DEV", "W")%> |
tempdb.temp_owner.EMP |
<%=odiRef.getObjectName("L", "EMP", "MSSQL_ODI", "CTX_DEV", "D")%> |
db_odi.dbo.EMP |
<%=odiRef.getObjectName("R", "%ERR_PRFEMP", "MSSQL_ODI", "CTX_DEV", "W")%> |
MyServer.tempdb.temp_owner.E$_EMP |
<%=odiRef.getObjectName("R", "EMP", "MSSQL_ODI", "CTX_DEV", "D")%> |
MyServer.db_odi.dbo.EMP |
Use to return the fully qualified named of an object in the default physical schema for the data server.
Usage
public java.lang.String getObjectNameDefaultPSchema( java.lang.String pMode, java.lang.String pObjectName, java.lang.String pLocation) public java.lang.String getObjectNameDefaultPSchema( java.lang.String pMode, java.lang.String pObjectName, java.lang.String pLogicalSchemaName, java.lang.String pLocation) public java.lang.String getObjectNameDefaultPSchema( java.lang.String pMode, java.lang.String pObjectName, java.lang.String pLogicalSchemaName, java.lang.String pContextName, java.lang.String pLocation) public java.lang.String getObjectNameDefaultPSchema( java.lang.String pObjectName, java.lang.String pLocation) public java.lang.String getObjectNameDefaultPSchema( java.lang.String pObjectName) public java.lang.String getObjectNameDefaultPSchema(java.lang.String pMode,java.lang.String pObjectName,java.lang.String pLogicalSchemaName,java.lang.String pContextName,java.lang.String pLocation, java.lang.String pPartitionType, java.lang.String pPartitionName)
Description
The method is similar to the getObjectName method. However, the object name is computed for the default physical schema of the data server to which the physical schema is attached. In getObjectName, the object name is computed for the physical schema itself.
For more information, see "getObjectName() Method".
Use to return the value of a KM or procedure option.
Usage
public java.lang.String getOption(java.lang.String pOptionName) public java.lang.String getUserExit(java.lang.String pOptionName)
Description
Returns the value of a KM or procedure option.
The getUserExit syntax is deprecated and is only kept for compatibility reasons.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pOptionName | String | String that contains the name of the requested option. |
Examples
The value of my MY_OPTION_1 option is <%=odiRef.getOption("MY_OPTION_1")%>
Use to return information about the current package.
Usage
public java.lang.String getPackage(java.lang.String pPropertyName)
Description
This method returns information about the current package. The list of available properties is described in the pPropertyName values table.
Parameters
| Parameters | Type | Description |
|---|---|---|
| pPropertyName | String | String that contains the name of the requested property. |
The following table lists the different possible values for pPropertyName:
| Parameter Value | Description |
|---|---|
| I_PACKAGE | Internal ID of the package |
| PACKAGE_NAME | Name of the package |
| <flexfield code> | Value of the flexfield for this package. |
Examples
Package <%=odiRef.getPackage("PACKAGE_NAME")%> is running.
Use to return the parent Load Plan step instance of this session.
Usage
public java.lang.String getParentLoadPlanStepInstance(java.lang.String pPropertyName)
Description
This method returns the step execution instance information of the parent of the current step for a Load Plan instance. It will return an empty string if the parent step is the root step.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String that contains the name of the requested property. |
The following table lists the different possible values for pPropertyName.
| Parameter Value | Description |
|---|---|
| BATCH_ID | Load Plan instance identifier (also Instance ID). Every time a Load Plan is started, a new Load Plan instance with a unique identifier is created. |
| RESTART_ATTEMPTS | Number of execution attempts of this Load Plan parent step instance. It starts at 1 when the Load Plan parent step instance is first started, and is incremented each time the Load Plan parent step instance is restarted. |
| STEP_NAME | Name of the Load Plan parent step |
| STEP_TYPE | Type of the Load Plan parent step |
| START_DATE | Starting date and time of the parent step instance of the current step of the current Load Plan instance run. |
Examples
Step <%=odiRef.getParentLoadPlanStepInstance("STEP_NAME")%> has been executed <%=odiRef.getParentLoadPlanStepInstance("RESTART_ATTEMPTS")%> times
Use to return information about a primary key.
Usage
public java.lang.String getPK(java.lang.String pPropertyName)
Description
This method returns information relative to the primary key of a datastore during a check procedure.
In an action, this method returns information related to the primary key currently handled by the DDL command.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String that contains the name of the requested property. |
The following table lists the different possible values for pPropertyName.
| Parameter Value | Description |
|---|---|
| ID | Internal number of the PK constraint. |
| KEY_NAME | Name of the primary key |
| MESS | Error message relative to the primary key constraint. |
| FULL_NAME | Full name of the PK generated with the local object mask. |
| <flexfield code> | Flexfield value for the primary key. |
Examples
The primary key of my table is called: <%=odiRef.getPK("KEY_NAME")%>
Use to return information about the columns of a primary key.
Usage
public java.lang.String getPKColList( java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
Description
Returns a list of columns 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 column 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 columns of the primary key handled by the DDL command, ordered by their position in the key.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pStart | String | This sequence marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of attributes that can be used in a pattern is detailed in the Pattern Attributes List below. Each attribute occurrence in the pattern sequence is replaced with its value. The attributes must be between brackets. ([ and ]) Example «My string [COL_NAME] is a column» |
| pSeparator | String | This parameter separates each pattern from its predecessor. |
| pEnd | String | This sequence marks the end of the string to generate. |
Pattern Attributes List
The following table lists the different values of the parameters as well as their associated description.
| Parameter Value | Description |
|---|---|
| I_COL | Column internal identifier |
| COL_NAME | Name of the key column |
| COL_HEADING | Header of the key column |
| COL_DESC | Column description |
| POS | Position of the column |
| LONGC | Length (Precision) of the column |
| SCALE | Scale of the column |
| FILE_POS | Beginning position of the column (fixed file) |
| BYTES | Number of physical bytes of the column |
| FILE_END_POS | End of the column (FILE_POS + BYTES) |
| IND_WRITE | Write right flag of the column |
| COL_MANDATORY | Mandatory character of the column. Valid values are:
|
| CHECK_FLOW | Flow control flag for of the column. Valid values are:
|
| CHECK_STAT | Static control flag of the column. Valid values are:
|
| COL_FORMAT | Logical format of the column |
| COL_DEC_SEP | Decimal symbol for the column |
| REC_CODE_LIST | List of the record codes retained for the column |
| COL_NULL_IF_ERR | Processing flag for the column. Valid values are:
|
| DEF_VALUE | Default value for the column |
| EXPRESSION | Not used |
| CX_COL_NAME | Not used |
| ALIAS_SEP | Grouping symbol used for the alias (from the technology) |
| SOURCE_DT | Code of the column's datatype. |
| SOURCE_CRE_DT | Create table syntax for the column's datatype. |
| SOURCE_WRI_DT | Create table syntax for the column's writable datatype. |
| DEST_DT | Code of the column's datatype converted to a datatype on the target technology. |
| DEST_CRE_DT | Create table syntax for the column's datatype converted to a datatype on the target technology. |
| DEST_WRI_DT | Create table syntax for the column's writable datatype converted to a datatype on the target technology. |
| SCD_COL_TYPE | Behavior defined for the Slowly Changing Dimensions for this column in the data model. |
| <flexfield code> | Flexfield value for the current column. |
Examples
If the CUSTOMER table has an primary key PK_CUSTOMER (CUST_ID, CUST_NAME) and you want to generate the following code:
create table T_PK_CUSTOMER (CUST_ID numeric(10) not null, CUST_NAME varchar(50) not null)
You can use the following code:
create table T_<%=odiRef.getPK("KEY_NAME")%>
<%=odiRef.getPKColList("(", "[COL_NAME] [DEST_CRE_DT] not null", ", ", ")")%>
Explanation: the getPKColList function will be used to generate the (CUST_ID numeric(10) not null, CUST_NAME varchar(50) not null) part, which starts and stops with a parenthesis and repeats the pattern (column, a data type, and not null) separated by commas for each column 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 column 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 an interface.
Usage
public java.lang.String getPop(java.lang.String pPropertyName)
Description
This method returns information about the current interface. The list of available information is described in the pPropertyName values table.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String that contains the name of the requested property. |
The following table lists the different possible values for pPropertyName:
| Parameter Value | Description |
|---|---|
| I_POP | Internal number of the interface. |
| FOLDER | Name of the folder of the interface |
| POP_NAME | Name of the interface |
| IND_WORK_TARG | Position flag of the staging area. |
| LSCHEMA_NAME | Name of the logical schema which is the staging area of the interface |
| DESCRIPTION | Description of the interface |
| WSTAGE | Flag indicating the nature of the target datastore:
|
| TABLE_NAME | Target table name |
| KEY_NAME | Name of the update key |
| DISTINCT_ROWS | Flag for doubles suppression |
| OPT_CTX | Name of the optimization context of the interface |
| TARG_CTX | Name of the execution context of the interface |
| MAX_ERR | Maximum number of accepted errors |
| MAX_ERR_PRCT | Error indicator in percentage |
| IKM | Name of the Integration Knowledge Module used in this interface. |
| 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 interface. |
| HAS_JRN | Returns 1 if there is a journalized table in source of the interface, 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 interface. |
Examples
The current interface 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.
Usage
public java.lang.String getPrevStepLog(java.lang.String pPropertyName)
Description
Returns information about the most recently executed step in a package. The information requested is specified through the pPropertyName parameter. If there is no previous step (for example, if the getPrevStepLog step is executed from outside a package), the exception "No previous step" is raised.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String that contains the name of the requested property about the previous step. See the list of valid properties below. |
The following table lists the different possible values for pPropertyName:
| Parameter Value | Description |
|---|---|
| SESS_NO | The number of the session. |
| NNO | The number of the step within a package. The first step executed is 0. |
| STEP_NAME | The name of the step. |
| STEP_TYPE | A code indicating the type of step. The following values may be returned:
|
| 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. |
Examples
Previous step '<%=odiRef.getPrevStepLog("STEP_NAME")%>' executed
in '<%=odiRef.getPrevStepLog("DURATION")%>' seconds.
Use to return a quoted string.
Usage
public java.lang.String getQuotedString(java.lang.String pString)
Description
This method returns a string surrounded with quotes. It preserves quotes and escape characters such as \n, \t that may appear in the string.
This method is useful to protect a string passed as a value in Java, Groovy or Jython code.
Parameters
| Parameter | Type | Description |
|---|---|---|
| Parameter | Type | Description |
| pString | String | String that to be protected with quotes. |
Examples
In the following Java code, the getQuotedString method is used to generate a valid string value.
String condSqlOK = <%=odiRef.getQuotedString(odiRef.getCK("MESS"))%>;
String condSqlKO = <%=odiRef.getCK("MESS")%>;
If the message for the condition is "Error:\n Zero is not a valid value", the generated code is as shown below. Without the getQuotedString, the code is incorrect, as the \n is not preserved and becomes a carriage return.
String condSqlOK = "Error:\n Zero is not a valid value"; String condSqlKO = "Error: Zero is not a valid value";
Use to return a schema name from the topology.
Usage
public java.lang.String getSchemaName( java.lang.String pLogicalSchemaName, java.lang.String pLocation) public java.lang.String getSchemaName( java.lang.String pLogicalSchemaName, java.lang.String pContextCode, java.lang.String pLocation) public java.lang.String getSchemaName( java.lang.String pLocation) public java.lang.String getSchemaName()
Description
Retrieves the physical name of a data schema or work schema from its logical schema.
If the first syntax is used, the returned schema corresponds to the current context.
If the second syntax is used, the returned schema corresponds to context specified in the pContextCode parameter.
The third syntax returns the name of the data schema (D) or work schema (W) for the current logical schema in the current context.
The fourth syntax returns the name of the data schema (D) for the current logical schema in the current context.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pLogicalSchemaName | String | Name of the logical schema of the schema |
| pContextCode | String | Forced context of the schema |
| pLocation | String | The valid values are:
|
Examples
If you have defined the physical schema: Pluton.db_odi.dbo
| Data catalog: | db_odi |
| Data schema: | dbo |
| Work catalog: | tempdb |
| Work schema: | temp_owner |
and you have associated this physical schema to the logical schema: MSSQL_ODI in the context CTX_DEV
| The Call To | Returns |
|---|---|
<%=odiRef.getSchemaName("MSSQL_ODI", "CTX_DEV", "W")%> |
temp_owner |
<%=odiRef.getSchemaName("MSSQL_ODI", "CTX_DEV", "D")%> |
dbo |
Use to return a catalog name for the default physical schema from the topology.
Usage
public java.lang.String getSchemaNameDefaultPSchema( java.lang.String pLogicalSchemaName, java.lang.String pLocation) public java.lang.String getSchemaNameDefaultPSchema( java.lang.String pLogicalSchemaName, java.lang.String pContextCode, java.lang.String pLocation) public java.lang.String getSchemaNameDefaultPSchema( java.lang.String pLocation) public java.lang.String getSchemaNameDefaultPSchema(
Description
Allows you to retrieve the name of the default physical data schema or work schema for the data server to which is associated the physical schema corresponding to the tuple (logical schema, context). If no context is specified, the current context is used. If no logical schema name is specified, then the current logical schema is used. If no pLocation is specified, then the data schema is returned.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pLogicalSchemaName | String | Name of the logical schema |
| pContextCode | String | Code of the enforced context of the schema |
| pLocation | String | The valid values are:
|
Examples
If you have defined the physical schemas: Pluton.db_odi.dbo
| Data catalog: | db_odi |
| Data schema: | dbo |
| Work catalog: | tempdb |
| Work schema: | temp_odi |
| Default Schema | Yes |
that you have associated with this physical schema: MSSQL_ODI in the context CTX_DEV, and Pluton.db_doc.doc
| Data catalog: | db_doc |
| Data schema: | doc |
| Work catalog: | tempdb |
| Work schema: | temp_doc |
| Default Schema | No |
that you have associated with this physical schema: MSSQL_DOC in the context CTX_DEV
| The Call To | Returns |
|---|---|
<%=odiRef.getSchemaNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "W")%> |
temp_odi |
<%=odiRef.getSchemaNameDefaultPSchema("MSSQL_DOC", "CTX_DEV", "D")%> |
dbo |
Use to return information about the current session.
Usage
public java.lang.String getSession(java.lang.String pPropertyName)
Description
This method returns information about the current session. The list of available properties is described in the pPropertyName values table.
Parameters
| Parameters | Type | Description |
|---|---|---|
| pPropertyName | String | String that contains the name of the requested property. |
The following table lists the different possible values for pPropertyName:
| Parameter Value | Description |
|---|---|
| SESS_NO | Internal number of the session |
| SESS_NAME | Name of the session |
| SCEN_NAME | Name of the scenario |
| SCEN_VERSION | Current scenario version |
| CONTEXT_NAME | Name of the execution context |
| CONTEXT_CODE | Code of the execution context |
| AGENT_NAME | Name of the physical agent in charge of the execution |
| SESS_BEG | Date and time of the beginning of the session |
| USER_NAME | ODI User running the session. |
Examples
The current session is: <%=odiRef.getSession("SESS_NAME")%>
Reserved for future use.
Usage
public java.lang.String getSessionVarList( java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd, java.lang.String pSelector)
Description
Reserved for future use.
Parameters
Reserved for future use.
Examples
Reserved for future use.
Use to return properties for each column from a filtered list of source columns involved in a loading or integration phase. The properties are organized according to a string pattern.
Usage
public java.lang.String getSrcColList( java.lang.Int pDSIndex, java.lang.String pStart, java.lang.String pUnMappedPattern, java.lang.String pMappedPattern, java.lang.String pSeparator, java.lang.String pEnd)
Description
This method available in LKMs and IKMs, returns properties for a list of columns in a given dataset. This list includes all the columns of the sources processed by the LKM (from the source) or the IKM (from the staging area). The list is sorted by the column 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 column is mapped or not. If the column is mapped, the properties returned are defined in the pMappedPattern pattern. If the column 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 column".
The pMappedPattern or pUnMappedPattern parameter is interpreted and then repeated for each element of the list. Patterns are separated with pSeparator. The generated string begins with pStart and ends with pEnd.
If there is a journalized datastore in the source of the interface, the three journalizing pseudo columns JRN_FLG, JRN_DATE and JRN_SUBSCRIBER are added as columns of the journalized source datastore.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
| pStart | String | This sequence marks the beginning of the string to generate. |
| pUnMappedPattern | String | The pattern is repeated for each occurrence in the list if the column is not mapped. |
| pMappedPattern | String | The pattern is repeated for each occurrence in the list, if the column is mapped. |
| pSeparator | String | This parameter separates patterns. |
| pEnd | String | This sequence marks the end of the string to generate. |
Pattern Attributes List
The following table lists different parameters values as well as their associated description.
| Parameter Value | Description |
|---|---|
| I_COL | Internal identifier of the column |
| COL_NAME | Name of the column |
| ALIAS_NAME | Name of the column. Unlike COL_NAME, this attribute returns the column name without the optional technology delimiters. These delimiters appear when the column name contains for instance spaces. |
| COL_HEADING | Header of the column |
| COL_DESC | Description of the column |
| POS | Position of the column |
| LONGC | Column length (Precision) |
| SCALE | Scale of the column |
| FILE_POS | Beginning (index) of the column |
| BYTES | Number of physical bytes in the column |
| FILE_END_POS | End of the column (FILE_POS + BYTES) |
| IND_WRITE | Write right flag of the column |
| COL_MANDATORY | Mandatory character of the column. Valid values are: (0: null authorized, 1: not null)
|
| CHECK_FLOW | Flow control flag of the column. Valid values are: (0: do not check, 1: check)
|
| CHECK_STAT | Static control flag of the column. Valid values are: (0: do not check, 1: check)
|
| COL_FORMAT | Logical format of the column |
| COL_DEC_SEP | Decimal symbol of the column |
| REC_CODE_LIST | List of the record codes retained in the column |
| COL_NULL_IF_ERR | Processing flag of the column. Valid values are:
|
| DEF_VALUE | Default value of the column |
| EXPRESSION | Text of the expression (as typed in the mapping field) executed on the source (LKM) or the staging area (IKM). If the column 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 column's datatype. |
| SOURCE_CRE_DT | Create table syntax for the column's datatype. |
| SOURCE_WRI_DT | Create table syntax for the column's writable datatype. |
| DEST_DT | Code of the column's datatype converted to a datatype on the target (IKM) or staging area (LKM) technology. |
| DEST_CRE_DT | Create table syntax for the column's datatype converted to a datatype on the target technology. |
| DEST_WRI_DT | Create table syntax for the column's writable datatype converted to a datatype on the target technology. |
| SCD_COL_TYPE | Behavior defined for the Slowly Changing Dimensions for this column in the data model. |
| MANDATORY_CLAUSE | Returns NOT NULL if the column 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 column. |
Examples
To create a table similar to a source file:
create table <%=odiRef.getTable("L","COLL_NAME", "D")%>_F
(
<%=odiRef.getSrcColList("","[COL_NAME] [DEST_CRE_DT]","[COL_NAME]
[DEST_CRE_DT]",",\n","")%>
)
Use to return properties for each source table of an interface. The properties are organized according to a string pattern.
Usage
public java.lang.String getSrcTablesList( java.lang.Int pDSIndex, java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
Alternative syntax:
public java.lang.String getSrcTablesList( java.lang.Int pDSIndex, java.lang.String pPattern, java.lang.String pSeparator)
Description
Returns a list of source tables of a given dataset in an interface. This method can be used to build a FROM clause in a SELECT order. However, it is advised to use the getFrom() method instead.
In IKMs only, the pDSIndex parameter identifies which of the datasets is taken into account by this command.
Note:
The pDSIndex parameter can be omitted when this method is used in an LKM. It can be also omitted for IKMs. In this case, the dataset taken into account is the first one.The pPattern pattern is interpreted and then repeated for each element of the list and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.
In the alternative syntax, any parameters not set are set to an empty string.
Parameters
| Parameters | Type | Description |
|---|---|---|
| pDSIndex | Int | Index identifying which of the datasets is taken into account by this command. |
| pStart | String | This parameter marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of possible attributes in a pattern is detailed in the Pattern Attributes List below. Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ]) Example «My string [COL_NAME] is a column» |
| pSeparator | String | This parameter separates each pattern from its predecessor. |
| pEnd | String | This parameter marks the end of the string to generate. |
Pattern Attributes List
The following table lists the different values of the parameters as well as the associated description.
| Attribute | Description |
|---|---|
| I_TABLE | Internal identifier of the current source table if available. |
| MODEL_NAME | Name of the model of the current source table, if available. |
| SUB_MODEL_NAME | Name of the sub-model of the current source table, if available |
| TECHNO_NAME | Name of the technology of the source datastore |
| LSCHEMA_NAME | Logical schema of the source table |
| TABLE_NAME | Logical name of the source datastore |
| RES_NAME | Physical access name of the resource (file name or JMS queue, physical name of the table, etc.). If there is a journalized datastore in source of the interface, 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 interface, if available. |
| TABLE_TYPE | Type of the datastore source, if available. |
| DESCRIPTION | Description of the source datastore, if available. |
| R_COUNT | Number of records of the source datastore, if available. |
| FILE_FORMAT | File format, if available. |
| FILE_SEP_FIELD | Field separator (file) |
| XFILE_SEP_FIELD | Hexadecimal field separator (file) |
| SFILE_SEP_FIELD | Field separator string (file) |
| FILE_ENC_FIELD | Field beginning and ending character (file) |
| FILE_SEP_ROW | Record separator (file) |
| XFILE_SEP_ROW | Hexadecimal record separator (file) |
| SFILE_SEP_ROW | Record separator string (file) |
| FILE_FIRST_ROW | Number of header lines to ignore, if available. |
| FILE_DEC_SEP | Default decimal separator for the datastore, if available. |
| METADATA | Description in ODI format of the metadata of the current resource, if available. |
| OLAP_TYPE | OLAP type specified in the datastore definition |
| IND_JRN | Flag indicating that the datastore is including in CDC. |
| JRN_ORDER | Order of the datastore in the CDC set for consistent journalizing. |
| PARTITION_NAME | Name of the partition or sub-partition selected for the source datastore. If no partition is selected, returns an empty string. |
| PARTITION_TYPE | Type of the partition or sub-partition selected for the source datastore. If no partition is selected, returns an empty string.
|
| <flexfield code> | Flexfield value for the current table. |
Examples
insert into <%=odiRef.getTable("L", "COLL_NAME", "W")%>
select <%=odiRef.getColList("", "[EXPRESSION]", ", ", "", "INS=1")%>
from <%=odiRef.getSrcTablesList("", "[CATALOG].[SCHEMA].[TABLE_NAME] AS [POP_TAB_ALIAS]", ", ", "")%>
where (1=1)
<%=odiRef.getJoinList("and ","([EXPRESSION])"," and ","")%>
<%=odiRef.getFilterList("and ","([EXPRESSION])"," and ","")%>
<%=odiRef.getGrpBy()%>
<%=odiRef.getHaving()%>
Explanation: the getSrcTablesList function will be used to generate the FROM clause of the SELECT STATEMENT that repeats the pattern (CATALOG.SCHEMA.TABLE_NAME as POP_TAB_ALIAS) separated by commas for each table in source.
The first parameter "" of the function indicates that we want do not want to start the string with any specific character.
The second parameter "[CATALOG].[SCHEMA].[TABLE_NAME] as [POP_TAB_ALIAS]" indicates that we want to repeat this pattern for each source table. The keywords [CATALOG], [SCHEMA], [TABLE_NAME] and [POP_TAB_ALIAS] reference valid keywords of the table Pattern Attributes List
The third parameter", " indicates that we want to separate each interpreted occurrence of the pattern with the string ", "
The fourth parameter "" of the function indicates that we want to end the string with no specific character
Use to return information about the current step.
Usage
public java.lang.String getStep(java.lang.String pPropertyName)
Description
This method returns information about the current step. The list of available information is described in the pPropertyName values table.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String that contains the name of the requested property. |
The following table lists the possible values for pPropertyName:
| Parameter Value | Description |
|---|---|
| SESS_NO | Number of the session to which the step belongs. |
| NNO | Number of the step in the session |
| NB_RUN | Number of execution attempts |
| STEP_NAME | Step name |
| STEP_TYPE | Step type |
| CONTEXT_NAME | Name of the execution context |
| VAR_INCR | Step variable increment |
| VAR_OP | Operator used to compare the variable |
| VAR_VALUE | Forced value of the variable |
| OK_EXIT_CODE | Exit code in case of success |
| OK_EXIT | End the package in case of success |
| OK_NEXT_STEP | Next step in case of success. |
| OK_NEXT_STEP_NAME | Name of the next step in case of success |
| KO_RETRY | Number of retry attempts in case of failure. |
| KO_RETRY_INTERV | Interval between each attempt in case of failure |
| KO_EXIT_CODE | Exit code in case of failure. |
| KO_EXIT | End the package in case of failure. |
| KO_NEXT_STEP | Next step in case of failure. |
| KO_NEXT_STEP_NAME | Name of the next step in case of failure |
Examples
The current step is: <%=odiRef.getStep("STEP_NAME")%>
Use to return properties for each of the subscribers of a journalized table. The properties are organized according to a string pattern.
Usage
public java.lang.String getSubscriberList( java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
Alternative syntax:
public java.lang.String getSubscriberList( java.lang.String pPattern, java.lang.String pSeparator,
Description
Returns a list of subscribers for a journalized table. The pPattern parameter is interpreted and then repeated for each element of the list, and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.
In the alternative syntax, any parameters not set are set to an empty string.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pStart | String | This sequence marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of the attributes usable in a pattern is detailed in the Pattern Attributes List below. Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ]) Example «My name is [SUBSCRIBER]» |
| pSeparator | String | This parameter separates each pattern from its predecessor. |
| pEnd | String | This sequence marks the end of the string to generate. |
Pattern Attributes List
The following table lists different parameters values as well as their associated description.
| Parameter Value | Description |
|---|---|
| SUBSCRIBER | Name of the Subscriber |
Examples
Here is list of Subscribers: <%=odiRef.getSubscriberList("\nBegin List\n", "- [SUBSCRIBER]", "\n", "\nEnd of List\n")%>
Use to return the system date of the machine running the session in a given format.
Usage
public java.lang.String getSysDate() public java.lang.String getSysDate(pDateFormat)
Description
This method returns the system date of the machine running the session.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pDateFormat | String | Date format used to return the system date. This pattern should follow the Java Date and Time pattern. For more information, see http://download.oracle.com/javase/1.4.2/docs/api/java/text/SimpleDateFormat.html |
Examples
Current year is: <%=odiRef.getSysDate("y")%>
Use to return the fully qualified named of a table. This table may be a source or target table, or one of the temporary or infrastructure table handled by Oracle Data Integrator.
Usage
public java.lang.String getTable( java.lang.String pMode, java.lang.String pProperty, java.lang.String pLocation) public java.lang.String getTable( java.lang.String pProperty, java.lang.String pLocation) public java.lang.String getTable( java.lang.String pProperty)
Description
Allows the retrieval of the fully qualified name of temporary and permanent tables handled by Oracle Data Integrator.
Parameters
| Parameters | Type | Description |
|---|---|---|
| pMode | String | "L": Uses the local object mask to build the complete path of the object. This value is used when pMode is not specified.
"R": Uses the object mask to build the complete path of the object "A" Automatic: Defines automatically the adequate mask to use. |
| pProperty | String | Parameter that indicates the name of the table to be built. The list of possible values is:
|
| pLocation | String | W: Returns the full name of the object in the physical catalog and the physical work schema that corresponds to the current tuple (context, logical schema)
D: Returns the full name of the object in the physical catalog and the physical data schema that corresponds to the current tuple (context, logical schema) A: Lets Oracle Data Integrator determine the default location of the object. This value is used if pLocation is not specified. |
Examples
If you have defined a physical schema called Pluton.db_odi.dbo as shown below:
| Data catalog: | db_odi |
| Data schema: | dbo |
| Work catalog: | tempdb |
| Work schema: | temp_owner |
| Local Mask: | %CATALOG.%SCHEMA.%OBJECT |
| Remote mask: | %DSERVER:%CATALOG.%SCHEMA.%OBJECT |
| Loading prefix: | CZ_ |
| Error prefix: | ERR_ |
| Integration prefix: | I$_ |
You have associated this physical schema to the logical schema called MSSQL_ODI in the context CTX_DEV and your working with a table is named CUSTOMER.
| A Call To | Returns |
|---|---|
<%=odiRef.getTable("L", "COLL_NAME", "W")%> |
tempdb.temp_owner.CZ_0CUSTOMER |
<%=odiRef.getTable("R", "COLL_NAME", "D")%> |
MyServer:db_odi.dbo.CZ_0CUSTOMER |
<%=odiRef.getTable("L", "INT_NAME", "W")%> |
tempdb.temp_owner.I$_CUSTOMER |
<%=odiRef.getTable("R", "ERR_NAME", "D")%> |
MyServer:db_odi.dbo.ERR_CUSTOMER |
Use to return information about the columns of the target table of an interface.
Usage
public java.lang.String getTargetColList( java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd, java.lang.String pSelector)
Alternative syntaxes:
public java.lang.String getTargetColList( java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd) public java.lang.String getTargetColList( java.lang.String pPattern, java.lang.String pSeparator)
Description
Provides a list of columns for the interface's target table.
The pPattern parameter is interpreted and then repeated for each element of the list (selected according to pSelector parameter) and separated from its predecessor with the parameter pSeparator. The generated string begins with pStart and ends with pEnd. If no element is selected, pStart and pEnd are omitted and an empty string is returned.
In the alternative syntaxes, any parameters not set are set to an empty string.
Parameters
| Parameters | Type | Description |
|---|---|---|
| pStart | String | This sequence marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of the attributes usable in a pattern is detailed in the Pattern Attributes List below. Each occurrence of the attributes in the pattern string is replaced by its value. Attributes must be between brackets ([ and ]) Example «My string [COL_NAME] is a column 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. |
Pattern Attributes List
The following table lists different parameters values as well as their associated description.
| Parameter Value | Description |
|---|---|
| I_COL | Internal identifier of the column |
| COL_NAME | Name of the column |
| COL_HEADING | Header of the column |
| COL_DESC | Description of the column |
| POS | Position of the column |
| LONGC | Column length (Precision) |
| SCALE | Scale of the column |
| FILE_POS | Beginning (index) of the column |
| BYTES | Number of physical bytes in the column |
| FILE_END_POS | End of the column (FILE_POS + BYTES) |
| IND_WRITE | Write right flag of the column |
| COL_MANDATORY | Mandatory character of the column. Valid values are:
|
| CHECK_FLOW | Flow control flag of the column. Valid values are: (0: do not check, 1: check)
|
| CHECK_STAT | Static control flag of the column. Valid values are: (0: do not check, 1: check)
|
| COL_FORMAT | Logical format of the column |
| COL_DEC_SEP | Decimal symbol of the column |
| REC_CODE_LIST | List of the record codes retained in the column |
| COL_NULL_IF_ERR | Processing flag of the column. Valid values are: (0 = Reject, 1 = Set to null active trace, 2= set to null inactive trace)
|
| DEF_VALUE | Default value of the column |
| ALIAS_SEP | Separator used for the alias (from the technology) |
| SOURCE_DT | Code of the column's datatype. |
| SOURCE_CRE_DT | Create table syntax for the column's datatype. |
| SOURCE_WRI_DT | Create table syntax for the column's writable datatype. |
| DEST_DT | Code of the column's datatype converted to a datatype on the target technology. |
| DEST_CRE_DT | Create table syntax for the column's datatype converted to a datatype on the target technology. |
| DEST_WRI_DT | Create table syntax for the column's writable datatype converted to a datatype on the target technology. |
| SCD_COL_TYPE | Behavior defined for the Slowly Changing Dimensions for this column in the data model. |
| MANDATORY_CLAUSE | Returns NOT NULL is the column 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 column returned by the driver. |
| <flexfield code> | Flexfield value for the current column. |
Selectors Description
| Parameter Value | Description |
|---|---|
| INS |
|
| UPD |
|
| TRG |
|
| NULL |
|
| PK |
|
| UK |
|
| REW |
|
| MAP |
Flow control: All columns of the target table loaded with expressions in the current interface Static control: All columns of the target table |
| SCD_SK | LKM, CKM, IKM: All columns marked SCD Behavior: Surrogate Key in the data model definition. |
| SCD_NK | LKM, CKM, IKM: All columns marked SCD Behavior: Natural Key in the data model definition. |
| SCD_UPD | LKM, CKM, IKM: All columns marked SCD Behavior: Overwrite on Change in the data model definition. |
| SCD_INS | LKM, CKM, IKM: All columns marked SCD Behavior: Add Row on Change in the data model definition. |
| SCD_FLAG | LKM, CKM, IKM: All columns marked SCD Behavior: Current Record Flag in the data model definition. |
| SCD_START | LKM, CKM, IKM: All columns marked SCD Behavior: Starting Timestamp in the data model definition. |
| SCD_END | LKM, CKM, IKM: All columns marked SCD Behavior: Ending Timestamp in the data model definition. |
| WS_INS | SKM: The column is flagged as allowing INSERT using Data Services. |
| WS_UPD | SKM: The column is flagged as allowing UDATE using Data Services. |
| WS_SEL | SKM: The column is flagged as allowing SELECT using Data Services. |
Examples
create table TARGET_COPY <%=odiRef.getTargetColList("(", "[COL_NAME] [DEST_DT] null", ", ", ")", "")%>
Use to return the name of the loading or integration table.
Usage
public java.lang.String getTableName( java.lang.String pProperty)
Description
This method returns the name of the temporary table used for loading or integration. This name is not qualified.
Parameters
| Parameters | Type | Description |
|---|---|---|
| pProperty | String | Parameter that indicates the name of the table to retreive. The list of possible values is:
|
Examples
<%= odiRef.getTableName("COLL_SHORT_NAME") %>
Use to return information about the target table of an interface.
Usage
public java.lang.String getTargetTable(java.lang.String pPropertyName)
Description
This method returns information about the current target table. The list of available data is described in the pPropertyName values table.
In an action, this method returns information on the table being processed by the DDL command.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String that contains the name of the requested property. |
The following table lists the possible values for pPropertyName:
| Parameter Value | Description |
|---|---|
| I_TABLE | Internal identifier of the datastore |
| MODEL_NAME | Name of the model of the current datastore. |
| SUB_MODEL_NAME | Name of the sub-model of the current datastore. |
| TECHNO_NAME | Name of the target technology. |
| LSCHEMA_NAME | Name of the target logical schema. |
| TABLE_NAME | Name of the target datastore. |
| RES_NAME | Physical name of the target resource. |
| CATALOG | Catalog name. |
| WORK_CATALOG | Name of the work catalog. |
| SCHEMA | Schema name |
| WORK_SCHEMA | Name of the work schema. |
| TABLE_ALIAS | Alias of the current datastore. |
| TABLE_TYPE | Type of the datastore. |
| DESCRIPTION | Description of the current interface. |
| TABLE_DESC | Description of the current interface'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. |
Examples
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 an interface.
Usage
public java.lang.String getTemporaryIndex(java.lang.String pPropertyName)
Description
This method returns information relative to a temporary index being created or dropped by an interface.
It can be used in a Loading or Integration Knowledge Module task if the Create Temporary Index option is set to On Source or On Target for this task.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String containing the name of the requested property. |
The following table lists the different possible values for pPropertyName.
| Parameter Value | Description |
|---|---|
| IDX_NAME | Name of the index. This name is computed and prefixed with the temporary index prefix defined for the physical schema. |
| FULL_IDX_NAME | Fully qualified name of the index. On the target tab, this name is qualified to create the index in the work schema of the staging area. On the source tab, this name is qualified to create the index in the source default work schema (LKM) or in the work schema of the staging area (IKM). |
| COLL_NAME | Fully qualified name of the loading table for an LKM. This property does not apply to IKMs. |
| CATALOG | Catalog containing the table to be indexed. |
| SCHEMA | Schema containing the table to be indexed. |
| WORK_CATALOG | Work catalog for the table to be indexed. |
| WORK_SCHEMA | Work schema for the table to be indexed. |
| DEF_CATALOG | Default catalog containing the table to be indexed. |
| DEF_SCHEMA | Default schema containing the table to be indexed. |
| DEF_WORK_CATALOG | Default work catalog for the table to be indexed. |
| DEF_WORK_SCHEMA | Default work schema for the table to be indexed. |
| DEF_WORK_SCHEMA | Default work schema for the table to be indexed. |
| LSCHEMA_NAME | Logical schema of the table to be indexed. |
| TABLE_NAME | Name of the table to be indexed. |
| FULL_TABLE_NAME | Fully qualified name of the table to be indexed. |
| INDEX_TYPE_CODE | Code representing the index type. |
| INDEX_TYPE_CLAUSE | Clause for creating an index of this type. |
| POP_TYPE_CLAUSE | Type of the clause for which the index is generated:
|
| EXPRESSION | Expression of the join or filer clause. Use for debug purposes. |
Examples
Create <%=odiRef.getTemporaryIndex (" [INDEX_TYPE_CLAUSE] index [FULL_IDX_NAME] on [FULL_TABLE_NAME] " )%><%=odiRef.getTemporaryIndexColList("(", "[COL_NAME]", ", ", ")")%>
Use to return information about the columns of a temporary index for an interface.
Usage
public java.lang.String getTemporaryIndexColList(java.lang.String pStart, java.lang.String pPattern, java.lang.String pSeparator, java.lang.String pEnd)
Description
Returns a list of columns of a temporary index.
The parameter pPattern 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 column of the temporary index.
It can be used in a Loading or Integration Knowledge Module task if the Create Temporary Index option is set to On Source or On Target for this task.
Parameters
| Parameter | Type | Description |
|---|---|---|
| Parameter | Type | Description |
| pStart | String | This parameter marks the beginning of the string to generate. |
| pPattern | String | The pattern is repeated for each occurrence in the list.
The list of possible attributes in a pattern is detailed in the Pattern Attributes List below. Each attribute occurrence in the pattern string is substituted with its value. The attributes must be between brackets ([ and ]) Example «My string [COL_NAME] is a column» |
| pSeparator | String | This parameter separates each pattern from its predecessor. |
| pEnd | String | This parameter marks the end of the string to generate. |
Pattern Attributes List
The following table lists the different values of the parameters as well as the associated description.
| Parameter Value | Description |
|---|---|
| CX_COL_NAME | Computed name of the column used as a container for the current expression on the staging area |
| COL_NAME | Name of the column participating to the index. |
| POS | Position of the first occurrence of this column in the join or filter clause this index optimizes. |
Examples
Create <%=odiRef.getTemporaryIndex (" [INDEX_TYPE_CLAUSE] index [FULL_IDX_NAME] on [FULL_TABLE_NAME] " )%><%=odiRef.getTemporaryIndexColList("(", "[COL_NAME]", ", ", ")")%>
Use to return information about the user running the current session.
Usage
public java.lang.String getUser(java.lang.String pPropertyName)
Description
This method returns information about the user executing the current session. The list of available properties is described in the pPropertyName values table.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | String that contains the name of the requested property. |
The following table lists the different possible values for pPropertyName:
| Parameter Value | Description |
|---|---|
| Parameter Value | Description |
| I_USER | User identifier |
| USER_NAME | User name |
| IS_SUPERVISOR | Boolean flag indicating if the user is supervisor (1) or not (0). |
Examples
This execution is performed by <%=odiRef.getUser("USER_NAME")%>
Use to return whether if the current datastore has a primary key.
Usage
public java.lang.Boolean hasPK()
Description
This method returns a boolean. The returned value is true if the datastore for which a web service is being generated has a primary key.
This method can only be used in SKMs.
Examples
<% if (odiRef.hasPK()) { %>
There is a PK :
<%=odiRef.getPK("KEY_NAME")%> : <%=odiRef.getPKColList("{",
"\u0022[COL_NAME]\u0022", ", ", "}")%>
<% } else {%>
There is NO PK.
<% } %>
Use to return whether a column attribute or comment is changed.
Usage
public java.lang.Boolean isColAttrChanged(java.lang.String pPropertyName)
Description
This method is usable in a column action for altering a column attribute or comment. It returns a boolean indicating if the column attribute passed as a parameter has changed.
Parameters
| Parameter | Type | Description |
|---|---|---|
| pPropertyName | String | Attribute code (see below). |
The following table lists the different possible values for pPropertyName
| Parameter Value | Description |
|---|---|
| DATATYPE | Column datatype, length or precision change, |
| LENGTH | Column length change (for example, VARCHAR(10) changes to VARCHAR(12)). |
| PRECISION | Column precision change (for example, DECIMAL(10,3) changes to DECIMAL(10,4)). |
| COMMENT | Column comment change. |
| NULL_TO_NOTNULL | Column nullable attribute change from NULL to NOT NULL. |
| NOTNULL_TO_NULL | Column nullable attribute change from NOT NULL to NULL. |
| NULL | Column nullable attribute change. |
| DEFAULT | Column default value change. |
Examples
<% if (odiRef.IsColAttrChanged("DEFAULT") ) { %>
/* Column default attribute has changed. */
<% } %>
Use to move to the next alternate key for a datastore.
Usage
public java.lang.Boolean nextAK()
Description
This method moves to the next alternate key (AK) of the datastore for which a Web service is being generated.
When first called, this method returns true and positions the current AK to the first AK of the datastore. If there is no AK for the datastore, it returns false.
Subsequent calls position the current AK to the next AKs of the datastore, and return true. If the is no next AK, the method returns false.
This method can be used only in SKMs.
Examples
In the example below, we iterate of all the AKs of the datastore. In each iteration of the while loop, the getAK and getAKColList methods return information on the various AKs of the datastore.
<% while (odiRef.nextAK()) { %>
<%=odiRef.getAK("KEY_NAME")%>
Columns <%=odiRef.getAKColList("{", "\u0022[COL_NAME]\u0022", ",
", "}")%>
Message : <%=odiRef.getAK("MESS")%>
<% } %>
Use to move to the next condition for a datastore.
Usage
public java.lang.Boolean nextCond()
Description
This method moves to the next condition (check constraint) of the datastore for which a Web service is being generated.
When first called, this method returns true and positions the current condition to the first condition of the datastore. If there is no condition for the datastore, it returns false.
Subsequent calls position the current condition to the next conditions of the datastore, and return true. If the is no next condition, the method returns false.
This method can be used only in SKMs.
Examples
In the example below, we iterate of all the conditions of the datastore. In each iteration of the while loop, the getCK method return information on the various conditions of the datastore.
<% while (odiRef.nextCond()) { %>
<%=odiRef.getCK("COND_NAME")%>
SQL :<%=odiRef.getCK("COND_SQL")%>
MESS :<%=odiRef.getCK("MESS")%>
<% } %>
Use to move to the next foreign key for a datastore.
Usage
public java.lang.Boolean nextFK()
Description
This method moves to the next foreign key (FK) of the datastore for which a Web service is being generated.
When first called, this method returns true and positions the current FK to the first FK of the datastore. If there is no FK for the datastore, it returns false.
Subsequent calls position the current FK to the next FKs of the datastore, and return true. If the is no next FK, the method returns false.
This method can be used only in SKMs.
Examples
In the example below, we iterate of all the FKs of the datastore. In each iteration of the while loop, the getFK and getFKColList methods return information on the various FKs of the datastore.
<% while (odiRef.nextFK()) { %>
FK : <%=odiRef.getFK("FK_NAME")%>
Referenced Table : <%=odiRef.getFK("PK_TABLE_NAME")%>
Columns <%=odiRef.getFKColList("{", "\u0022[COL_NAME]\u0022", ",
", "}")%>
Message : <%=odiRef.getFK("MESS")%>
<% } %>
Use to set the number of inserted, updated, deleted or erroneous rows for the current task.
Usage
public java.lang.Void setNbInsert(public java.lang.Long) public java.lang.Void setNbUpdate(public java.lang.Long) public java.lang.Void setNbDelete(public java.lang.Long) public java.lang.Void setNbErrors(public java.lang.Long) public java.lang.Void setNbRows(public java.lang.Long)
Description
These methods set for the current task report the values for:
the number of rows inserted (setNbInsert)
the number of rows updated (setNbUpdate)
the number of rows deleted (setNbDelete)
the number of rows in error (setNbErrors)
total number of rows handled during this task (setNbRows)
These numbers can be set independently from the real number of lines processed.
Note:
This method can be used only within scripting engine commands, such as in Jython code, and should not be enclosed in <%%> tags.Examples
In the Jython example below, we set the number of inserted rows to the constant value of 50, and the number of erroneous rows to a value coming from an ODI variable called #DEMO.NbErrors.
InsertNumber=50 odiRef.setNbInsert(InsertNumber) ErrorNumber=#DEMO.NbErrors odiRef.setNbErrors(ErrorNumber)
Use to set the name of the loading or integration table.
Usage
public java.lang.Void setTableName( java.lang.String pProperty, java.lang.String pTableName)
Description
This method sets the name of temporary table used for loading or integration. this name can be any value.
When using the method, the loading or integration table name is no longer generated by ODI and does not follow the standard naming convention (for example, a loading table will not be prefixed with a C$ prefix). Yet, other methods using this table name will return the newly set value.
The fist parameter pProperty indicates the temporary table name to set. The second parameter can be any valid table name.
Parameters
| Parameters | Type | Description |
|---|---|---|
| pProperty | String | Parameter that indicates the table name to set. The list of possible values is:
|
| pTableName | String | New name for the temporary table. |
Examples
<% odiRef.setTableName("COLL_SHORT_NAME", "C" + getInfo("I_SRC_SET")) %>
<% odiRef.setTableName("COLL_SHORT_NAME", odiRef.getOption("Flow # ") + odiRef.getTable("ID")) %>
Use to set the name of a session task in a Knowledge Module, Procedure, or action.
Usage
public java.lang.String setTaskName( java.lang.String taskName)
Description
This method sets the name of a task to the taskName value. This value is set at run-time. This method is available in all Knowledge Modules, procedures, and actions (Global Methods).
Parameters
| Parameters | Type | Description |
|---|---|---|
| taskName | String | Parameter that indicates the task name to set. If this vlaue is empty, the task remains the one defined in the Knowledge Module or Procedure task. |
Examples
<$=odiRef.setTaskName("Create Error Table " + "<%=odiRef.getTable("L","ERR_NAME","W")%>") $>
<$=odiRef.setTaskName("Insert Error for " + "<%=odiRef.getFK("FK_NAME")%>") $>
<$=odiRef.setTaskName("Loading " + "<%=odiRef.getTable("L", "COLL_NAME", "W")%>" + " from " + "<%=odiRef.getSrcTablesList("", "RES_NAME", ",", ".")%>" ) $>
|
 Copyright © 2011, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|