Skip Headers
Oracle® Warehouse Builder OMB*Plus Command Reference
11g Release 2 (11.2)

Part Number E14406-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
Mobi · ePub

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.