Oracle

Note: When working with Oracle v11gR2, use the Oracle v11g DBMS definition file. Support for Oracle v8-9 is deprecated.

When working with Oracle triggers, you can use the TRGBODY and TRGDESC variables. For information about working with variables, see Customizing and Extending PowerDesigner > DBMS Definition Files > PDM Variables and Macros.

The following table lists Oracle dimension objects and their equivalents in PowerDesigner:

Oracle object	PowerDesigner object
Dimension	Dimension (see Dimensions (PDM))
Hierarchy	Dimension hierarchy (see Hierarchies (PDM))
Level	Dimension attribute used in a hierarchy (see Fact and Dimension Attributes (PDM))
Attribute	Dimension attribute used as detail attribute (seeFact and Dimension Attributes (PDM))

The following sections list the extensions provided for Oracle.

Note: We do not provide documentation for the properties on the Physical Options and certain other tabs, though minimal information is available for them in the Resource Editor. For information about these properties, consult your DBMS reference documentation.

Note: In Oracle, the storage composite physical option is used as a template to define all the storage values in a storage entry to avoid having to set values independently each time you need to re-use them same values in a storage clause. For this reason, the Oracle physical option does not include the storage name (%s).

Abstract Data Types and Attributes

The following extensions are available on the General tab of abstract data types:

Name	Description
Editionable	[12c and higher] Specifies that the type is an editioned object (if editioning is enabled for the schema object type TYPE in schema). This applies to both type specification and body. Scripting name: Editionable

Name

Description

Editionable

[12c and higher] Specifies that the type is an editioned object (if editioning is enabled for the schema object type TYPE in schema). This applies to both type specification and body.

Scripting name: Editionable

The following extensions are available on the Oracle tab for attributes of abstract data types of type OBJECT or SQLJ_OBJECT:

Name	Description
Declare REF	Generates a REF modifier on attribute to declare references, which hold pointers to objects. Scripting name: RefAttribute

Name

Description

Declare REF

Generates a REF modifier on attribute to declare references, which hold pointers to objects.

Scripting name: RefAttribute

Columns

The following extensions are available on the Oracle tab:

Name	Description
Check constraint	[v11g and higher] You can specify the following options: Deferrable - Specifies that in subsequent transactions you can use the SET CONSTRAINT clause to defer checking of this constraint until after the transaction is committed. Initially deferred - Specifies that Oracle should check this constraint at the end of subsequent transactions. Scripting name: CheckDeferrable, NotNullDeferrable, CheckInitiallyDeferred, NotNullInitiallyDeferred
Deferred option of check constraint	[up to v10gR2] Defines the deferred option of a column constraint check. It is used in the definition or create and add items statements. Scripting name: ExtColumnDeferOption
Mandatory columns	You can specify the following options: Constraint name/Name of not null constraint - Defines the name of the not null constraint for a column. [v11g and higher] Deferrable - Specifies that in subsequent transactions you can use the SET CONSTRAINT clause to defer checking of this constraint until after the transaction is committed. [v11g and higher] Initially deferred - Specifies that Oracle should check this constraint at the end of subsequent transactions. [up to v10gR2] Deferred option of not null constraint - Defines the deferred option of a column not null constraint. An empty value means "Not deferrable". Scripting name: ExtNotNullConstraintName, ExtNotNullDeferOption
Encrypted	[v10gR2 and higher] Specifies that column is encrypted. You can specify the following options: Algorithm - Specifies the algorithm used for encryption. With salt - Specifies that the encryption adds salt to encoded data. Identified by Password - Provides the password for encrypting the column Scripting name: Encrypted, Algorithm, EncryptionWithSalt, IdentifiedByPassword
Identity	[v12c and higher] Specifies that the column stores a number incrementing with each insertion. You can specify the following options: Generated - Specifies when the identity clause applies to the column. Start -Specifies the value to begin the sequence with. Increment - Specifies the interval between the sequence numbers. Cycle - Specifies that the sequence continues to generate values after reaching either its maximum or minimum value. If this option is not selected the sequence cannot generate more values after reaching its maximum or minimum value. Order - Specifies that sequence numbers must be generated in order of request. Cache - Specifies that values of the sequence are preallocated. You can additionally specify how many values of the sequence the database preallocates and keeps in memory for faster access. Minvalue, Maxvalue - Specify that the sequence has a minimum and/or maximum value, which you specify in the fields to the right of the options. Scripting name: Identity, Generated, StartWith, IncrementBy, Cycle, Order, HasCache, CacheValue, HasMinvalue, Minvalue, HasMaxvalue, Maxvalue
Options	[v12c and higher] You can specify the following options: Invisible - Specifies that the column is a hidden column. To display or assign a value to an INVISIBLE column, you must specify its name explicitly. Default on null - Specifies that Oracle assigns the DEFAULT column value when a subsequent INSERT statement attempts to assign a value that evaluates to NULL. Scripting name: Invisible, DefaultOnNull

XML Virtual Columns

If the table type is set to XML, the Columns tab is replaced by the XML Virtual Columns tab.The following extensions are available on the General tab of XML virtual columns:

Name	Description
Expression	Specifies the SQL expression used to compute virtual column value. Scripting name: Expression

Name

Description

Expression

Specifies the SQL expression used to compute virtual column value.

Scripting name: Expression

Database Packages

The following extensions are available on the Oracle tab:

Name	Description
Add serially_reusable pragma on package specification	Specifies that the pragma serially_reusable clause must be applied on the database package specification. Scripting name: IsSpecPragma
Add serially_reusable pragma on package body	Specifies that the pragma serially_reusable clause must be applied on the database package body declaration. Scripting name: IsPragma
Editionable	[12c and higher] Specifies that the package is an editioned object (if editioning is enabled for the schema object type PACKAGE in schema). This applies to both package specification and body. Scripting name: Editionable

Name

Description

Add serially_reusable pragma on package specification

Specifies that the pragma serially_reusable clause must be applied on the database package specification.

Scripting name: IsSpecPragma

Add serially_reusable pragma on package body

Specifies that the pragma serially_reusable clause must be applied on the database package body declaration.

Scripting name: IsPragma

Editionable

[12c and higher] Specifies that the package is an editioned object (if editioning is enabled for the schema object type PACKAGE in schema). This applies to both package specification and body.

Scripting name: Editionable

Models

The following extensions are available on the Oracle tab:

Name	Description
Password Encryption	[v10gR2 and higher] Specifies the master key for encoding and decoding encrypted data. Scripting name: PasswordEncryption

Name

Description

Password Encryption

[v10gR2 and higher] Specifies the master key for encoding and decoding encrypted data.

Scripting name: PasswordEncryption

References

The following extensions are available on the Oracle tab:

Name	Description
Deferred option	Defines the deferred option of a reference. It is used in the definition of create and add items statements. Scripting name: ExtReferenceDeferOption
Exceptions into	Specifies a table into which Oracle places the ROWIDs of all rows violating the constraint. Scripting name: ExceptionsInto
Rely	Specifies whether an enabled constraint is to be enforced. Specify RELY to enable an existing constraint without enforcement. Specify NORELY to enable and enforce an existing constraint. Scripting name: Rely
Disable	Disables the integrity constraint. Scripting name: Disable
Validate	Checks that all old data also obeys the constraint. Scripting name: Validate

Tables

The following extensions are available on the Oracle tab:

Name	Description
Materialized view log	Specifies the materialized view log associated with the table. Scripting name: MaterializedViewLog

Name

Description

Materialized view log

Specifies the materialized view log associated with the table.

Scripting name: MaterializedViewLog

The following extensions are available on the XML properties tab (for v11g and higher) when the table type is set to XML:

Name	Description
Definition	Specifies that the properties of object tables are essentially the same as those of relational tables. However, instead of specifying columns, you specify attributes of the object. Scripting name: XmlTypeObjProperty
Storage type	Specifies that XMLType columns can be stored in LOB, object-relational, or binary XML columns. Scripting name: XMLTypeStorage
Basic file	Use this clause to specify the traditional LOB storage. Scripting name: BasicFile
Secure file	Use this clause to specify high-performance LOB. Scripting name: SecureFile
LOB segment name	Specify the name of the LOB data segment. You cannot use LOB_segname if you specify more than one LOB_item. Scripting name: LOBSegname
LOB parameters	Use this clause to specify various elements of LOB parameters. Scripting name: LOBParameters

Tablespaces

The following extensions are available on the Oracle tab:

Name	Description
Size specification	[v10g and higher] Specifies whether the tablespace is a bigfile or smallfile tablespace. This clause overrides any default tablespace type setting for the database. You can choose from the following settings: bigfile - contains only one datafile or tempfile. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks. smallfile - a traditional Oracle tablespace. Scripting name: SizeSpecification
Temporary tablespace	Use this option to create a locally managed temporary tablespace, which is an allocation of space in the database that can contain transient data that persists only for the duration of a session. This transient data cannot be recovered after process or instance failure. Scripting name: Temporary
Undo tablespace	Use this option to create an undo tablespace. When you run the database in automatic undo management mode, Oracle Database manages undo space using the undo tablespace instead of rollback segments. This clause is useful if you are now running in automatic undo management mode but your database was not created in automatic undo management mode. Scripting name: Undo

Name

Description

Size specification

[v10g and higher] Specifies whether the tablespace is a bigfile or smallfile tablespace. This clause overrides any default tablespace type setting for the database. You can choose from the following settings:

bigfile - contains only one datafile or tempfile. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32K blocks and 32TB for a tablespace with 8K blocks.
smallfile - a traditional Oracle tablespace.

Scripting name: SizeSpecification

Temporary tablespace

Use this option to create a locally managed temporary tablespace, which is an allocation of space in the database that can contain transient data that persists only for the duration of a session. This transient data cannot be recovered after process or instance failure.

Scripting name: Temporary

Undo tablespace

Use this option to create an undo tablespace. When you run the database in automatic undo management mode, Oracle Database manages undo space using the undo tablespace instead of rollback segments. This clause is useful if you are now running in automatic undo management mode but your database was not created in automatic undo management mode.

Scripting name: Undo

Note: If you do not have a login "System", when reversing tablespaces via a live database connection, physical options will not be reversed. If you want to cancel the reverse engineering of tablespace physical options, you should clear the SqlAttrQuery query in the Tablespace category in the Oracle DBMS.

Users

The following extensions are available on the General tab (for v9i and higher):

Name	Description
Identification type	Specifies how the user will be identified. You can choose between: by - requires a password externally - requires a distinguished name globally - requires a distinguished name Scripting name: Identification
Distinguished name	[external or global identification types] Specifies the user's distinguished name (DN) in the directory or certificate. Scripting name: DistinguishedName
Password	[by identification type] Specifies the user password. Scripting name: ClearPassword

Name

Description

Identification type

Specifies how the user will be identified. You can choose between:

by - requires a password
externally - requires a distinguished name
globally - requires a distinguished name

Scripting name: Identification

Distinguished name

[external or global identification types] Specifies the user's distinguished name (DN) in the directory or certificate.

Scripting name: DistinguishedName

Password

[by identification type] Specifies the user password.

Scripting name: ClearPassword

The following extensions are available on the Options tab:

Name	Description
Default tablespace	Specifies the default tablespace for objects that the user creates. Scripting name: DefaultTablespace
Temporary tablespace	Specifies the tablespace or tablespace group for the user's temporary segments. Scripting name: TemporaryTablespace
Quota definition	Specifies the maximum amount of space the user can allocate in the tablespace. Scripting name: QuotaDefinition
Profile	Specifies the profile to assign to the user. Scripting name: Profile
Password expire	Specifies that the user's password will expire. Scripting name: PasswordExpire
Account lock	Select lock to lock the user's account and disable access or unlock to enable access to the account. Scripting name: AccountLock

Views

The following extensions are available on the Oracle tab:

Name	Description
Super view object	Used in the UNDER clause to specify the superview the current object view is based on. Scripting name: ExtObjSuperView
Object view key	Specifies the attributes of the object type that will be used as a key to identify each row in the object view. Scripting name: ExtObjOIDList
Object view type	Defines the type of the object view. Scripting name: ExtObjViewType
Force	When set to TRUE, allows you to create the view regardless of the existence of the base tables or the owner privileges on these tables. Scripting name: ExtViewForce
Editioning	[v12c and higher] Specifies that the view is an editioning view, a single-table view that selects all rows from the base table and displays a subset of the base table columns. You can specify that the view is: `editioning` `editionable` `editionable editioning` `noneditionable` Scripting name: Editioning
Bequeath	[v12c and higher] Specifies whether functions referenced in the view are executed using the view invoker's rights or the view definer's rights. Scripting name: Bequeath

Synonyms

The following extensions are available on the General tab:

Name	Description
Editionable	[v12c and higher] Specify whether the synonym is an editioned or noneditioned object if editioning is enabled for the schema object type SYNONYM in schema. Scripting name: Editionable

Name

Description

Editionable

[v12c and higher] Specify whether the synonym is an editioned or noneditioned object if editioning is enabled for the schema object type SYNONYM in schema.

Scripting name: Editionable