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

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

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