Creating Models › Target Servers for Physical Models › Database Property Editors › Oracle Physical Property Editors › Oracle Table Column Editor › Define Oracle Table Column Properties

Define Oracle Table Column Properties

In a model based on the Oracle target server, you use the Oracle tab in the Table Column Editor to define Oracle properties such as the physical data type, null options, and constraint state for the selected column.

Note: The options described here are the basic options available for most physical data types. There can be more options available depending on the physical data type you assign to the table column. We recommend that you refer to your Oracle documentation for more information about using these properties.

To define Oracle table column properties

1. Right-click a column in the Model Explorer and click Properties.

   The Oracle Table Column Editor opens.

2. Select the table from the Table drop-down that contains the column that you want to define.
3. Select the column in the Navigation Grid that you want to define.

   Note: Click New on the toolbar to create a new column. Use the Enter filter text box to filter a very large list of columns to quickly locate the one that you want to define.

4. Click the Oracle tab and work with the following options:

   Physical Data type

   Lets you select a data type from the drop-down list to apply to the selected column. If the selected data type requires a value for precision, scale, or both, enter the value inside the data type parentheses. For example, you can select the DECIMAL() data type and specify a precision of 2 and a scale of 10 inside the parentheses to assign the data type DECIMAL(2,10) to the selected column.

   Average Width

   Lets you enter an estimated average width for the column if variable width is allowed based on the data type you assigned. If it is not allowed, an error is displayed in the Details section at the bottom of the editor. You can later use the Volumetrics dialog to calculate table and database size estimates based on these and other values.

   Null Option

   Lets you specify a null option for the selected column.

   Percent Null

   Lets you enter the estimated percentage of nulls used for that column if the option is available. For example, you can estimate a variable width ADDRESS 2 column assigned a width of 50 characters, to be NULL about 30 percent of the time. Use whole numbers. You can later use the Volumetrics dialog to calculate table and database size estimates based on these and other values.

   Null Option Constraint Name

   Lets you enter a constraint name for the Null Option.

   Null Option Constraint State

   Specifies the constraint state. This option is only available if you set your Null Option to Not Null. Select the check box to enable this option and specify a True or False value for the following properties as needed:

   Deferrable

   Specifies whether constraint checking in subsequent transactions can be deferred until the end of the transaction.

   Immediate

   Checks the constraint at the end of each subsequent SQL statement

   Validate

   Specifies whether all old and new data complies with the constraint to guarantee that all data is valid and continues to be valid, or that only new operations on the constrained data comply with the constraint.

   Enabled

   Specifies whether to apply the constraint to the table data or to disable the integrity constraint.

   Rely

   Specifies whether to activate an existing constraint with NOVALIDATE set for query rewrite.

   Exception Table

   Lets you specify a table into which Oracle places the rowids of all rows that violate the constraint. The table you specify must exist on your local database.

   Character Type

   Specifies the data subtype for a character column.

   Note: This option is only available if you select a character data type, such as CHAR, CHAR(), CHARACTER, and so on.

   Character Semantics

   Lets you specify the semantics used to calculate column length. Select one of the following values from the drop-down list:

   Byte

   Calculates column lengths in bytes (byte semantics).

   Character

   Measures column lengths in characters (character semantics).

   Note: This option is only available if you select a character data type, such as CHAR, CHAR(), CHARACTER, and so on.

   Sort Column

   Specifies that the column is a sort column for a hash-clustered table. Select the check box to specify this option.

5. Click Close.

   Oracle table column properties are defined and the Oracle Table Column Editor closes.