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
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.

OMBALTER VIEW

Purpose

Prerequisites

Syntax

Parameters

alterViewCommand

QUOTED_STRING

renameClause

alterViewSCOandDependentClauses

setPropertiesClause

getPropertiesClause

addColumnClauseForAlter

QUOTED_STRING

addViewConstraintClause

alterViewConstraintClauses

addDataRuleUsageClause

alterDataRuleUsageClauses

addRelationalDependentClause

modifyColumnClause

modifyViewConstraintClause

modifyDataRuleUsageClause

deleteColumnClause

deleteViewConstraintClause

deleteDataRuleUsageClause

deleteRelationalDependentClause

propertyNameList

propertyValueList

addUkPkClause

QUOTED_STRING

addFkClause

QUOTED_STRING

moveToClause

modifyUkPkClause

modifyFkClause

propertyValue

setUkPkPropertiesAndReferencesColumnsClauses

setFkSubClauses

constraintColumnReferencesClause

setFkReferencesClauses

quotedNameList

constraintUkReferencesClause

VIEW Object

COLUMN Object

UNIQUE_KEY Object

FOREIGN_KEY Object

See Also