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

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

OMBALTER VIEW

Purpose

To alter properties and definition of a view.

Prerequisites

In the context of an Oracle Module.

Syntax

alterViewCommand =  OMBALTER ( VIEW "QUOTED_STRING" ( "renameClause" [ 
          "alterPropertiesOrIconSetClause" ] [ "alterViewSCOandDependentClauses"
           ] | "alterPropertiesOrIconSetClause" [ 
          "alterViewSCOandDependentClauses" ] | 
          "alterViewSCOandDependentClauses" ) )
     renameClause =  RENAME TO "QUOTED_STRING"
     alterPropertiesOrIconSetClause =  SET ( "setPropertiesClause" [ SET ( REF |
           REFERENCE ) "setReferenceIconSetClause" | UNSET ( REF | REFERENCE ) 
          "unsetReferenceIconSetClause" ] | ( REF | REFERENCE ) 
          "setReferenceIconSetClause" ) | UNSET ( REF | REFERENCE ) 
          "unsetReferenceIconSetClause"
     alterViewSCOandDependentClauses =  ADD ( "addColumnClauseForAlter" [ 
          "alterViewSCOandDependentClauses" ] | "addViewConstraintClause" { 
          "alterViewConstraintClauses" } | "addDataRuleUsageClause" { 
          "alterDataRuleUsageClauses" } | "addRelationalDependentClause" [ 
          "alterViewSCOandDependentClauses" ] ) | MODIFY ( "modifyColumnClause" 
          [ "alterViewSCOandDependentClauses" ] | "modifyViewConstraintClause" {
           "alterViewConstraintClauses" } | "modifyDataRuleUsageClause" { 
          "alterDataRuleUsageClauses" } ) | DELETE ( "deleteColumnClause" [ 
          "alterViewSCOandDependentClauses" ] | "deleteViewConstraintClause" { 
          "alterViewConstraintClauses" } | "deleteDataRuleUsageClause" { 
          "alterDataRuleUsageClauses" } | "deleteRelationalDependentClause" [ 
          "alterViewSCOandDependentClauses" ] )
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     setReferenceIconSetClause =  ICONSET "QUOTED_STRING"
     unsetReferenceIconSetClause =  ICONSET
     addColumnClauseForAlter =  COLUMN "QUOTED_STRING" [ AT POSITION 
          "INTEGER_LITERAL" ] [ SET "setPropertiesClause" ]
     addViewConstraintClause =  "addUkPkClause" | "addFkClause"
     alterViewConstraintClauses =  ADD "addViewConstraintClause" | MODIFY 
          "modifyViewConstraintClause" | DELETE "deleteViewConstraintClause"
     addDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING" SET REF DATA_RULE
           "QUOTED_STRING" ( GROUP "QUOTED_STRING" SET REF ( TABLE | VIEW | 
          MATERIALIZED_VIEW | EXTERNAL_TABLE ) "QUOTED_STRING" ( ATTRIBUTE 
          "QUOTED_STRING" SET REF COLUMN "QUOTED_STRING" )+ )+ [ SET 
          "setPropertiesClause" ]
     alterDataRuleUsageClauses =  ADD "addDataRuleUsageClause" | MODIFY 
          "modifyDataRuleUsageClause" | DELETE "deleteDataRuleUsageClause"
     addRelationalDependentClause =  ( REFERENCE | REF ) ( TABLE | VIEW | 
          MATERIALIZED_VIEW ) "QUOTED_STRING"
     modifyColumnClause =  COLUMN "QUOTED_STRING" ( "renameClause" [ 
          "moveToClause" ] [ SET "setPropertiesClause" ] | "moveToClause" [ SET 
          "setPropertiesClause" ] | SET "setPropertiesClause" )
     modifyViewConstraintClause =  "modifyUkPkClause" | "modifyFkClause"
     modifyDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING" ( 
          "renameClause" [ SET "setPropertiesClause" ] | SET 
          "setPropertiesClause" )
     deleteColumnClause =  COLUMN "QUOTED_STRING"
     deleteViewConstraintClause =  UNIQUE_KEY "QUOTED_STRING" | PRIMARY_KEY 
          "QUOTED_STRING" | FOREIGN_KEY "QUOTED_STRING"
     deleteDataRuleUsageClause =  DATA_RULE_USAGE "QUOTED_STRING"
     deleteRelationalDependentClause =  ( REFERENCE | REF ) ( TABLE | VIEW | 
          MATERIALIZED_VIEW ) "QUOTED_STRING"
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     propertyValueList =  "propertyValue" { "," "propertyValue" }
     addUkPkClause =  ( UNIQUE_KEY | PRIMARY_KEY ) "QUOTED_STRING" [ SET 
          "setUkPkPropertiesAndReferencesColumnsClauses" ]
     addFkClause =  FOREIGN_KEY "QUOTED_STRING" [ SET "setFkSubClauses" ]
     moveToClause =  MOVE TO POSITION "INTEGER_LITERAL"
     modifyUkPkClause =  ( UNIQUE_KEY | PRIMARY_KEY ) "QUOTED_STRING" ( 
          "renameClause" [ SET "setUkPkPropertiesAndReferencesColumnsClauses" ] 
          | SET "setUkPkPropertiesAndReferencesColumnsClauses" )
     modifyFkClause =  FOREIGN_KEY "QUOTED_STRING" ( "renameClause" [ SET 
          "setFkSubClauses" ] | SET "setFkSubClauses" )
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )
     setUkPkPropertiesAndReferencesColumnsClauses =  "setPropertiesClause" [ SET
           ( REF | REFERENCE ) "constraintColumnReferencesClause" ] | ( REF | 
          REFERENCE ) "constraintColumnReferencesClause"
     setFkSubClauses =  "setPropertiesClause" [ SET ( REF | REFERENCE ) 
          "setFkReferencesClauses" ] | ( REF | REFERENCE ) 
          "setFkReferencesClauses"
     constraintColumnReferencesClause =  COLUMNS "(" "quotedNameList" ")"
     setFkReferencesClauses =  "constraintColumnReferencesClause" [ SET ( REF | 
          REFERENCE ) "constraintUkReferencesClause" ] | 
          "constraintUkReferencesClause" [ SET ( REF | REFERENCE ) 
          "constraintColumnReferencesClause" ]
     quotedNameList =  "QUOTED_STRING" { "," "QUOTED_STRING" }
     constraintUkReferencesClause =  ( UNIQUE_KEY | PRIMARY_KEY ) 
          "QUOTED_STRING" [ OF ( TABLE | VIEW ) "QUOTED_STRING" ]

Parameters

alterViewCommand

This clause alters a view.

QUOTED_STRING

name of the view.

renameClause

renames a table with a different name.

alterViewSCOandDependentClauses

This clause alters the view clause.

setPropertiesClause

Used to set properties (core, logical, physical, user-defined) for views and their columns, unique keys, foreign keys, and primary keys.

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.

addColumnClauseForAlter

This clause adds a column at a particular position.

When you alter a table and add columns to it, the position you specify for a new column must be less than or equal to the number of columns added up to that point in the OMBALTER command.

For example, a table TEMP_TAB contains three columns. You use the following

OMBALTER TABLE command to add three more columns:

OMBALTER TABLE 'TEMP_TAB' \

ADD COLUMN 'C4' AT POSITION 4 \

SET PROPERTIES(DATATYPE,PRECISION) VALUES('NUMBER',7) \

ADD COLUMN 'C5' AT POSITION 6 \

SET PROPERTIES(DATATYPE) VALUES('VARCHAR2') \

ADD COLUMN 'C6' AT POSITION 5 \

SET PROPERTIES(DATATYPE,PRECISION) VALUES('NUMBER',10);

This command does not execute successfully because at the point when you specify the position of the column C5 as 6, the table is contains only 5 columns.

QUOTED_STRING

The column name.

addViewConstraintClause

This clause adds the view's configuration clause.

alterViewConstraintClauses

This clause alters the view's constraint clause.

addDataRuleUsageClause

Add a data rule usage to the relation.

alterDataRuleUsageClauses

Add, modify, or delete data rule usages.

addRelationalDependentClause

This clause adds referential dependencies to other relational objects.

modifyColumnClause

This clause renames, set properties, and move columns.

modifyViewConstraintClause

This clause modifies the view's constraint clause.

modifyDataRuleUsageClause

Rename or modify the properties of a data rule usage.

deleteColumnClause

This clause deletes a column.

deleteViewConstraintClause

This clause deletes the view's constraint.

deleteDataRuleUsageClause

Delete a data rule usage.

deleteRelationalDependentClause

This clause deletes referential dependencies to other relational objects.

propertyNameList

The list of properties.

propertyValueList

The list of property values.

addUkPkClause

This clause adds a unique key and primary key.

QUOTED_STRING

name of the unique key or primary key.

addFkClause

This clause adds foreign key.

QUOTED_STRING

Name of the foreign key.

moveToClause

This clause will move the column to given position.

modifyUkPkClause

This clause modifies a unique or primary key.

modifyFkClause

This clause modifies the foreign key.

propertyValue

This clause adds the property values.

setUkPkPropertiesAndReferencesColumnsClauses

This clause adds properties and references to columns.

setFkSubClauses

This clause sets references to a foreign key.

constraintColumnReferencesClause

This clause provides names of all columns.

setFkReferencesClauses

This clause sets foreign key references.

quotedNameList

This clause gives column names.

constraintUkReferencesClause

The first QUOTED_STRING denotes the UniqueKey or Primay key name, and the latter denotes the table's or view's name.

VIEW Object

Table 5-2 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 5-3 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 5-4 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 5-5 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.