OMBRETRIEVE PLSQL_TABLE_TYPE

Purpose

Retrieve details of the Table Type.

Prerequisites

Should be in the context of a Package.

Syntax

retrievePlSqlTableTypeCommand =  OMBRETRIEVE PLSQL_TABLE_TYPE 
          "QUOTED_STRING" "retrievePlSqlTableTypeClause"
     retrievePlSqlTableTypeClause =  GET "getPropertiesClause"
     getPropertiesClause =  PROPERTIES "(" "propertyNameList" ")"
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }

Parameters

retrievePlSqlTableTypeCommand

Retrieves the details of the Table Type with the given name.

retrievePlSqlTableTypeClause

Retrieves the properties of the table type with the given name.

getPropertiesClause

This clause retrieves all the properties.

getPropertiesClause

Basic properties for PLSQL_TABLE_TYPE: 

Name: BUSINESS_NAME
Type: STRING(200)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Business name of the Table Type

Name: DESCRIPTION
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Description of the Table Type

Name: RETURN_TYPE
Type: STRING(20)
Valid Values: NUMBER, VARCHAR2, VARCHAR, DATE, FLOAT
Default: Empty string
Return type of the Table Type. This can be a scalar type or a PLSQL Record Type.

propertyNameList

The list of properties.

PLSQL_TABLE_TYPE Object

Table 20-9 PLSQL_TABLE_TYPE Object

Property	Type	Choices	Min	Max	Default	Description
GENERATION_COMMENTS	STRING	none	none	none	empty string	Enter additional comments for the generated code.

ATTRIBUTE Object

Table 20-10 ATTRIBUTE Object

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.

OMBRETRIEVE PLSQL_TABLE_TYPE

Purpose

Prerequisites

Syntax

Parameters

retrievePlSqlTableTypeCommand

retrievePlSqlTableTypeClause

getPropertiesClause

getPropertiesClause

propertyNameList

PLSQL_TABLE_TYPE Object

ATTRIBUTE Object

See Also