../E14406-01.epub /> ../E14406-01.mobi />

OMBRETRIEVE LOGICAL_TABLE

Purpose

Retrieve details of a logical table.

Prerequisites

Should be in the context of an OBIEE Business Definition Module or use the full path.

Syntax

retrieveLogicalTableCommand =  OMBRETRIEVE LOGICAL_TABLE "QUOTED_STRING" ( 
          "retrieveItemFolderClauseLT" | "retrieveItemFolderItemClauseLT" | 
          "retrieveItemFolderJoinClauseLT" )
     retrieveItemFolderClauseLT =  GET ( "getPropertiesClause" | 
          "getReferenceIconSetClause" | "getItemFolderSCOClauseLT" )
     retrieveItemFolderItemClauseLT =  ITEM "QUOTED_STRING" GET ( 
          "getPropertiesClause" | SOURCE_OBJECTS | ( REF | REFERENCE ) 
          DRILL_LEVELS | ( REF | REFERENCE ) LOCAL JOINS | ( REF | REFERENCE ) 
          REMOTE JOINS )
     retrieveItemFolderJoinClauseLT =  JOIN "QUOTED_STRING" ( 
          "retrieveItemFolderJoinComponentClause" | GET ( "getPropertiesClause" 
          | JOIN_COMPONENTS | ( REF | REFERENCE ) FOREIGN_KEY | ( REF | 
          REFERENCE ) REMOTE LOGICAL_TABLE ) )
     getPropertiesClause =  PROPERTIES "(" "propertyNameList" ")"
     getReferenceIconSetClause =  ( REF | REFERENCE ) ICONSET
     getItemFolderSCOClauseLT =  ITEMS | JOINS | SOURCE_OBJECTS | 
          DERIVATION_SOURCE | USING FOREIGN_KEYS | ( REF | REFERENCE ) 
          CATALOG_FOLDERS | ( REF | REFERENCE ) REMOTE JOINS | ( REF | REFERENCE
           ) ROLE | ( REF | REFERENCE ) LEVEL | DEPENDENTS
     retrieveItemFolderJoinComponentClause =  JOIN_COMPONENT "QUOTED_STRING" GET
           ( "getPropertiesClause" | ( REF | REFERENCE ) LOCAL ITEM | ( REF | 
          REFERENCE ) REMOTE ITEM )
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }

Parameters

retrieveLogicalTableCommand

To retrieve a logical table.

QUOTED_STRING

name of the logical table.

retrieveItemFolderClauseLT

This clause retrieves the contents of a logical table.

GET

For a logical table, this clause retrieves the following

ITEMS retrieves the items in the logical table.

JOINS retrieves the joins in the logical table.

SOURCE_OBJECTS retrieves the objects the logical table is based on.

DERIVATION_SOURCE retrieves the source object derived into this logical table.

REF USING FOREIGN_KEYS retrieves the foreign keys usages for the logical table.

REF CATALOG_FOLDERS retrieves the catalog folders the logical table belongs to.

REF ROLE retrieves the dimension use reference for the logical table.

REF LEVEL retrieves the level reference for the logical table.

DEPENDENTS retrieves the dependents for the logical table.

retrieveItemFolderItemClauseLT

Retrieves a specific item.

QUOTED_STRING

name of the item.

GET

For an item, this clause retrieves the following

SOURCE_OBJECT retrieves the object that the item is based on.

In the case of a complex item (i.e. an expression) the list of items referenced by the current item

REF DRILL LEVELS retrieves the list of Drill Levels that reference this Item.

REF LOCAL JOINS retrieves the list of Join Components that reference this Item as their local Item.

REF REMOTE JOINS retrieves the list of Join Components that reference this Item as their remote Item.

retrieveItemFolderJoinClauseLT

Retrieves a specific join.

QUOTED_STRING

name of the join.

GET

For join this clause retrieves the following

JOIN_COMPONENTS retrieves the list of Join Components of this Join.

REF FOREIGN KEY retrieves the foreign key associated with this join.

ombretrieve_logical_table$getItemFolderSCOClauseLT = Retrieves the contents of the logical table.

getPropertiesClause

Retrieves the properties of the object.

getPropertiesClause

Basic properties for ITEM_FOLDER: 

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

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

Name: EXTERNAL_TABLE_NAME
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The physical name for the corresponding table or view.  This is automatically set if the Folder is associated with a Table

Name: VISIBLE
Type: BOOLEAN
Valid Values: Y,N
Default: 'Y'
Whether the item folder should be visible to the user 

Name: DISTINCT_VALUES
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether to select distinct values from the source. Oracle Business Intelligence Modules (OBIEE) only 

Name: BRIDGE_TABLE
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether this represents an intermediate table between a fact and dimension. Oracle Business Intelligence (OBIEE) only 

Name: FOLDER_TYPE
Type: STRING(40)
Valid Values: SIMPLE, COMPLEX
Default: Empty string
The type of item folder

Basic properties for ITEM:

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

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

Name: ALIGNMENT
Type: STRING(40)
Valid Values: GENERAL, LEFT, CENTER, RIGHT
Default: 'GENERAL'
The default alignment for displaying the item

Name: DISPLAY_CASE
Type: STRING(40)
Valid Values: GENERAL, LOWER, UPPER, INITCAPPED
Default: 'GENERAL'
How alphabetic characters should be displayed

Name: CASE_STORAGE
Type: STRING(40)
Valid Values: GENERAL, LOWER, UPPER, MIXED
Default: 'GENERAL'
How alphabetic characters are stored

Name: CONTENT_TYPE
Type: STRING(40)
Valid Values: No Value or FILE. For datatypes such as BLOB, it may contain a file extension such as DOC, AVI, WAV, JPG
Default: Empty string
Details on whether the Item contains a file name or should be processed by an external application

Name: DEFAULT_AGGREGATE
Type: STRING(255)
Valid Values (in a Logical Table): NONE, SUM, AVG, COUNT, COUNT DISTINCT, MAX, MIN, FIRST, LAST, MEDIAN, STDDEV and STDDEV_POP
Default: 'NONE'
Valid Values (in an Item Folder): Detail, AVG, COUNT, MAX, MIN, SUM
Default: 'SUM' when the datatype is Numeric, 'Detail' otherwise
Name of the default rollup function for the item

getPropertiesClause

Name: DEFAULT_POSITION
Type: STRING(40)
Valid Values: MEASURE, TOP OR SIDE, TOP, SIDE, PAGE
Default: 'MEASURE' when the datatype is NUMBER or FLOAT, 'TOP OR SIDE' otherwise
Default position for the item

Name: REPLACE_NULL_WITH
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The value to be displayed for null values

Name: FORMULA
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
The text of the derivation expression for a derived item 

Name: EXTERNAL_COLUMN_NAME
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The external name of the corresponding column.  This is automatically set if the Item is is associated with a Column

Name: FORMAT_MASK
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The display format mask for the item 

Name: HEADING
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The displayed heading text for the item 

For Logical Table Items for OBI EE
Name: DATATYPE
Type: STRING(40)
Valid Values: BINARY, BIT, CHAR, DATE, DATETIME, DOUBLE, 
FLOAT, INT, INTERVAL, LONGVARBINARY, LONGVARCHAR, SMALLINT, 
TIME, TIMESTAMP, TINYINT, UNKNOWN, VARBINARY, VARCHAR
Default: 'VARCHAR'
For Item Folder Items for Oracle Discoverer
Name: DATATYPE
Type: STRING(40)
Valid Values: BINARY_DOUBLE, BINARY_FLOAT, BLOB, CHAR, CLOB, DATE, FLOAT, INTEGER, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, LONG, LONG RAW 
NCHAR, NCLOB, NUMBER, NVARCHAR2, RAW, SYS.ANYDATA, SYS.LCR$_ROW_RECORD, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP WITH TIME ZONE, VARCHAR, VARCHAR2, XMLTYPE
Default: 'VARCHAR2'
The datatype for the item 

Name: VISIBLE
Type: BOOLEAN
Valid Values: Y,N
Default: 'Y'
Whether the item should be visible to the user 

Name: MAX_CHAR_FETCHED
Type: Number
Valid Values: Any valid character string in supported character set.
Default: Empty string
The maximum number of characters fetched for an item 

Name: DEFAULT_WIDTH
Type: Number
Valid Values: Any valid character string in supported character set.
Default: Empty string
The default number of characters to display 

Name: WORD_WRAP
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether wordwrap is allowed in the display

getPropertiesClause

Basic properties for JOIN:

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

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

Name: INCLUDE_UNMATCHED_DETAIL_ROWS
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether detail rows with no related master row should be included in the join

Name: INCLUDE_UNMATCHED_MASTER_ROWS
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether master rows with no related detail rows should be included in the join

Name: EXTERNAL_KEY_NAME
Type: STRING(255)
Valid Values: Any valid character string in supported character set.
Default: Empty string
The external name of the corresponding foreign key.  This is automatically set if the Join is is associated with a Foreign Key 

Name: DETAIL_ALWAYS_HAS_MASTER
Type: BOOLEAN
Valid Values: Y,N
Default: 'Y'
Whether every detail row must reference a unique master row 

Name: ONE_TO_ONE
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether a master row only ever has a single detail row

Basic properties for JOIN_COMPONENT:

Name: JOIN_OPERATOR
Type: STRING(200)
Valid Values: =, <>, <, <=, > or >= 
Default: Empty string
Business name of the join 

Basic properties for CONDITION:

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

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

Name: MATCH_CASE
Type: BOOLEAN
Valid Values: Y,N
Default: 'Y'
Whether the case of alphabetic characters must match exactly

Name: FORMULA
Type: STRING
Valid Values: Any valid character string in supported character set.
Default: Empty string
The expression for the condition

Name: MANDATORY
Type: BOOLEAN
Valid Values: Y,N
Default: 'N'
Whether the Condition is optional or mandatory

getReferenceIconSetClause

Get specified Icon Set.

getItemFolderSCOClauseLT

ombretrieve_logical_table$getItemFolderSCOClauseLT??

retrieveItemFolderJoinComponentClause

Retrieves the join components.

QUOTED_STRING

name of the join component.

GET

For join component this clause retrieves the following

REF LOCAL ITEM retrieves the local item used in the join.

REF REMOTE ITEM retrieves the remote item used in the join.

propertyNameList

This is the list of property names.

LOGICAL_TABLE Object

Table 19-7 LOGICAL_TABLE Object

Property Type Choices Min Max Default Description

BRIDGE_TABLE

BOOLEAN

true, false

none

none

false

Whether the Logical Table represents an intermediate table between a fact table and a dimension table (to resolve many-to-many associations).

DEPLOYABLE

BOOLEAN

true, false

none

none

true

Warehouse Builder generates a set of scripts to create an object only for those objects marked as Deployable = true

DISTINCT_VALUES

BOOLEAN

true, false

none

none

false

Whether to only select distinct values from the source physical table.

EXTERNAL_TABLE_NAME

STRING

none

none

none

empty string

The physical name for the corresponding table or view.

VISIBLE

BOOLEAN

true, false

none

none

false

Whether this object is visible for use in query definitions.