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

OMBRETRIEVE VIEW

Purpose

To retrieve properties of a view.

Prerequisites

In the context of an Oracle Module

Syntax

retrieveViewCommand =  OMBRETRIEVE VIEW "QUOTED_STRING" ( 
          "retrieveViewClause" | "retrieveColumnClause" | "retrieveUkPkClause" |
           "retrieveFkClause" | "retrieveDataRuleUsageClause" )
     retrieveViewClause =  GET ( "getPropertiesClause" | 
          "getReferenceIconSetClause" | "getViewSCOorDependentsClause" )
     retrieveColumnClause =  COLUMN "QUOTED_STRING" GET "getPropertiesClause"
     retrieveUkPkClause =  ( UNIQUE_KEY | PRIMARY_KEY ) "QUOTED_STRING" GET ( 
          "getPropertiesClause" | COLUMNS )
     retrieveFkClause =  FOREIGN_KEY "QUOTED_STRING" GET ( "getPropertiesClause"
           | COLUMNS | UNIQUE_KEY | PRIMARY_KEY | REFERENCED_KEY )
     retrieveDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING" ( GET ( 
          "getPropertiesClause" | GROUPS ) | GROUP "QUOTED_STRING" ( GET ( 
          "getPropertiesClause" | ATTRIBUTES | REF ( TABLE | VIEW | 
          MATERIALIZED_VIEW | EXTERNAL_TABLE ) ) | ATTRIBUTE "QUOTED_STRING" ( 
          GET ( "getPropertiesClause" | REF COLUMN ) ) ) )
     getPropertiesClause =  PROPERTIES "(" "propertyNameList" ")"
     getReferenceIconSetClause =  ( REF | REFERENCE ) ICONSET
     getViewSCOorDependentsClause =  COLUMNS | UNIQUE_KEYS | PRIMARY_KEY | 
          FOREIGN_KEYS | COLUMN AT POSITION "INTEGER_LITERAL" | DATA_RULE_USAGES
           | ATTRIBUTE_SETS | ( REF | REFERENCE ) ( TABLES | VIEWS | 
          MATERIALIZED_VIEWS )
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }

Parameters

retrieveViewCommand

This clause retrieves a view.

QUOTED_STRING

name of the view.

retrieveViewClause

This clause will retrieve a view.

retrieveColumnClause

This clause will retrieve columns.

QUOTED_STRING

Name of the column.

retrieveUkPkClause

This clause will retrieve a unique key or primary key.

QUOTED_STRING

Name of the unique key or the primary key.

retrieveFkClause

This clause will retrieve a key referenced by a foreign key, either a unique key or primary key. Use REFERENCED_KEY to retrieve the referenced key for a foreign key regardless of the type of referenced key (unique or primary).

QUOTED_STRING

Name of the foreign key.

retrieveDataRuleUsageClause

This clause retrieves the data rule usages.

QUOTED_STRING

Name of data rule usage, group or attribute.

GROUPS

Retrieve the names of all relation groups in the data rule usage.

ATTRIBUTES

Retrieve the names of all attributes in a data rule usage group.

TABLE

Table name associated withthe data rule usage group.

VIEW

View name associated withthe data rule usage group.

MATERIALIZED_VIEW

Materialized view name associated with the data rule usage group.

EXTERNAL_TABLE

External table name associated with the data rule usage group.

COLUMN

Column name associated with the data rule usage group attribute.

getPropertiesClause

This clause retrieves all the properties.

getPropertiesClause

Note:

Constraints can be specified but will not be generated for either View or Materialized View in this release.


Basic properties for VIEW, MATERIALIZED_VIEW: 

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

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

Name: VIEW_QUERY
Type: STRING(4000)
Valid Values: Any valid character string in supported character set.
Default: Empty string
Sets the query definition in View and MaterializedView.

getViewSCOorDependentsClause

This clause will retrieves view components like columns, keys, etc. or relational objects that this view hav referential dependency on.

propertyNameList

The list of properties.

VIEW Object

Table 21-14 VIEW Object

Property Type Choices Min Max Default Description

DEPLOYABLE

BOOLEAN

true, false

none

none

false

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

ERROR_TABLESPACE

STRING(30)

none

none

none

empty string

Use the Tablespace parameter to specify the name of tablespace.

ERROR_TABLE_NAME

STRING(30)

none

none

none

empty string

Use the error table name to specify the name of Error Table.

GENERATE_ERROR_TABLE_ONLY

BOOLEAN

true, false

none

none

false

Create, drop, replace or upgrade only the error table.

GENERATION_COMMENTS

STRING

none

none

none

empty string

Enter additional comments for the generated code.


COLUMN Object

Table 21-15 COLUMN Object

Property Type Choices Min Max Default Description

META_IMPORT_BYTES_PER_CHAR

NUMBER

none

none

none

1

 

META_IMPORT_CHARSET

STRING(45)

none

none

none

empty string

 

META_IMPORT_USE_CHAR_SEMANTICS

BOOLEAN

true, false

none

none

false

 

UNIQUE_KEY Object

Table 21-16 UNIQUE_KEY Object

Property Type Choices Min Max Default Description

DB_LOCATION

STRING

none

none

none

empty string

Location for referenced database objects.

DEFERRABLE

STRING

, DEFERRABLE, NOT DEFERRABLE

none

none

empty string

Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The default is NOT DEFERRABLE.

DEPLOYABLE

BOOLEAN

true, false

none

none

true

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

ENABLECONSTRAINT

STRING

, DISABLE, ENABLE

none

none

empty string

Specify ENABLE if you want the constraint to be applied to the data in the table. Specify DISABLE to disable the integrity constraint. The default is ENABLE.

EXCEPTIONSINTO

STRING

none

none

none

empty string

Specify an exceptions table ([schema.]table). The EXCEPTIONS table or the table you specify must exist on your local database. If you create your own exceptions table, then it must follow the format prescribed by one of the two scripts supplied by Oracle. Do not use this property with NOVALIDATE option.

INDEX_TABLESPACE

STRING(30)

none

none

none

empty string

Specify index tablespace to be used for a constraint if created as an index.

INITIALLY

STRING

, DEFERRED, IMMEDIATE

none

none

empty string

Specify (INITIALLY) IMMEDIATE to indicate that Oracle should check a DEFERRABLE constraint at the end of each subsequent SQL statement. Specify (INITIALLY) DEFERRED to indicate that Oracle should check a DEFERRABLE constraint at the end of subsequent transactions. The default is (INITIALLY) IMMEDIATE.

RELY

STRING

, NORELY, RELY

none

none

empty string

Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The default is NORELY.

SUBSTITUTE_KEY

BOOLEAN

true, false

none

none

false

This is related to Streams Support. If this is true, deployment will result only in creation of the key metadata. The constraint itself will not be enforced. This will be done by creating a Streams substitute key.

USING_INDEX

BOOLEAN

true, false

none

none

false

Specify True to create a constraint as an index.

VALIDATECONSTRAINT

STRING

, NOVALIDATE, VALIDATE

none

none

empty string

The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. (ENABLE) VALIDATE specifies that all old and new data must compliy with the constraint. (ENABLE) NOVALIDATE only ensures that all new DML operations on the constrained data comply with the constraint. (DISABLE) VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. (DISABLE) NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). The default is NOVALIDATE.


FOREIGN_KEY Object

Table 21-17 FOREIGN_KEY Object

Property Type Choices Min Max Default Description

DB_LOCATION

STRING

none

none

none

empty string

Location for referenced database objects.

DEFERRABLE

STRING

, DEFERRABLE, NOT DEFERRABLE

none

none

empty string

Specify DEFERRABLE to indicate that in subsequent transactions you can use the SET CONSTRAINT[S] clause to defer checking of this constraint until after the transaction is committed. Specify NOT DEFERRABLE to indicate that in subsequent transactions you cannot use the SET CONSTRAINT[S] clause to defer checking of this constraint until the transaction is committed. The default is NOT DEFERRABLE.

DEPLOYABLE

BOOLEAN

true, false

none

none

true

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

ENABLECONSTRAINT

STRING

, DISABLE, ENABLE

none

none

empty string

Specify ENABLE if you want the constraint to be applied to the data in the table. Specify DISABLE to disable the integrity constraint. The default is ENABLE.

EXCEPTIONSINTO

STRING

none

none

none

empty string

Specify an exceptions table ([schema.]table). The EXCEPTIONS table or the table you specify must exist on your local database. If you create your own exceptions table, then it must follow the format prescribed by one of the two scripts supplied by Oracle. Do not use this property with NOVALIDATE option.

INITIALLY

STRING

, DEFERRED, IMMEDIATE

none

none

empty string

Specify (INITIALLY) IMMEDIATE to indicate that Oracle should check a DEFERRABLE constraint at the end of each subsequent SQL statement. Specify (INITIALLY) DEFERRED to indicate that Oracle should check a DEFERRABLE constraint at the end of subsequent transactions. The default is (INITIALLY) IMMEDIATE.

ONDELETE

STRING

, CASCADE, SET NULL

none

none

empty string

Specify CASCADE if you want Oracle to remove dependent foreign key values. Specify SET NULL if you want Oracle to convert dependent foreign key values to NULL.

RELY

STRING

, NORELY, RELY

none

none

empty string

Specify RELY to activate an existing constraint in NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The default is NORELY.

SUBSTITUTE_KEY

BOOLEAN

true, false

none

none

false

This is related to Streams Support. If this is true, deployment will result only in creation of the key metadata. The constraint itself will not be enforced. This will be done by creating a Streams substitute key.

VALIDATECONSTRAINT

STRING

, NOVALIDATE, VALIDATE

none

none

empty string

The behavior of VALIDATE and NOVALIDATE always depends on whether the constraint is enabled or disabled, either explicitly or by default. (ENABLE) VALIDATE specifies that all old and new data must compliy with the constraint. (ENABLE) NOVALIDATE only ensures that all new DML operations on the constrained data comply with the constraint. (DISABLE) VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. (DISABLE) NOVALIDATE signifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated). The default is NOVALIDATE.