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

Use the Oracle tab in the Table Column Editor to define properties such as the physical data type, null options, and constraint state for a 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: To create a column, click New on the toolbar. Use the Enter filter text box to filter a large list of columns to 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, specify a precision of 2 and a scale of 10 to assign the data type DECIMAL(2,10) to the selected column.

   Average Width

   Lets you enter an estimated average width for the column, only if variable width is allowed for the selected data type. 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 the 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 that are used for a column, only 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 the 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. If you select the check box, specify values for the following properties:

   Deferrable

   Specifies whether checking for constraints 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 if all old and new data is validated continuously. If you select False, only the new operations on the constrained data are validated 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 rewriting a query.

   Exception Table

   Lets you specify a table into which Oracle places the rowids of all rows that violate the constraint. The table that 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 that is used to calculate the 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.

   Virtual Column Option

   Note: This option is available only when the target database is Oracle 11.x.

   Is Virtual Column

   Specifies whether the selected column is a virtual column. You cannot create a virtual column for a table that is included in a cluster. A virtual column cannot be of the LONG, BLOB, REF, and BFILE data type.

   Expression

   Specifies the SQL query or the expression that is stored as the virtual column. You can use the toolbar options to cut, copy, or paste text, open the Macro Toolbox, or work with the Text Editor.

   Is Generated Always

   Specifies whether the keyword Generated Always is included in the Forward Engineering script.

   Is Datatype Allowed

   Specifies whether the data type that is selected for the virtual column is included in the Forward Engineering script.

5. Click Close.

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