OMBCREATE VIEW

Purpose

To create a view.

Prerequisites

In the context of an Oracle Module.

Syntax

createViewCommand =  OMBCREATE ( VIEW "QUOTED_STRING" [ SET ( 
          "setPropertiesClause" [ SET ( REF | REFERENCE ) 
          "setReferenceIconSetClause" ] | ( REF | REFERENCE ) 
          "setReferenceIconSetClause" ) ] [ "addViewSCOandDependentClause" ] )
     setPropertiesClause =  PROPERTIES "(" "propertyNameList" ")" VALUES "(" 
          "propertyValueList" ")"
     setReferenceIconSetClause =  ICONSET "QUOTED_STRING"
     addViewSCOandDependentClause =  ADD ( "addColumnClause" | 
          "addViewConstraintClause" | "addRelationalDependentClause" ) [ 
          "addViewSCOandDependentClause" ]
     propertyNameList =  "UNQUOTED_STRING" { "," "UNQUOTED_STRING" }
     propertyValueList =  "propertyValue" { "," "propertyValue" }
     addColumnClause =  COLUMN "QUOTED_STRING" [ AT POSITION "INTEGER_LITERAL" ]
           [ SET "setPropertiesClause" ]
     addViewConstraintClause =  "addUkPkClause" | "addFkClause"
     addRelationalDependentClause =  ( REFERENCE | REF ) ( TABLE | VIEW | 
          MATERIALIZED_VIEW ) "QUOTED_STRING"
     propertyValue =  ( "QUOTED_STRING" | "INTEGER_LITERAL" | 
          "FLOATING_POINT_LITERAL" )
     addUkPkClause =  ( UNIQUE_KEY | PRIMARY_KEY ) "QUOTED_STRING" [ SET 
          "setUkPkPropertiesAndReferencesColumnsClauses" ]
     addFkClause =  FOREIGN_KEY "QUOTED_STRING" [ SET "setFkSubClauses" ]
     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

createViewCommand

This command creates a view.

QUOTED_STRING

Specify the name of the view to be created.

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.

addViewSCOandDependentClause

This clause adds components like columns and constraints, as well as dependencies to some other relational objects.

propertyNameList

The list of properties.

propertyValueList

The list of property values.

addColumnClause

This clause adds a column.

When you create a table or alter a table to add a set of columns, the

position that you specify for a column must be either less than or equal to

the number of columns that you have listed up to that point in the command.

For example, the following OMBCREATE command does not add the specified

columns to the table:

OMBCREATE TABLE 'MY_TABLE' \

SET PROPERTIES (DESCRIPTION) VALUES ('TRIAL TABLE') \

ADD COLUMN 'C1' \

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

ADD COLUMN 'C2' AT POSITION 3 \

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

ADD COLUMN 'C3' AT POSITION 2 \

SET PROPERTIES (DATATYPE) VALUES ('VARCHAR2')

This is because at the point when you specify the position of the column

C2 as 3, you have added just two columns to the table.But the following

OMBALTER command adds the specified columns to the table. This is because

at the point when you specify the position of the column C2 as 2, you are

adding the second column to the table.

OMBCREATE TABLE 'MY_TABLE' \

SET PROPERTIES (DESCRIPTION) VALUES ('TRIAL TABLE') \

ADD COLUMN 'C1' \

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

ADD COLUMN 'C2' AT POSITION 2 \

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

ADD COLUMN 'C3' AT POSITION 1 \

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

In the above example, the order in which the columns are added are as

follows:

C1, C2

C3, C1, C2

addViewConstraintClause

This clause adds the view's configuration clause.

addRelationalDependentClause

This clause adds referential dependencies to other relational objects.

propertyValue

This clause adds the 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.

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 10-3 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 10-4 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 10-5 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 10-6 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.

OMBCREATE VIEW

Purpose

Prerequisites

Syntax

Parameters

createViewCommand

QUOTED_STRING

setPropertiesClause

getPropertiesClause

addViewSCOandDependentClause

propertyNameList

propertyValueList

addColumnClause

addViewConstraintClause

addRelationalDependentClause

propertyValue

addUkPkClause

QUOTED_STRING

addFkClause

QUOTED_STRING

setUkPkPropertiesAndReferencesColumnsClauses

setFkSubClauses

constraintColumnReferencesClause

setFkReferencesClauses

quotedNameList

constraintUkReferencesClause

VIEW Object

COLUMN Object

UNIQUE_KEY Object

FOREIGN_KEY Object

See Also