Oracle® Warehouse Builder OMB*Plus Command Reference 11g Release 2 (11.2) Part Number E14406-01 |
|
|
Mobi · ePub |
retrieveRefCursorTypeCommand = OMBRETRIEVE PLSQL_REF_CURSOR_TYPE "QUOTED_STRING" "retrieveRefCursorTypeClause" retrieveRefCursorTypeClause = GET "getPropertiesClause" getPropertiesClause = PROPERTIES "(" "propertyNameList" ")" propertyNameList = "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
Basic properties for PLSQL_REF_CURSOR_TYPE: Name: BUSINESS_NAME Type: STRING(200) Valid Values: Any valid character string in supported character set. Default: Empty string Business name of the Ref-Cursor Type Name: DESCRIPTION Type: STRING(4000) Valid Values: Any valid character string in supported character set. Default: Empty string Description of the Ref-Cursor Type Name: RETURN_TYPE Type: STRING(20) Valid Values: Any valid character string in supported character set. Default: Empty string Return type of the Ref-Cursor Type. This should be a PLSQL Record Type.
Property | Type | Choices | Min | Max | Default | Description |
---|---|---|---|---|---|---|
ADDRESS_TYPE |
STRING |
NA_ADDRTYPE_DUAL, NA_ADDRTYPE_NORMAL |
none |
none |
NA_ADDRTYPE_NORMAL |
You can designate an address type as Normal or Dual. For example, a dual address occurs when a record contains both a street address and a P.O. Box; this is common with business data. A normal address contains only one type of address. |
AGGMAP_NAME |
STRING |
none |
none |
none |
empty string |
|
ATTRIBUTE_ROLE |
STRING |
END_DATE, LOOKUP_ACTIVE_DATE, MEASURE, NATURAL_KEY, NATURAL_KEY_AND_SURROGATE_KEY, NONE, PARENT_NATURAL_KEY, PARENT_NATURAL_KEY_AND_PARENT_SURROGATE_KEY, PARENT_REF_KEY, PARENT_SURROGATE_KEY, PREV_VALUE, START_DATE, SURROGATE_KEY, TRIGGER |
none |
none |
NONE |
The type of role to indicate how this attribute will be used in history logging. History As Previous Value indicates that this attribute will be used to keep previous value. Surrogate Identifier indicates that this attribute will be used to keep the surrogate identifier. Natural Identifier indicates that this attribute will be used to keep the natural identifier. Effective Time indicates that this attribute will be used as the effective time of the version. Expiration Time indicates that this attribute will be used as the expiration time of the version. Trigger indicates that this attribute will be used to trigger history logging. If none is specified, this attribute will be used to keep current value. |
ATTR_NOT_NULL |
BOOLEAN |
true, false |
none |
none |
false |
|
BINDING_COLUMN_NAME |
STRING |
none |
none |
none |
empty string |
The binding column name for this attribute |
BOUND_NAME |
STRING |
none |
none |
none |
empty string |
The name to be used by the code generator to identify this item. By default it is the same physical name as the item. |
CODE_TEMPLATE_SCD |
STRING(9) |
SCD_END, SCD_FLAG, SCD_INS, SCD_NK, SCD_SK, SCD_START, SCD_UND, SCD_UPD |
none |
none |
SCD_UND |
Slowly changing dimension behavior for this attribute. |
CODE_TEMPLATE_UPDATE |
STRING(3) |
NO, YES |
none |
none |
YES |
A boolean value indicating whether this attribute will included in code template functions using the UPD (Update) tag. |
CODE_TEMPLATE_USER_DEFINED_1 |
STRING(3) |
NO, YES |
none |
none |
NO |
A boolean value indicating whether this attribute will included in code template functions using the UD1 (User Defined 1) tag. |
CODE_TEMPLATE_USER_DEFINED_2 |
STRING(3) |
NO, YES |
none |
none |
NO |
A boolean value indicating whether this attribute will included in code template functions using the UD2 (User Defined 2) tag. |
CODE_TEMPLATE_USER_DEFINED_3 |
STRING(3) |
NO, YES |
none |
none |
NO |
A boolean value indicating whether this attribute will included in code template functions using the UD3 (User Defined 3) tag. |
CODE_TEMPLATE_USER_DEFINED_4 |
STRING(3) |
NO, YES |
none |
none |
NO |
A boolean value indicating whether this attribute will included in code template functions using the UD4 (User Defined 4) tag. |
CODE_TEMPLATE_USER_DEFINED_5 |
STRING(3) |
NO, YES |
none |
none |
NO |
A boolean value indicating whether this attribute will included in code template functions using the UD5 (User Defined 5) tag. |
COLUMN_USAGE |
STRING |
CDC_COL, ROWID, ROWNUM, TABLE_COL |
none |
none |
TABLE_COL |
|
CONTROL_FLOW |
BOOLEAN |
true, false |
none |
none |
false |
|
CONTROL_STATIC |
BOOLEAN |
true, false |
none |
none |
false |
|
DATA_TYPE |
STRING |
none |
none |
none |
empty string |
The data type of the attribute |
DEFAULT_VALUE |
STRING |
none |
none |
none |
empty string |
The default value for the attribute, used for orphan management default parent record creation. |
DIMENSION_ATTRIBUTE_NAME |
STRING |
none |
none |
none |
empty string |
The dimension attribute referenced to by this level attribute. |
EXPRESSION |
STRING |
none |
none |
none |
empty string |
The output expression for the attribute |
FIELD_DATA_TYPE |
STRING |
BYTEINT, CHAR, DATE, DECIMAL, DECIMAL EXTERNAL, DOUBLE, FLOAT, FLOAT EXTERNAL, GRAPHIC, GRAPHIC EXTERNAL, INTEGER, INTEGER EXTERNAL, INTEGER UNSIGNED, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, LONG VARRAW, RAW, SMALLINT, SMALLINT UNSIGNED, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE, VARCHAR, VARCHARC, VARGRAPHIC, VARRAW, VARRAWC, ZONED, ZONED EXTERNAL |
none |
none |
CHAR |
SQL Data Type of the field in the file to which this operator is bound. |
FIELD_DATA_TYPE_LENGTH |
NUMBER |
none |
none |
none |
0 |
Field Length of the field in the file to which this operator is bound. |
FIELD_DATA_TYPE_PRECISION |
NUMBER |
none |
none |
none |
0 |
Field Precision of the field in the file to which this operator is bound. |
FIELD_DATA_TYPE_SCALE |
NUMBER |
none |
none |
none |
0 |
Field Scale of the field in the file to which this operator is bound. |
FIELD_DEFAULTIF_VALUE |
STRING |
none |
none |
none |
empty string |
The expression that indicates the value of the field is blank or zero, based on the datatype. |
FIELD_END_POSITION |
NUMBER |
none |
none |
none |
0 |
The ending position of the field in the file |
FIELD_MASK |
STRING |
none |
none |
none |
empty string |
The mask for the field |
FIELD_NULLIF_VALUE |
STRING |
none |
none |
none |
empty string |
The expression that indicates the value of the field is null |
FIELD_START_POSITION |
NUMBER |
none |
none |
none |
0 |
The starting position of the field in the file |
FRACTIONAL_SECONDS_PRECISION |
NUMBER |
none |
none |
none |
0 |
The data fractional seconds precision value of the attribute |
FUNCTION_RETURN |
BOOLEAN |
true, false |
none |
none |
false |
Specifies whether this output is the return value of this function |
GROUP_KEY |
BOOLEAN |
true, false |
none |
none |
false |
A boolean value to indicate whether this input attribute is a part of the unpivot group key. |
HISTORY_ATTRIBUTE |
STRING |
none |
none |
none |
empty string |
The bound name of the attribute to indicate where the history of the current attribute is to be logged. |
INPUT_ROLE |
STRING |
NA_ADDRESS, NA_ADDRESS2, NA_CITY, NA_COUNTRYCODE, NA_COUNTRYNAME, NA_FIRMNAME, NA_FIRSTNAME, NA_FIRSTPARTNAME, NA_LASTLINE, NA_LASTLINE_2, NA_LASTNAME, NA_LASTPARTNAME, NA_LINE1, NA_LINE10, NA_LINE2, NA_LINE3, NA_LINE4, NA_LINE5, NA_LINE6, NA_LINE7, NA_LINE8, NA_LINE9, NA_LOCALITYNAME, NA_LOCALITY_2, NA_LOCALITY_3, NA_LOCALITY_4, NA_MIDDLENAME, NA_MIDDLENAME2, NA_MIDDLENAME3, NA_NEIGHBORHOOD, NA_NONE, NA_PASSTHRU, NA_PERSON, NA_PERSON2, NA_PERSON3, NA_POSTALCODE, NA_POSTNAME, NA_PRENAME, NA_PRIMARYADDRESS, NA_SECONDARYADDRESS, NA_STATE |
none |
none |
NA_NONE |
Assigns a name-address input role to the selected input attribute |
INSTANCE |
STRING |
NA_INSTANCE_FIFTH, NA_INSTANCE_FIRST, NA_INSTANCE_FOURTH, NA_INSTANCE_SECOND, NA_INSTANCE_SIXTH, NA_INSTANCE_THIRD |
none |
none |
NA_INSTANCE_FIRST |
The instance option is used when an address contains multiple names, you can specify which name in the group should be used. In addition, you can use this option to assign an address type to a miscellaneous address component. |
IN_MATCHING_ATTRIBUTE |
STRING |
none |
none |
none |
empty string |
Defines the matching output attribute for the "IN" subquery |
IS_AGGREGATION |
STRING |
NO, YES |
none |
none |
NO |
|
IS_CAST_TARGET |
BOOLEAN |
true, false |
none |
none |
false |
Description not available. |
IS_CHUNKING_COLUMN |
BOOLEAN |
true, false |
none |
none |
false |
This Number Column will be used as the chunking column . |
IS_OPTIONAL |
BOOLEAN |
true, false |
none |
none |
false |
If true, the input is not required to be connected |
IS_PREDEFINED_CONSTANT |
BOOLEAN |
true, false |
none |
none |
false |
Description not available. |
IS_VARIABLE_EXPRESSION |
BOOLEAN |
true, false |
none |
none |
false |
If true, the expression value is stored as a runtime variable. The value is altered only when the write condition is true. |
JOIN_INPUT_UOID |
STRING |
none |
none |
none |
empty string |
|
JOIN_OUTPUT_UOID |
STRING |
none |
none |
none |
empty string |
|
LENGTH |
NUMBER |
none |
none |
none |
0 |
|
LEVEL_ATTRIBUTE_COLNAME_NAME |
STRING |
none |
none |
none |
empty string |
Column name in the AW staging table and source view for this attribute. |
LEVEL_ATTRIBUTE_ISPARENT |
STRING |
NO, YES |
none |
none |
NO |
Indicates whether this is a parent level. |
LEVEL_ATTRIBUTE_LEVEL_INDICATOR_COLNAME |
STRING |
none |
none |
none |
empty string |
Level indicating columns name. This level will contain the name of the name of the level this parent reference belongs to. This is relavant only for skip level hierarchies. |
LEVEL_ATTRIBUTE_NAME |
STRING |
none |
none |
none |
empty string |
The name of the Level Attribute. |
LEVEL_ATTRIBUTE_OBJECT_ID |
STRING |
none |
none |
none |
empty string |
|
LEVEL_PARENTHIERARCHY |
STRING |
none |
none |
none |
empty string |
Name of the parent hierarchy. |
LEVEL_PARENTREF_PARENTLEVEL |
STRING |
none |
none |
none |
empty string |
The name of the parent level if relavant. |
LEVEL_RELATIONSHIP_NAME |
STRING |
none |
none |
none |
empty string |
The level relationship name associated to this attribute. |
LOADING_FLAG |
STRING |
none |
none |
none |
YES |
A boolean value to indicate whether this attribute will particiate in the load operation. |
LOAD_COLUMN_WHEN_INSERTING_ROW |
STRING(3) |
NO, YES |
none |
none |
YES |
A boolean value to indicate whether this attribute will particiate in the insert load operation. |
LOAD_COLUMN_WHEN_UPDATING_ROW |
STRING(3) |
NO, YES |
none |
none |
YES |
A boolean value to indicate whether this attribute will particiate in the update load operation. |
LOAD_MATCHING |
STRING |
NO, YES |
none |
none |
NO |
A boolean value to indicate whether this attribute will be used to construct the matching criteria between the incoming data and the existing data on the target during the load operation. |
LOAD_ON_INSERT |
STRING(3) |
NO, YES |
none |
none |
YES |
A boolean value to indicate whether this level attribute will participate in an insert when inserting a new record. Has no effect for SCD type 2 trigger or business key attributes. |
LOAD_ON_UPDATE |
STRING(3) |
NO, YES |
none |
none |
YES |
A boolean value to indicate whether this level attribute will participate in an update when updating an existing record. Has no effect for SCD type 2 trigger attributes. |
LOOKUP_ATTRIBUTE_ROLE |
STRING |
DIMENSION_KEY, END_DATE, NATURAL_KEY, NATURAL_KEY_AND_SURROGATE_KEY, NONE, PREV_VALUE, START_DATE, SURROGATE_KEY, TRIGGER |
none |
none |
NONE |
The role of the attribute, synchronized from the referencing lookup table or level |
LOOKUP_MATCHING |
STRING |
NO, YES |
none |
none |
NO |
A boolean value to indicate whether this attribute will be used to construct the lookup condition to resolve level reference between the incoming child data and the existing parent data on the target during the selected operation. |
MATCHING_ROW |
NUMBER |
none |
1 |
1000 |
1 |
An positive integer to indicate from which row within the unpivot group this output attribute obtains its data. |
MATCH_COLUMN_WHEN_DELETING_ROW |
STRING(3) |
NO, YES |
none |
none |
NO |
A boolean value to indicate whether this attribute will be used to construct the matching criteria between the incoming data and the existing data on the target during the delete load operation. |
MATCH_COLUMN_WHEN_UPDATING_ROW |
STRING(3) |
NO, YES |
none |
none |
NO |
A boolean value to indicate whether this attribute will be used to construct the matching criteria between the incoming data and the existing data on the target during the update load operation. |
MCM_MATCH_VALUE |
STRING |
none |
none |
none |
empty string |
|
MERGE_ATTR |
STRING |
none |
none |
none |
empty string |
Related merge attribute |
NULL_VALUE |
STRING |
none |
none |
none |
NULL |
The data value that is interpreted as "null" by orphan management. Default is SQL NULL, specified as unquoted NULL. |
OUTPUT_COMPONENT |
STRING |
NA_ADDRESS, NA_ADDRESS2, NA_ADDRESSCORRECTED, NA_ADDRESSTYPE, NA_AUTO_ZONE_IND, NA_BOXNAME, NA_BOXNUMBER, NA_BUILDINGNAME, NA_CART, NA_CBSA_CODE, NA_CBSA_DESC, NA_CENSUSID, NA_CHECKDIGIT, NA_CITY, NA_CITYCORRECTED, NA_CITYMATCH, NA_CITYWARNING, NA_CITY_ABBREV, NA_CITY_ABBREV_2, NA_CITY_ALTERNATE, NA_COMPLEX, NA_COUNTRYCODE, NA_COUNTRYCODE3, NA_COUNTRYNAME, NA_COUNTYNAME, NA_DELIVERYBEATCODE, NA_DELIVERYOFFICECODE, NA_DELIVERYPOINT, NA_EMAIL, NA_EXTRA_1, NA_EXTRA_10, NA_EXTRA_11, NA_EXTRA_12, NA_EXTRA_13, NA_EXTRA_14, NA_EXTRA_15, NA_EXTRA_16, NA_EXTRA_17, NA_EXTRA_18, NA_EXTRA_19, NA_EXTRA_2, NA_EXTRA_20, NA_EXTRA_3, NA_EXTRA_4, NA_EXTRA_5, NA_EXTRA_6, NA_EXTRA_7, NA_EXTRA_8, NA_EXTRA_9, NA_FIPS, NA_FIPSCOUNTY, NA_FIPS_PLACE_CODE, NA_FIRMCOUNT, NA_FIRMNAME, NA_FIRM_LOC, NA_FIRSTNAME, NA_FIRSTNAMESTD, NA_GENDER, NA_GEO_MATCH_PREC, NA_INSTALLATIONNAME, NA_INSTALLATIONTYPE, NA_ISADDRESSVERIFIABLE, NA_ISFOUND, NA_ISGOODADDRESS, NA_ISGOODGROUP, NA_ISGOODNAME, NA_ISPARSED, NA_LACS, NA_LASTLINE, NA_LASTLINE_2, NA_LASTNAME, NA_LATITUDE, NA_LOCALITYCODE, NA_LOCALITYNAME, NA_LOCALITY_2, NA_LOCALITY_3, NA_LOCALITY_4, NA_LONGITUDE, NA_LOT, NA_LOT_ORDER, NA_MCD, NA_MIDDLENAME, NA_MIDDLENAME2, NA_MIDDLENAME2STD, NA_MIDDLENAME3, NA_MIDDLENAME3STD, NA_MIDDLENAMESTD, NA_MISCADDRESS, NA_MSA, NA_NAMEDESIGNATOR, NA_NAMEWARNING, NA_NAME_FIRM_EXTRA, NA_NEIGHBORHOOD, NA_NONAMBIGUOUSMATCH, NA_NONE, NA_NP_SEC_ADDR, NA_NP_UNIT_DESIG, NA_NP_UNIT_NBR, NA_OTHERPOSTNAME, NA_PARSESTATUS, NA_PARSESTATUSDESC, NA_PARSINGCOUNTRY, NA_PASSTHRU, NA_PERSON, NA_PERSONCOUNT, NA_PHONE, NA_POSTALCODE, NA_POSTALCODECORRECTED, NA_POSTALCODEFORMATTED, NA_POSTDIRECTIONAL, NA_POSTNAME, NA_PREDIRECTIONAL, NA_PRENAME, NA_PRIMARYADDRESS, NA_PRIM_NAME_2, NA_RELATIONSHIP, NA_ROUTENAME, NA_ROUTENUMBER, NA_SECONDARYADDRESS, NA_SSN, NA_STATE, NA_STREETCOMPCORRECTED, NA_STREETCOMPMATCH, NA_STREETCORRECTED, NA_STREETNAME, NA_STREETNAMEMATCH, NA_STREETNUMBER, NA_STREETNUMBERMATCH, NA_STREETTYPE, NA_STREETWARNING, NA_TITLE, NA_UNITDESIGNATOR, NA_UNITNUMBER, NA_URBANIZATIONNAME, NA_URBAN_IND, NA_ZIP4, NA_ZIP5 |
none |
none |
NA_NONE |
Assigns a Name and Address output component to the selected output attribute. |
OUTPUT_GROUP_KEY |
BOOLEAN |
true, false |
none |
none |
false |
A boolean value to indicate whether this output attribute is a part of the unpivot group key, which obtains its value from its corresponding input attribute. |
PIVOT_EXPRESSION |
STRING |
none |
none |
none |
empty string |
A comma-separated expression that gives the input attribute to be used for each output row in the pivot group. |
PRECISION |
NUMBER |
none |
none |
none |
0 |
The data precision value of the attribute |
REFERENCED_LEVEL_ATTRIBUTE_NAME |
STRING |
none |
none |
none |
empty string |
The bound name of the parent level attribute associated to this attribute. |
REFERENCED_LEVEL_NAME |
STRING |
none |
none |
none |
empty string |
The bound name of the parent level group associated to this attribute. |
REFERENCING_TYPE |
STRING |
LOOKUP_KEY, LOOKUP_KEY_AND_REFERENCE_KEY_ALL, LOOKUP_KEY_AND_REFERENCE_KEY_ONLY, NONE, REFERENCE_KEY_ALL, REFERENCE_KEY_ONLY |
none |
none |
NONE |
The type of reference to indicate how this attribute participates in resolving existing level relationships and level implementations. If Lookup Reference Attribute is specified, this attribute will be used as lookup attribute upon parent level to resolve level implementations during loading. If Level Relationship Attribute (Snowflake) is specified, this attribute will be directly used as level relationship attribute and no lookup upon parent level would be performed during loading. If Level Relationship Attribute (Star) is specified, this attribute will be directly used as level relationship attribute, as well as lookup attribute upon parent level to resolve level implementations during loading. If none is specified, this attribute does not participate in any level relationship. |
REMOVE_MATCHING |
STRING |
NO, YES |
none |
none |
NO |
A boolean value to indicate whether this attribute will be used to construct the matching criteria between the incoming data and the existing data on the target during the remove operation. |
REPRESENTS_LOOKUP_TABLE_COLUMN |
BOOLEAN |
true, false |
none |
none |
false |
|
SCALE |
NUMBER |
none |
none |
none |
0 |
The data scale value of the attribute |
SKIP_LEVEL_DIMENSION |
STRING |
NO, YES |
none |
none |
NO |
Indicates whether this level has a skip level parent. |
SOURCE_AGGREGATION_FUNCTION |
STRING |
none |
none |
none |
SUM |
The source loading aggregation function for the measure. |
SUBQUERY_OUTPUT_MATCHING |
STRING |
none |
none |
none |
empty string |
A unique matching value which will match a corresponding property for an output attribute |
TYPE_ATTRIBUTE_NAME |
STRING |
none |
none |
none |
empty string |
The name of the field of the PLS Record or attribute of the Object Type or column of the ROWTYPE that corresponds to this attribute. This property is not applicable if the return type is TABLE of SCALAR. |
TYPE_TWO_SCD_TRIGGER |
STRING |
NO, YES |
none |
none |
NO |
If YES, indicates that this attribute will be used as a trigger for type 2 Slowly Changing Dimension history recording. |
UNPIVOT_EXPRESSION |
STRING |
none |
none |
none |
NULL |
An expression that gives the input attribute to be used as the output of this attribute. |
UPDATE_OPERATION |
STRING(3) |
*=, +=, -=, /=, =, =-, =/, =||, ||= |
none |
none |
= |
The computation to be performed on this attribute between the incoming data and the existing data on the target during the update load operation. Only applicable to measure attributes. |
VALUETYPE |
STRING |
NEW, NEW_OLD, OLD |
none |
none |
NEW_OLD |
Specifies the value type of this attribute |
VARIABLE_INITIAL_VALUE |
STRING |
none |
none |
none |
empty string |
If this is a variable expression, then this is the initial value of the variable expression. |
VARIABLE_WRITE_CONDITION |
STRING |
none |
none |
none |
empty string |
If this is a variable expression, then the variable value is set when processing a row where this condition is true. |