Listing of all
Standards and Guidelines
- by Type


OMG = Oracle Method Guideline
OMS = Oracle Method Standard

General Standards - General
OMG-10008: Only information that cannot be stored in the repository in any way or that is not important for maintaining the system, may be published as (electronic) documents.
Rationale Analysts as well as developers tend to rely upon the repository as their single source of information. Information published in any other format, might be overlooked during maintenance. It's also harder to enforce standards upon such information.

OMG-10010: Use the correct spelling for descriptive text as well as for entity names, attribute names, etc.
Rationale Using the correct spelling makes text easier and less irritating to read.

OMG-10042: Document any unusual design decisions including the reasons in the Notes property of the respective object.
Rationale This helps your team members understand the decisions that have been made. It is also valuable information for anyone performing QA on the project.

OMS-30507: All naming and documentation should be done in one agreed language, including applications designed to be multi-lingual. This is true for all deliverables.
Rationale When there are more teams working on different sites in the world, with different native languages, then it is important to agree on a common language so that all documentation is understandable for all parties.

OMS-61518: When a secondary access element or secondary access controlled element (SAC) is deleted, this should be recorded in the notes of the primary access element or Primary access controlled (PAC) element.
Rationale Analysts or developers might want to know what happened to the SAC. If this kind of information is not recorded, the person that can explain, might not be available on the project, if at all.
Example The deletion of an attribute is recorded in the notes of the entity.

OMS-61537: Document any deviations of the CDM Standards in the respective Notes, including the OMS Standard code and the reason.
Rationale Documenting these deviations helps your team members understand the decisions that have been made. This is also valuable information for anyone performing QA on the project.

Note: If you deviate from any OMS standards on a project level, do not include the deviation for every object. If there is only one Application System involved, then document these deviations in the Description property of the Application System. When there are more Application Systems involved, then it is recommended to document the deviations in a separate document.
Entity Relationship Modeling
OMS-30504: Data that does not take part in the universe of discourse for the business that is the object of analysis should not be modeled in the business data model (for example, system messages or system configuration parameters). Audit requirements, such as journalling, are part of the business and should therefore be modeled in the Business Data Model.
Rationale By keeping the models as close to the business as possible, it will be more understandable for the users to read the models. Please be sure you add system entities to the system data model. If you do not, it will be harder to estimate your Design phase.

OMS-61510: Give an entity relationship diagram a name using the following naming convention:
[application code]_ERD_[logical name](#)
In the case of an entity relationship diagram for a subsystem, the logical name will be the name of the subsystem. The # stands for an optional digit in case there are different versions.
Rationale Naming entity relationship diagrams this way makes it easier to recognize the theme of the diagram. The indicator ERD is added to make the names distinct, for example when showed in the diagrams node of the Repository Object Navigator. For example, Designer does not prevent that an entity and a data diagram having the same name.

Drawing conventions
OMG-10000: List only those attributes that are part of the first unique identifier inside the soft box representing the entity to which they belong.
Rationale Listing all the attributes, in most situations, makes the diagrams too big and thereby less readable as the size of the entity soft boxes needs to be increased substantially.

OMG-10004: Create an Entity Relationship Diagram for important aspects of the data model. Create one large diagram with all the entities, if possible.
Rationale Especially for large application systems, it is easier to understand and discuss smaller parts of the total model at one time. One large model provides a complete picture where it is possible to detect incompleteness and inconsistencies.
Example Suppose that for a big application system different subsystems have entities that model information to be used for invoicing, and suppose these entities do not directly belong to the Financial subsystem. Creating a diagram that contains all these entities might clarify the way the data model covers the information needed to generate invoices.

OMS-30506: Make separate diagrams for every subsystem. Start from a copy of the diagram of the entire system (if you have made one), and remove the obsolete entities, thus keeping the basic layout intact.
Rationale Since users and developers are mostly focused on one sub system, it makes sense to make a diagram per sub system.

OMS-61511: For entity relationship diagrams, put one name of a relationship at one side of the line and the other name at the opposite side of the line, that is left versus right or up versus down.
Rationale This makes the diagram more readable.

OMS-61512: At the beginning of the project, set up generic drawing conventions for aspects as colors, font types, font sizes, line widths, etc.
Rationale This makes the diagram more readable. It is also easier to compare diagrams.

Relationship Lines
OMS-33015: Relationship lines should not cross entity soft boxes.
Rationale The diagrams are more readable when relationships do not cross entities.

OMS-61505: When drawing relationships, avoid crossing other relationship lines, to the extent that this is possible
Rationale The diagrams are more readable when relationships cross each other as little as possible.

OMS-61506: Relationship lines should be drawn using short, straight lines and right angles, to the extent that this is possible.
Rationale The diagrams are more readable when keeping relationships short, straight and with right angles.

OMS-61507: Relationship lines should be drawn one to many, from top to bottom and left to right, to the extent that this is possible.
Rationale By keeping the directions of the relationships the same way, it is easier to read the diagram as you would only need to read in the same direction. Also similar constructions are easier to recognize.

OMS-61508: Relationship lines should possess relationship names at both ends.
Rationale This allows reading in either direction, and increases the understanding of the relationship as the relationship can be read from two different views.

Diagram Layout
OMS-33019: Avoid making drastic changes to the basic layout of an entity relationship (ER) diagram that has been around for some time.
The first blow is half the battle. Make sure the floor plan of the first layout of the overall ER diagram is very similar to the complete one.
Rationale If someone drastically changes the floor plan of the ER diagram you used to understand, it may take you quite some time to reach the same level of understanding of the new layout. Often, the first layout functions as a "mind map" of the business. If that is changed, it is as disorienting as if they changed the infrastructure of your home town: everything looks vaguely familiar, but you get lost for sure.

OMS-33020: Give the core entities of your system a central place in your diagram. For example, take the entity ORDER LINE or PRODUCED LOT. These are usually low-level entities that are not likely to be referred to. To show their importance, they can be drawn slightly larger than other entity boxes. Sometimes it is not necessary to draw them as larger soft boxes, because their importance is immediately visible; they look like a spider in a web of relationships.
Rationale By providing the core entities a central place, you increase the ease of understanding the model.

OMS-33021: Place entities that are often referred to, but that are not specific to the business, somewhere at the border of your diagram as long as it can be arranged neatly.
Rationale Usually this type of entity is only referred to. The referring relationships are often of exactly the same nature. These may occur many times in one model.
By placing these entities on the edge of the diagram there is less focus on these entities, and thereby the readability of the diagram increases.
Example Examples are CURRENCY or LANGUAGE for multi-currency or multi-lingual environments.
Some example relationship ends are: expressed in CURRENCY or responsibility of DEPARTMENT.

OMS-33022: Place entities that are often referred to and specific to the business (such as PERSON, ARTICLE) in a central zone between the core entities that actually refer to them, or draw them in a long and narrow soft box placed along the border of the paper.
Rationale This makes it easier to draw short, straight and non-crossing relationships to these entities, and thereby the readability of the diagram increases.

OMS-33023: Place entities that are not often referred to and are not specific to the business somewhere near the referring entity, or leave them out of your diagram completely.
Rationale This makes it easier to draw short, straight and non crossing relationships to the entity, and thereby the readability of the diagram increases.

OMS-33024: Do not draw all relationship lines that represent the same kind of relationship to one particular entity from many (five or more) others. Invent a special symbol as a substitute.
Rationale Replacing the actual relationships with a relationship end with a special symbol increases readability as a web of relationship lines are avoided.
Example All 24 relationships to the entity CURRENCY have been replaced by a relationship end and a $-sign for all the entities related to the currency entity for the sake of readability.

Attention: Do not remove the relationships from the repository, only from the diagram. You can include a ?one-end? dummy relationship described as follows:

1. Choose a real relationship within the diagram that has the same kind of end (for example, a many end) as you require.
2. Minimize the relationship names, so that they are not visible in the diagram.
3. Select the relationship and make a copy (Edit, Copy).
4. Include a text object. Choose from the menu Edit -> Insert New Object. Then select Create New and (for example) the Object Type WordPad.
5. Enter the special symbol that represents the relationship, and then paste the copied relationship. Format so that the symbol is located correctly relative to the relationship.
6. Close the text object and move it to the correct place on the diagram.

Note: Be careful in resizing the text as this might lead to changes in font size and the text becoming partially visible.
OMS-33025: Draw entity relationship structures that are congruent by nature as congruent structures in your model.
Rationale The more similar structures are used, the easier it is to read and understand the model quickly.
Example For example, the structure of an ORDER, ORDER LINE is similar to QUOTATION, QUOTATION LINE. This similarity should be visible immediately.

OMS-33027: Make one ER diagram that represents the final Oracle Designer content exactly.
Rationale Drawing one complete model often makes it easier to discover incompleteness and inconsistencies.

OMS-33029: Do not draw implied subtypes as subtypes, unless they have a right of their own to exist.
Rationale An implied subtype of an entity is a subtype that exists as an implication of the entities to which it refers.

As there are SERVICEs and PRODUCTs to which the ORDER LINES can refer, there are - by implication - two kinds of ORDER LINES. This kind of distinction cannot be denied, but it often does not make sense to model it.
Always bear in mind that the goal of representing entity or functional information in some kind of diagram is to add clarity to the model.

OMS-33030: ER Diagrams should always show a legend stating:
  • diagram name
  • application (sub)system
  • author
  • date
Use the description for each diagram for an explanation of symbols used.
Rationale This information makes it obvious to the reader what the diagram is modeling, who is the owner of the diagram and what date the diagram was last updated.

Attention: This information can be provided under File -> Summary Information in the Entity Relationship Modeler, and can be made visible on the diagram by checking the appropriate Show check boxes.
OMS-33031: Any inconsistencies between the ER diagram and the repository must always be explicitly stated in the diagram legend.
Rationale By including this information it is obvious to the reader what objects are intentionally left out and why.
Example Subtypes of entity ORGANIZATION and their relationships have been left out of this diagram for clarity.

Function Modeling
OMG-10016: Consider recording the general functional requirements on how batches should be dealt with in a business function.
Rationale There are various ways to implement this, from using a third-party tool to building it. The functionality provided by these tools can differ significantly. Specifying the functional requirements facilitates the decision making during design in this respect. Record it in the repository for future reference.

OMS-61513: When data needs to be journalled, record a 'journal business function' that has entity usages for all entities that need to be journalled. Also record attribute usages for those entities for which not all attributes need to be journalled. Record the requirements for journalling in the description of the business function.
Rationale The need for journalled data is functional and therefore should be modeled at the analysis level.
Modeling it in a function instead of in separate 'Journal Entities' avoids having to duplicate entities and attributes (which would have to be maintained when the entities change). During the Design phase the journal tables can be generated from the original tables, so that is another reason not to use 'Journal Entities'.
Modeling it in a central function instead of in each function where the relevant entities are maintained, avoids duplication when there is more than one function for one entity, and gives an easy overview of all data that have to be journalled.

Attention: Be aware that there is a difference between journalling and recording history.
OMS-61525: Use the following naming convention for function hierarchy diagrams of business function structures (that is, not the business rules):
[application code]_FHD_[subsystem name](#)
where # stands for an optional one-digit sequence number.
Name the function hierarchy diagram with the business rules as follows:
[application code]_FHD_BR
Rationale In this way, it is easier to recognize the purpose of a function hierarchy diagram from the name, for example, when opening an existing diagram. The abbreviation BR for Business Rules is also used for the function labels.
Example OMS_FHD_INTERFACES
OMS_FHD_BR

Dataflow Analysis
OMS-31500: Come to an agreement with the customer beforehand concerning whether or not you will use the dataflow analysis technique. Also, decide whether or not you will use dataflow diagrams as a means of communication. If you do, agree on what level of functions you will cover in your dataflows, and whether you will strive to be complete on that level.
Rationale Data Flow Analysis is useful to understand the dataflow in complex applications. However, in most project situations, it provides only limited additional value to development. It is a time-consuming task to perform this kind of analysis, and therefore it is important that you determine the true additional value of the diagrams.

OMS-31501: For simple dataflow diagramming purposes, one single data store, containing all data, will do.
Rationale This saves effort in creating the dataflows.

OMS-31520: Only create dataflow diagrams it they substantially contribute to the understanding of the system. This may be the case if, for example, the business is strongly process-oriented, or if the actual processes take a long time to finish.
Rationale If they do not contribute in the understanding of the system, then the effort spent on creating dataflow diagrams is wasted.

OMS-31521: Do not create detailed dataflow diagrams down to the level of elementary functions.
Rationale Dataflows on this level often mystify more than they clarify.

Logical Database Design
OMG-10059: If practical, make one diagram showing all tables and foreign keys between them.
Rationale To understand the logical data model it is convenient to have one diagram that shows all tables with their foreign key relationships.

Suggestion: If the diagram becomes too large, then you may change the preferences of the diagram so that only the primary key columns or no columns at all are displayed.
OMG-10060: Make separate Server Model diagrams for every subsystem
Rationale To understand what data is used by what subsystem, it is convenient to have a diagram that shows all tables that are used by that subsystem with their foreign key relationships. This is especially true for large or multi-application systems.

Note: If a diagram with all tables and foreign key relations is already available, it is recommended to start from a copy of that diagram. Remove the superfluous tables and foreign key relationships, so that the basic layout is kept intact.
OMS-61559: Use the following naming convention for Server Model Diagrams:
[application code]_DSD_[logical name](#)
In case of a data structure diagram for a subsystem, the logical name is the name of the subsystem. The # is an optional digit in case there are different versions.
Rationale Naming Server Model Diagrams this way makes it easier to recognize the theme of the diagram.

Server Model Generation
OMG-10076: If you have dynamic domains, use a central reference code table (CG_REF_CODES) for all your applications.
Rationale The alternative is a different table for each application system where the table name is [application system name]_REF_CODES . Sometimes the application system name is different from the application code (see also OMS-61605), but you would want the table name to start with the application code instead of the whole application system name.
When you do not make the table name application specific, you do not get any problems if you have multiple versions of the same logical application stored in differently named physical application systems in Designer (in that case, you would have to use a work around such as temporarily renaming the application system while generating modules and reference code table).

This also applies when moving to Oracle Repository (Designer 6i): when you structure your project/system into multiple containers, the question that may arise is: how would you know which container to use to give the table its name.
Example Suppose you have the following application systems, which all represent a version of the same logical application system:
AID_DEV
AID_PROD
If you would use application-specific names for the reference codes table, you would get the following table names
AID_DEV_REF_CODES
AID_PROD_REF_CODES
depending on which application system you generate from, while the result should have been the same for all these application systems. If you choose a central reference codes table, for each application system you would get the same:
CG_REF_CODES

Suggestion: If you decide to always use CG_REF_CODES, there are two arguments that plead for using the same table instance for all your applications:

* If you create a separate CG_REF_CODES table for each application, you might get conflicting synonym names if a user has access to multiple applications. (This can be solved through alter session set current_schema, but that makes things more complex.)

* Suppose you have a database setup with a separate owner for each application and one 'super' user who has access to all applications (each application owner grants access to the 'super' user and the 'super' user has synonyms to the application owner's objects), where users log in with either a user name that is not a database user or a user name which has no privileges. Once successful login is confirmed, the security system logs in with the 'super' user. In that case, on the database you must have either different names, or a single CG_REF_CODES table. You cannot have one CG_REF_CODES per application.

So if the end users only have access to one application system, create CG_REF_CODES in the application owner schema and reverse engineer the table into the Designer application system.
And if the end users might need access to more than one application system, create CG_REF_CODES in a central schema and let each application owner have a grant/synonym for it; reverse engineer the table to an application system for common objects and share it to the other application systems.
Generation: By default, a single table called CG_REF_CODES is created to store the allowable values held against the columns and domains in all application systems. Alternatively, within the General Generator Options dialog box you can set the Scope Of Reference Code Table Option to generate a reference code table for a specific application system.
OMG-10077: If you need code control sequences, use either a central code control table (CG_CODE_CONTROLS) for all your applications or a separate code control table for each relevant table ( [table name]_CC ).
Rationale The alternative is a different table for each application system where the table name is [application system name]_CODE_CONTROLS ). Sometimes the application system name is different from the application code (see also OMS-61605), but you would want the table name to start with the application code instead of the whole application system name.
When you do not make the table name application specific, you do not get any problems if you have multiple versions of the same logical application stored in differently named physical application systems in Designer. (In that case you would have to use a work around such as temporarily renaming the application system while generating modules and code control table.)

This also applies when moving to Oracle Repository (Designer 6i): When you structure your project/system into multiple containers, the question that may arise is: how would you know which container to use to give the table its name.
Example Suppose you have the following application systems, which all represent a version of the same logical application system:
AID_DEV
AID_PROD
If you would use application specific names for the code control table, you would get the table names
AID_DEV_CODE_CONTROLS
AID_PROD_CODE_CONTROLS
depending on which application system you generate from, while the result should have been the same for all these application systems. If you choose a central code control table, for each application system you would get the same:
CG_CODE_CONTROLS
Also if you choose a separate code control table per relevant table, for each application you would get the same, because each application system would contain the same table name AID_INVOICES:
AID_INVOICES_CC

Suggestion: If you decide to always use CG_CODE_CONTROLS, there are two arguments that plead for using the same table instance for all your applications:

* If you create a separate CG_CODE_CONTROLS table for each application, you might get conflicting synonym names if a user has access to multiple applications. (This can be solved through alter session set current_schema, but that makes things more complex.)

* Suppose you have a database setup with a separate owner for each application and one 'super' user who has access to all applications (each application owner grants access to the 'super' user and the 'super' user has synonyms t o the application owner's objects), where users log in with either a username that is not a database user or a username which has no privileges. Once successful login is confirmed, the security system logs in with the 'super' user. In that case, on the database you must have either different names, or a single CG_CODE_CONTROLS table. You cannot have one CG_REF_CODES per application.

So if the end users only have access to one application system, create CG_CODE_CONTROLS in the application owner schema and reverse engineer the table into the Designer application system. And if the end users might need access to more than one application system, create CG_CODE_CONTROLS in a central schema and let each application owner have a grant/synonym for it; reverse engineer the table to an application system for common objects and share it to the other application systems.
Generation: By default, a single table CG_CODE_CONTROLS is generated to provide unique identifiers for all modules in the application system. Alternatively, within the General Generator Options dialog box you can set the Scope Of Code Control Table Option to generate a table for a specific application system, or for a specific table.
OMG-10078: If in your forms you want to display online help information in a special help form, use a central online help table (CG_FORM_HELP) for all your applications.
Rationale The alternative is a different table for each application system where the table name is [application system name]_FORM_HELP . Sometimes the application system name is different from the application code (see also OMS-61605), but you would want the table name to start with the application code instead of the whole application system name.
When you do not make the table name application specific, you do not get any problems if you have multiple versions of the same logical application stored in differently named physical application systems in Designer (in that case you would have to use a work around like temporarily renaming the application system while generating modules and online help table).

This also applies when moving to Oracle Repository (Designer 6i): when you structure your project/system into multiple containers, the question that may arise is: how would you know which container to use to give the table its name.

Also, the standard help form (ofghlpf) attempts to locate help text in CG_FORM_HELP. If you choose the application specific table name and you intend to use ofghlpf to display help text, you must change any references to CG_FORM_HELP in ofghlpf to refer instead to the name of the table created for the application system.
Example Suppose you have the following application systems, which all represent a version of the same logical application system:
AID_DEV
AID_PROD
If you would use application specific names for the online help table, you would get the table names
AID_DEV_FORM_HELP
AID_PROD_FORM_HELP
depending on which application system you generate from, while the result should have been the same for all these application systems. If you choose a central online help table, for each application system you would get the same:
CG_FORM_HELP

Generation: In the DBA section of the Form Generator Preferences, the preference HPTABL determines the Scope of the online help table.
The options are:
GENERIC (default)
System wide. A single table called CG_FORM_HELP is created to hold online help text for forms generated from all the Repository application systems.
APPSYS Application system wide. A table is created for each Repository application system, named [application system name]_FORM_HELP.
Suggestion: If you decide to always use CG_FORM_HELP, there are two arguments that plead for using the same table instance for all your applications:

* If you create a separate CG_FORM_HELP table for each application, you might get conflicting synonym names if a user has access to multiple applications. (This can be solved through alter session set current_schema, but that makes things more complex.)

* Suppose you have a database setup with a separate owner for each application and one 'super' user who has access to all applications (each application owner grants access to the 'super' user and the 'super' user has synonyms to the application owner's objects), where users log in with either a username that is not a database user or a username which has no privileges. Once successful login is confirmed, the security system logs in with the 'super' user. In that case, on the database you must have either different names, or a single CG_FORM_HELP table. You cannot have one CG_FORM_HELP per application.

So if the end users only have access to one application system, create CG_FORM_HELP in the application owner schema and reverse engineer the table into the Designer application system. And if the end users might need access to more than one application system, create CG_FORM_HELP in a central schema and let each application owner have a grant/synonym for it; reverse engineer the table to an application system for common objects and share it to the other application systems.
OMS-61572: Do not check the Generate Valid Value Constraints option when running the Generate Database from Server Model utility.
Rationale Setting this option leads to the generation of inline check constraints for allowable values. In case of inline check constraint, the set of allowable values can only be altered by dropping and recreating the table. The same functionality can be achieved by using outline check constraints.

Button Toolbar
OMS-58168: Dynamically enable and disable the toolbar buttons context sensitively.
Rationale In this way it is obvious which functions are available.

OMS-60050: Do not change the icon of a button, nor the hint text, dynamically.
Rationale This is only confusing to the end user.

Closing Non-modal Windows
OMS-58147: A user closes a non-modal window, with the native GUI close mechanism. Do not provide 'Close' or 'Dismiss' buttons in any non-modal windows.
Rationale This is in accordance with standard UI conventions.

OMS-58148: Only dialog (modal) windows are closed automatically within the form. The user invokes this action by pressing a button, such as 'OK', 'Cancel' or 'Done'.
Rationale This is in accordance with standard UI convention.

OMS-58149: Closing a window does not force a commit of the data contained in it. It merely means do not show this information right now.
Rationale User should remain in control of what is happening, and therefore explicitly commit the data.

OMS-58151: When a user closes a master window, all of its details must be closed as well.
Rationale The details window only has a meaning in the context of a master.

Form Generator: The generator preference NAVWND = 'Y' (Navigation Window Management) ensures this behavior within a single form. The generator preference NAVCCF = 'Y' (Close child forms) ensures this behavior across forms. This means that when a child form is called from a window in a master form, the child form is also closed when the window that called the child form is closed.
OMS-58152: If the cursor is in the window that the user requests to be closed, then it should be moved to a previous block that is not in that window.
Rationale The cursor must remain within the user's context.

Form Generator: This behavior is controlled by the generator preference NAVCUR (Navigation Cursor Management). To achieve the desired functionality, this preference must be set to Y(es).
Modal Window
OMS-58031: A source in the object library that is used to generate modal windows, should have the following properties:
Style: Dialog
Icon Name: (*)
Modal: True
Bordered: True
Closable: False
Fixed size: True
Iconifiable: False
Moveable: True
Zoomable: False
(*) Although the window is not iconifiable, the Icon Name must still be specified, as it appears in the window manager window.
Rationale This is according to standard UI conventions.

OMS-58034: A modal window must at least contain an OK-button (or language equivalent). Choose one of the following button combinations:
Buttons Usage
OK & Cancel OK closes the window. In some cases, it may perform a commit as well (see 'Implicit Commits'). Cancel clears the data without asking for confirmation,, and closes the window. Note that usually only very simple modal windows can be modeled this way, and Save, Clear, etc. must be disabled.
OK Closes a window. Typically used for a window that is display only.
OK, Apply & Cancel OK closes the window and applies the changes. Apply processes the data, but does not close the window. Cancel closes the window, and clears any pending changes since the last Apply.
Rationale This is according to standard UI conventions.

About Window
OMS-60051: Create an About window with at least the following information:
  • application name and version
  • copyright message
  • module or file name
  • module version
  • user name
  • database connect information
Rationale This information is useful during testing and support. This information should not be put into the header lines of the form. This consumes too much space, and is not useful end user information.

OMS-60052: The About window must be a modal dialog window.
Rationale This is according to standard UI conventions.

Block Layout
OMG-10056: Avoid the use of Radio Buttons in multi-record blocks.
Rationale Radio Buttons in multi-record blocks consume too much space unnecessarily .

OMS-58004: When showing only one block in a window, do not provide a block border.
Rationale A block border consumes too much space unnecessarily

Form Generator: This is governed by the BLKDEC preference.
OMS-58049: Always use a vertical scrollbar in multi-row blocks (preference BLKVSB), and never in a single-row block.
Rationale A scrollbar clearly indicates that one is dealing with a multi-row block, and indicates when there are more rows than are visible.

Exception: Only in multi-row blocks where the number of records never exceeds the number of displayed records, you may choose not to include a vertical scroll bar.
OMS-60042: Position the scrollbar to the right of the block data (preference BLKSBP).
Rationale This is according to standard UI conventions.

Block Specific Buttons
OMS-58112: Buttons within a window should be sized similarly and spaced consistently.
Rationale This provides a consistent look and feel throughout the application.

Form Generator: The sizing of buttons is governed by the preference AIBBWD (Action Item Button Width).
Navigation within a Form
OMS-60055: Validation of required items is performed upon leaving the record.
Rationale This ensures that the end user can remain in control of their activities.

Block Navigation
OMS-60054: Always provide a button to navigate to a detail block if the detail block is rendered in another window.
Rationale This is the most intuitive way to point out to the user that it is possible to navigate to another window.

Forward Navigation from Last Item
OMS-58140: When the user performs forward navigation from the last item of a block, then in a single-row block, the behavior is dependent upon where detail blocks are rendered:
  • if any detail block is rendered in the same window, then the cursor moves into the first detail block (change_block)
  • if no detail block is rendered in the same window, the cursor moves to the first field of the current record (same_record)
Rationale A window is a logical unit, so therefore the cursor should always remain within the window unless the user performs a specific navigation action to move out of the window.

OMS-58527: When the user performs forward navigation from the last item of a block, then in a multi-row block, the behavior is always change_record. That is, the cursor moves to the first item of the next record.
Rationale This makes it more intuitive for the user. It also makes it easier for the user to navigate through the records of the form.

Previous Navigation from First Item
OMS-58141: When the user performs previous navigation from the first item of a block, then the behavior is dictated by the Forward navigation requirement. No concession is made to optimize the behavior of "previous" navigation.
Rationale This is the reverse of the behavior described in standards OMS-58527 and OMS-58140. This is what a user would predict to happen.

Fonts
OMS-58012: Use a font the user is accustomed to from other applications.
Rationale This provides consistency throughout the applications.

OMS-60045: Items that currently are display-only should be shown with a canvas-gray background. For items with insert allowed and update not allowed, the background color should dynamically change to canvas-gray as soon as the row is committed.
Rationale This provides a visual indication on what kind of actions are not allowed for the individual items. This increases the usability of the application.

Generation: If you use Headstart Oracle Designer you get this functionality by default.
Menu Design
OMS-60059: Use the DO_KEY built-in for menu entries replicating standard Forms function.
Rationale This ensures that the functionality of the menu entry and corresponding function key is the same.

Message Handling
OMG-10058: Messages that are not self-explanatory are shown in a pop-up window with an additional Help button. When pressed, the Help button shows a window with additional information on the cause of the message and the action to take.
Rationale This makes the application more user friendly.

OMS-58176: Error messages are shown in a modal dialog window (for example, native Forms alert box) with a stop sign, the message text, and the OK button.
Rationale It makes it clear to the user an error has occurred, and forces the user to perform an action before continuing.

OMS-58177: If warning messages are shown in a pop-up window, the window must include at least two buttons:
- a Cancel button that causes processing to be halted,
- and a Continue button that does not stop processing.
Rationale In this way the decision is with the user whether to continue or halt the operation.

Code Layout
OMS-51001: Write SQL statements in lowercase characters. Use uppercase characters when attention should be drawn to a specific part of the SQL statement. Note that comparisons to literal constants might require uppercase characters.
Rationale Writing all SQL statements in lowercase makes the code more 'quiet ' and easier to read.

OMS-51002: Start every clause that contains a column name, a table name, or a SQL reserved word on a new line.
Rationale Experience has shown that SQL statements written using this method are less prone to editing errors. Therefore, this notation decreases development and maintenance time.

OMS-51003: Place commas that separate column or table names on the first position of a new line. This rule also applies to data definition statements.
Rationale Experience has shown that SQL statements written using this method are less prone to editing errors. Adding or removing lines does not result in missing commas. Therefore, this notation decreases development and maintenance time.

OMS-52850: Use the following layout for your SELECT statements:

select..col1...alias1
,.......col2...alias2
,.......(...)
from....table1...alias1
,.......table2...alias2
where...condition1
and.....(condition2a
........or
.........condition2b
........)
(...)
group by col1
,........col2
order by col1
,........col2
;

Note: The dots indicate the required number of spaces to properly layout the statement.
Rationale This makes the code easier to read.

OMS-52851: Use the following layout for a subquery:

...(select..col1
....from....table1
....where...condition1
...)

Note: The dots indicate the required number of spaces to properly layout the statement.
Rationale This makes the code easier to read.

Inline Documentation
OMS-51050: Use inline documentation only to document or clarify individual parts of a SQL statement.
Rationale Too much inline documentation might make the SQL statement harder to read. The purpose of commenting is the opposite; to improve the understanding of the statement.

OMS-51051: Place comments within SQL statements on the right-hand side of the SQL text, at the end of the line. Right-align the end of comment markers. Use complete lines within the SQL statement if the comment lines become too large.
Rationale This makes it easier to read the statement

OMS-51052: Document the lines of a statement that you (re)write to enable index use, specifying the original condition. This rule also applies to the hints you provide the cost-based optimizer.
Rationale This makes it easier to understand why the statement has been written the way it is.

OMS-51053: Document the lines of a statement that you (re)write to disable index use. This rule also applies to the hints you provide the cost-based optimizer.
Rationale This makes it easier to understand why the statement has been written the way it is.
Example select..emp.ename
,.......emp.sal
from....oms_employees emp
where...emp.job||''= 'CLERK' -- disabled index-use on JOB

OMS-51054: Document the lines of a statement where you have to use a conversion function that disables index use, specifying the original condition.
Rationale This makes it obvious that the conversion function is used intentionally to disable index use.

Datatype Conversion
OMS-51150: Specify an explicit datatype conversion function where Oracle would otherwise perform implicit datatype conversion
Rationale When datatype conversion is explicitly coded it is clear that it is intended. Implicit datatype conversion can unintentionally disable the use of indexes.
In addition , the algorithms for implicit datatype conversion can be subject to change across releases of the Oracle Server.

OMS-51151: When you compare an indexed column to a constant, apply any necessary datatype conversion to the constant.
Rationale This ensures that the Cost-Based Optimizer will continue to consider the use of the index in the execution plan.

OMS-51152: Do not store graphic images in a LONG column.
Rationale The resulting implicit RAW_TO_HEX conversion takes up a lot of resources (memory and disk space). Use LOB column instead.

SQL Functions
OMS-51200: Avoid the DECODE expressions that contain return arguments of more than one datatype.
Rationale This may lead to confusing results as the datatype returned by the DECODE function is always the type of the first output variable in the decode list.
If you cannot avoid this, explicitly convert all return arguments to a single datatype.
Example select ename
from...oms_employees emp
where (deptno is null)
or....(empno >= 1000
.......and
.......deptno <= 30
.......);

OMS-51201: Do not use the MAX function to retrieve a new highest value for a column; (for example, for key generation).
Rationale With multiple users, and/or sessions this may lead to duplicate values. Instead, use a sequence generator.

Operators
OMS-51270: Always use parentheses when an expression involves two or more operators.
Rationale This makes it easier to read and maintain the code.

OMS-51271: Always specify parentheses around the arguments of an OR operator.
Rationale This makes it easier to read and maintain the code.

OMS-51273: Avoid the use of the LIKE operator with columns of DATE and NUMBER datatype.
Rationale This prevents implicit datatype conversion and thereby ensure that the Cost-Based Optimizer will continue to consider the use of the index in the execution plan.

OMS-51274: Only use the NOT EXISTS operator when the subquery can only return the value TRUE or FALSE.
Rationale The NOT EXISTS operator does not differentiate between UNK(nown) or FALSE. Therefore, under certain circumstances NOT EXISTS may yield an incorrect result.
Example In a given company the bonus for a department depends on the fact that there are no salesmen who have not met their target. Assume that the comm column of the OMS_EMPLOYEES table signifies whether a target has or has not been met. A SQL statement to find all departments that do not contain Salesmen without Commission could read:

select..'department #'||dpt.deptno
from....dept dpt
where...not exists (select..'x'
....................from....emp
....................where...emp.deptno..= dpt.deptno
....................and.....emp.job.....= 'SALESMAN'
....................and.....emp.comm....<= 0
....................)

The subquery will evaluate to TRUE for the departments where all salesmen have a commission entered larger than zero. If this is not the case, then either FALSE or UNKNOWN is returned. Assume the following salesmen exist in department #30:

ENAME.........COMM
-------.......---------
ALLEN.........300
WARD..........500
TURNER
MARTIN........1400

The subquery returns the value UNKNOW, as Turner has an unspecified commission. When applying the EXISTS operator on an UNKNOWN result, the result is converted into FALSE, and when then applying the NOT operator on FALSE, the result is converted into TRUE.
Therefore for the sole purpose of establishing a department's bonus, using this particular SQL statement, the result is in fact incorrect, as the result is actually UNKNOWN.

OMS-51275: Only use the NOT IN operator where the expression will either result in TRUE or FALSE.
Rationale The NOT IN operator returns the statement to false when at least one of the values are UNK(nown). This may yield an incorrect result.
Example select..'TRUE'
from....emp
where...deptno not in (5,15,null)

This example returns no rows because the WHERE clause condition evaluates to:
deptno != 5 AND deptno != 15 AND deptno != null
Because all conditions that compare a null result in a null, the entire expression results in a null. This behavior can easily be overlooked, especially when the NOT IN operator references a subquery.

Select
OMS-51301: Specify a full column list in a SELECT statement. Do not use the asterisk (*) shorthand in SELECT statements in application programs.
Rationale The asterisk implies all columns, while most of the time you will be selecting data into a limited number of variables. Each column you select passes through the interface layers of the Oracle Server, which requires extra memory for the context area.

OMS-51302: Only select those columns that you actually use in your program.
Rationale Each column you select passes through the interface layers of the Oracle Server, which requires extra memory for the context area.

OMS-51303: Prefix each column in the SELECT list with a table alias, even in a statement that selects rows from a single table or view.
Rationale Aliases make it easier to build and maintain SQL statements. An alias makes a SQL statement more readable and easier to expand without becoming sensitive to errors.

OMS-51304: Specify a column alias for expressions, columns that are used more than once in a select list and for columns that are modified by a function. Use a meaningful column alias that indicates what the expression does.
Rationale Aliases makes it easier to reference to these columns. An alias makes a SQL statement more readable and easier to expand without becoming sensitive to errors. Using an alias that indicates what the expression does, makes the code less prone to errors due to misunderstandings.
Example select..emp.name...empname
,.......mgr.name...mgrname
from....emp emp
,.......emp mgr
where...emp.mgr = mgr.empno;

OMS-51305: Never use the original column name for a column alias.
Rationale If, later in your program, you refer to a field named after the column alias, you may have forgotten that you are not referring to the original column. This might be a problem for example when the column is converted to another datatype to obtain specific date formatting with the original datatype. If you use the original column name for the column alias, you will not be able to see that it has a different datatype than the original column. This may cause implicit datatype conversion.

OMS-52846: When using expressions with grouping operations in a select list, then first perform the grouping operation and the expression.
Rationale This performs better than the other way around.
Example select..max(emp.sal) * 1.5 max_sal
from....oms_employees emp

instead of:

select..max(emp.sal*1.5) max_sal
from....oms_employees emp

From
OMG-10091: Do not use subqueries in the FROM clause of a SQL statement.
Rationale SQL statements with subqueries on the FROM clause can often be rewritten into a SQL statement that is easier to understand.
Example select..emp10.ename
,.......emp10.dname
,.......salgrd.grade
from...(select emp.ename
........,......emp.sal
........,......dept.dname
........from...oms_employees....emp
........,......oms_departments..dept
........where..dept.deptno......= 10
........and....emp.deptno.......= dept.deptno
........)............emp10
,.......oms_salgrade salgrd
where...emp10.sal between salgrd.losal
..................and.....salgrd.hisal
;

This statement above gives the same result as:

select..emp.ename
,.......dept.dname
,.......salgrd.grade
from....oms_employees....emp
,.......oms_departments..dept
,.......oms_salgrade.....salgrd
where...dept.deptno = 10
and.....emp.deptno..= dept.deptno
and.....emp.sal.....between salgrd.losal
....................and.....salgrd.hisal
;

OMS-51320: Specify an alias, preferably its short name as defined in the Oracle Designer Repository, for each table or view in the FROM clause, even if the SELECT statement only uses one table or view.
If the table or view is used more than once in the SQL statement, then , preferably use a three character long table alias to give a logical indication of what each table usage represents. If this is difficult, then add a number to the table short name to make the table alias unique.
Rationale Aliases make it easier to build and maintain SQL statements. An alias makes a SQL statement more readable and easier to expand without becoming sensitive to errors.
Example select..emp.name
,.......mgr.name
from....emp emp
,.......emp mgr
where...emp.mgr = mgr.empno;

This is easier to understand than:

select..emp1.name
,.......emp2.name
from....emp emp1
,.......emp emp2
where...emp1.mgr = emp2.empno

OMS-51321: Do not prefix table names in the FROM clause with schema owner names.
Rationale Using schema owner names as table name prefix makes the SQL code less portable from one environment to another. The schema owner name may be different in another environment. Use synonyms instead.

Where
OMG-10090: Use bind variables when the SQL statements used are identical with the only exception of a comparison value.
Rationale Bind variables can be used to ensure that SQL statements are shared across repeated executions. Therefore, when using bind variables in this way you prevent the SQL statement to be parsed on every execution only because the supplied value has changed.
Example select emp1.ename
from...oms_employees emp1
where..emp1.deptno = 10;

and

select emp1.ename
from...oms_employees emp1
where..emp1.deptno = 20;

can be changed into:

select emp1.ename
from...oms_employees emp1
where..emp1.deptno = b_deptno;

OMS-51340: Write any join conditions directly following the reserved word WHERE.
Rationale This is good programming practice.

OMS-52841: Declare bind variables used in SQL statements with the type and length identical to the column definition with which they are compared.
Rationale This prevents implicit conversion while comparing values. Also, inconsistent bind types can cause multiple child cursors to be created for the same SQL statement.

OMS-52845: Never compare a column with an empty string in a where clause. Compare it with NULL instead.
Rationale Comparing a column with an empty string always gives the result 'no rows selected '.
Example select ename
from...emp
where..job is null;

delivers all the employees where no job has been entered, while

select ename
from...emp
where..job = '';

delivers 'no rows selected '.

OMS-52848: Only use the BETWEEN operator when the boundaries are of the same type.
Rationale Implicit datatype conversion may disable index use or result in incorrect value comparisons.

OMS-52849: Avoid using a constant in front of the BETWEEN operator.
Rationale When this is done it is hard to understand what is actually meant.
Example Do not use:
select..emp.ename
from....oms_employees emp
where...2500 between emp.sal
............and.....emp.comm

Group By
OMS-51360: Do not group on columns modified by other expressions or functions other than those stated in the SELECT list.
Rationale If you do, this may lead to confusing query results.

OMS-51361: Place as many conditions as possible in the WHERE clause instead of in the HAVING clause.
Rationale In this way, the number of rows that need to be sorted is limited. The grouped data that is sorted by the HAVING clause is by definition, unindexed.

Connected By
OMS-51380: Always specify a START WITH clause in combination with a CONNECT BY clause.
Rationale The START WITH identifies the row(s) to be used as the root(s) of a hierarchical query. If you omit this clause, Oracle uses all rows in the table as root rows, which will have a negative impact on performance.
Example select..ename
,.......empno
,.......mgr
,.......job
from....emp
start with job = 'PRESIDENT'
connect by prior empno = mgr
;

OMS-51381: Do not reference the LEVEL pseudo-column in the START WITH clause.
Rationale Referencing LEVEL in the START WITH condition is meaningless.

OMS-51382: Do not use a CONNECT BY clause if circular referencing is a property of your data.
Rationale Otherwise, this may lead to the following error message:
ORA-01436: CONNECT BY loop in user data

Order By
OMS-51400: Use the ORDER BY clause only when your data really needs to be presented in a sorted order.
Rationale The ORDER BY clause requires a sort operation if the Oracle Server cannot use an index to present the rows in a sorted order. This sort operation can be relatively expensive, especially when a large number of rows is processed.
A sort operation can also have a negative impact on perceived response time; the user only sees the first results of the query when the complete sort operation is finished.

OMS-51401: Do not rely on sorting that occurs by index use without specifying an ORDER BY clause.
Rationale Using the Cost-Based Optimizer, you cannot be sure an index is always used.

OMS-51402: Use column aliases to reference column names in the ORDER BY list with the set operators UNION, MINUS, and INTERSECT.
Rationale Number references in the ORDER BY Clause are not SQL92 ANSI standard and may be desupported in future versions of Oracle Server.
Example select..emp.empno
,.......emp.ename.....name
from....oms_employees emp
union
select..prs.id
,.......prs.name......name
from....oms_persons...prs
order by name
;

OMS-51404: Use the DESC keyword only if one or several columns have to be sorted in descending order.
Rationale The default ordering is ascending.

OMS-51405: If you use mixed ascending and descending sorting, specify both the ASC and DESC keywords for all column names in the ORDER BY list.
Rationale This makes the code easier to understand and maintain.

For Update
OMS-51420: Limit the number of rows that you reserve for future update by always specifying a restrictive WHERE clause.
Rationale The Oracle Server locks all the records you select using FOR UPDATE even before they are retrieved. Therefore, it is important to limit the number of records to the minimum.

OMS-51421: Always specify a column list in the FOR UPDATE clause.
Rationale Omitting the column list in a join statement results in placement of row locks on all rows of all tables selected by the statement.

OMS-51422: Specify the primary key column(s) of a table, prefixed with the table alias, in the FOR UPDATE clause of a SELECT statement.
Rationale This is to indicate from which table(s) rows must be locked. If you do not indicate this, then row locks will be placed on all rows of all tables selected by the statement

OMS-51423: Only specify NOWAIT in a FOR UPDATE clause if the resulting error can be properly handled by the programming environment.
Rationale Otherwise, unexpected and unwanted behavior may occur.

OMS-51424: If possible, use the CURRENT OF CURSOR mechanism in combination with a FOR UPDATE clause, instead of direct references to a ROWID
Rationale This simplifies your code and makes it more readable and easier to maintain.

Subqueries
OMS-51440: Only use subqueries if the desired query result cannot be achieved through a join condition
Rationale Joins often performs better than subqueries.

OMS-51441: Only use correlated subqueries when the result cannot be achieved through a non-correlated subquery (by replacing column references in the subquery with bind variables).
Rationale Non-correlated subqueries are generally preferred above correlated subqueries, since then the subquery will be executed only once (unless the NOT IN operator is used).
A correlated subquery however, is evaluated once for each row processed by the parent statement.

OMS-51442: Do not use the concatenation operator when comparing a column list from a subquery to a column list from the main query.
Rationale A VARCHAR2 concatenation disables index use in the main query and distorts the collating sequence for DATE and NUMBER datatypes, producing incorrect query results.
Example Do not use:
select..emp1.ename
from....oms_employees..emp1
where...(to_char(emp1.deptno)||emp1.ename
........) in
..........(select to_char(emp2.deptno)||emp2.ename
...........from...oms_employees..emp2
...........where..emp2.ename like 'SCO%'
..........)

Use:
select..emp1.ename
from....oms_employees..emp1
where...(emp1.deptno
........,emp1.ename
........) in
..........(select..emp2.deptno
...........,.......emp2.ename
...........from....oms_employees..emp2
...........where...emp2.ename like 'SCO%'
..........)

OMS-51443: When comparing a column list from a subquery to a column list from the main query, only perform any necessary datatype conversion in the SELECT list of the subquery.
Rationale This ensures that the Cost-Based Optimizer will continue to consider the use of indexes in the execution plan.

Outer Join
OMS-51450: Do not use outer-join columns in predicates that are branches of an OR.
Rationale Using outer-join columns in predicates that are branches of an OR makes the logical expressions complicated, and therefore may not be well formed.

OMS-51451: Limit outer join columns to the top level of the WHERE clause, or to (nested) AND predicates only.
Rationale Otherwise, you increase the complexity of the logical expressions, and they may therefore not be well formed.

Insert
OMS-51460: Specify a full column list for the target table in INSERT statements.
Rationale This makes your statement less vulnerable to changes. If a table is recreated with a different column ordering, your statements will still work.

OMS-51461: Specify NULL values for unknown values in INSERT statements with the VALUES or SELECT FROM construct. Do not rely on the implicit insertion of NULL values in non-specified columns.
Rationale If you do not do this, then it is not clear from your statement whether a column is deliberately left out to insert a NULL value, or was simply forgotten. Also, this prevents default values from being generated whenever a default is defined for the column.

Update
OMS-51480: Only use ROWID in an UPDATE statement after you have first locked the target rows using a SELECT FOR UPDATE statement in the same transaction.
Rationale This is to make certain that you are updating the same row you intend to update. If another user has deleted the record in the mean time, a new record may get the same ROWID as the deleted record, and you would thereby update a different record than the one you initially intended to update.

Delete
OMS-51500: Always specify the FROM keyword in DELETE statements.
Rationale Mixing the usage, to sometimes use FROM and sometimes not, is confusing.

OMS-51501: Only use ROWID in a DELETE statement after you have first locked the target rows using a SELECT FOR UPDATE statement in the same transaction.
Rationale This is to make certain that you are deleting the row you actually intend to delete. If another user has deleted the record in the mean time, a new record may get the same ROWID as the deleted record, and you would thereby physically delete a different record than the one you initially intended to delete.

Returning Clause
OMS-52843: Use DML returning clause if you need access to values that are server-derived as a result of a DML statement you are performing
Rationale When using a RETURNING clause to return values from updated columns, you eliminate the need to perform a SELECT following the DML statement.
Example The following example returns values from the updated row and stores the result in PL/SQL variables BND1, BND2, BND3:

update.....oms_employees
set........job ='MANAGER'
,..........sal = sal + 1000
,..........deptno = 20
where......ename = 'JONES'
returning..sal*0.25
,..........ename
,..........deptno
into.......bnd1
,..........bnd2
,..........bnd3
;

Views
OMS-51520: Specify a full column list in a CREATE VIEW statement.
Rationale If you use the *, the statement is transformed into a full column list. However, if a column is added to the table, then the view needs to be recreated. Therefore using the * may lead to unexpected behavior.

OMS-51521: Prevent the use of functions or expressions in a CREATE VIEW statement, apart from group functions, if the columns based on these expressions will be searched.
Rationale This has a negative effect on performance.

OMS-51522: For all columns modified by expressions or functions in the SELECT list of a CREATE VIEW statement, use the column alias as a column name in the view.
Rationale In this way, you can easily match the view columns and their source in the SELECT statement.

OMS-51523: Avoid the use of columns in the SELECT list of a view that belong to the outer-joined table.
Rationale This ensures that the Cost-Based Optimizer will continue to consider the use of indexes in the execution plan.

Analyzing SQL Statements
OMS-51705: Use the VALIDATE STRUCTURE (CASCADE) command in scripts that check referential integrity.
Rationale In this way you get verification of whether the structure of the object is corrupt or not. If it is corrupt, the object must be dropped and recreated.

OMS-52844: Ensure that your SQL statements do not use more than 1MB of sharable memory. Check the usage by using V$SQL.
Rationale You should be careful with large and complex SQL statements. These may use a considerable amount of sharable memory which places a large burden on the shared pool. The larger the SQL statement, the more memory allocations is required to build a sharable cursor in the cursor cache.

Therefore SQL statements which require a large amount of memory eventually evolve into a scalability problem as it limits the amount of sharable cursors that can be active in the shared pool. Large SQL statements may consume several megabytes of sharable memory.
Example In the example below, you can see how much sharable memory has been used for the simple SQL Statement:
SQL> select ename
2 from emp;
SQL> select sql_text
2 , sharable_mem
3 from v$sql
4 where sql_text like '%select ename from% ';

SQL_TEXT.................................SHARABLE_MEM
------------------------------------.....----------
select ename from emp....................6139

In this example a bit more than 6K of shared memory has been used.

PL/SQL Standards
OMG-10084: Every logical block of executable statements should be separated from each other with one blank line.
Rationale This makes the code easier to read.

OMG-10085: Use named parameters to call functions and procedures with more than three parameters.
Rationale This makes the code easier to read, and less vulnerable to errors.
You do not need to know the order in which the formal parameters are listed. Also, you do not need to include more parameters than exactly those needed in the call in the specific situation.

OMG-10092: Your PL/SQL code needs clarification when:
* the code is getting complex with a lot of nested code
* the number of statements grows large within one program unit
* the code includes unusual or complex constructs

Choose from the following options to clarify your code:
  • use comment lines (see standard OMS-52101)
  • place portions of the code in separate program units, and use meaningful names for the new program units.
Rationale This makes it easier to read and maintain the code.

OMS-52001: Do not interfere with the Oracle Forms transaction mechanisms from within a PL/SQL code segment using COMMIT, SAVEPOINT, or ROLLBACK. The only exception is POST, which you are allowed to use in combination with the Oracle Server with the transaction processing option.
Rationale If you interfere with Oracle Forms transaction mechanism, this may cause unexpected behavior.

OMS-52832: Indent your code. Each indent is defined as 3 blanks. Do not use tabs to create this indentation, use spaces instead. Indentation is started at the first line after a line that ends with:
- begin
- loop
- then
- exception

Un-indentation (return to previous level of indentation) should be applied at the first line after a line containing:
  • end
  • end loop
  • end if
Rationale The code is easier to read when the code is indented. You should not use tabs because the number of characters in a tab can differ across systems and word editors, causing the layout to be messed up when moving from one environment to another.

OMS-52834: Use the following layout for procedure and function declarations:

procedure convert_unit
( p_from in varchar2
, p_to in varchar2
, p_qty_in in number
, p_qty_out out number
)
is

function convert_unit
( p_from in varchar2
, p_to in varchar2
, p_qty_in in number
) return number
is
Rationale This makes the code easier to read, and it is less likely that any commas and brackets will be forgotten when writing the code.

OMS-52835: In calls to program units, place each parameter on a new line, and align as follows:

convert_unit
( l_uni_id_from
, l_uni_id_to
, l_qty
);

or if you are using named parameters:
convert_unit
( p_from....=> l_uni_id_from
, p_to......=> l_uni_id_to
, p_qty_in..=> l_qty
);

Note: The dots indicate spaces
Rationale This makes the code easier to read, and it is less likely that any commas and brackets will be forgotten when writing the code.

OMS-52836: When concatenating strings use the following layout:

l_string := 'abc_'
............||to_char(p_uni_id)
............||'_xyz';


Use the following layout in a call to a program unit:

convert_unit
( l_uni_id
, 'abc'
..||to_char(p_uni_id)
..||'_xyz'
);

Note: The dots indicate spaces
Rationale This makes the code easier to read, and it is less likely that any commas and brackets will be forgotten when writing the code.

OMS-52837: The general layout of a PL/SQL block is:

<<label>>
declare
---(...)
begin
---(...)
exception
---(...)
end;


Note: The three --- hyphens outside the brackets indicate the number of spaces required. The (...) indicate programming code.

The layout of a IF-THEN-ELSE is:

if (...)
then
---(...)
elsif (...)
then
---(...)
else
---(...)

end if; --if_label


Note: The section in italic is optional.

The layout of a LOOP is:

<<loop_label>>
loop
---(...)
end loop loop_label;


The layout of a FOR loop is:

<<for_loop_label>>
for i_counter in i_start..i_end loop
---(...)
end loop for_loop_label;


Layout of WHILE loops:

<<while_loop_label>>
while (...) loop
---(...)
end loop while_loop_label;
Rationale This makes the code easier to read.

OMS-52838: Do not use the mixed notation (positional and named parameters) in the parameter list to call functions and procedures.
Rationale Mixing notations is confusing. Therefore, keeping it consistent makes the code easier to read, and in addition less vulnerable to errors.

OMS-52839: Use the following layout for a CURSOR:

declare
...cursor...c_table_alias cursor
...(........b_var1...datatype1
...,........b_var2...datatype2
...)........is
...select...column1
...,........column2
...(...)
...from.....table
...where....condition1;


Note: The dots indicate the number of spaces required to layout the statement properly.
Rationale This makes the code easier to read.

OMS-52840: If you want to explicitly identify an if-then statement, use the following layout:

if (condition1)
then
...if condition2
...then
......(...)
...else
......(...)
...end if; --if_label cond2
else
...(...)
end if; --if_label cond1


The comment after the 'end if' identifies the statement, and should refer to the condition after the '''if'''. For example, a logical indication, the actual expression or the name of a variable used in the condition.
Note: The dots outside the brackets indicate the number of spaces required.
Rationale This makes it obvious to which if the end if belongs. This makes your code easier to read, especially when the if-then statement grows large.

OMS-52847: Use the following layout for an if statement, when more than one condition needs to be tested in the if clause:


if...condition1
and..(condition2
......or
......condition3
.....)
then


Note: The dots indicate the number of spaces required.
Rationale This makes the code easier to read.

OMS-60062: Prevent messages from being hard-coded into the application.
Rationale This offers you the flexibility to easily change message text online and implement multi-lingual messaging.

Inline Documentation
OMS-52100: Use the double hyphen-style comment markers: --. Only use the '/*' and '*/' comment markers in package specification headings.
Rationale When you have used double hyphens comment markers, you can easily use the '/*' and '*/' comment markers to temporarily comment out large pieces of code (for example, for debugging purposes).
Nested '/*' and '*/' comment markers do not work.
For package specification headings, this is less important as the comment is placed before the code starts.

OMS-52101: Use comments if a group of statements need clarification. Place them before the code segment commences.
The comment should give a short clarification on what is performed within the group of PL/SQL statements.
Rationale This makes it easier to understand and thereby maintain the code.

OMS-52102: If you want to give a short comment for a single line of PL/SQL code, then start the comment at the (end of the) same line as the line for which it is a comment.
If the comment lines become too long to put at the end of the same line, then instead use a new line above the statement with the same indent as the statement itself.
Rationale This makes it immediately obvious to which section the comment applies. The code is then easier to read.
Example if l_name = c_emp.ename -- this is a short comment
then
...-- this is a long comment that will not fit at the end
...l_deptno := c_emp.deptno
...(...)
end if;

Note: The dots indicate spaces.

OMS-52103: Remove all comment markers implemented for debugging purposes before you deliver a PL/SQL module.

Naming Conventions
OMS-52200: A PL/SQL variable, except for fields in record structures, should not have a name identical to a column name. Try to assign to each variable in a PL/SQL code segment a unique name. However, if necessary, use block names as a prefix to make names of variables local to the block unique. If the block is anonymous, then give it a block label for this purpose.
Rationale If you use the same name, it is possible to accidentally mix the column and variables within the same program unit, resulting in undesired behavior.

OMS-52201: Do not use reserved words or the word STANDARD as a block or loop label.
Rationale PL/SQL declares built-in functions globally in package STANDARD. Redefining it locally is error prone because your local declaration overrides the global declaration.

Attention: Oracle 8 reserved words are:
ACCESS ADD ALL ALTER
AND ANY AS ASC
AUDIT BETWEEN BY CHAR
CHECK CLUSTER COLUMN COMMENT
COMPRESS CONNECT CREATE CURRENT
DATE DECIMAL DEFAULT DELETE
DESC DISTINCT DROP ELSE
EXCLUSIVE EXISTS FILE FLOAT
FOR FROM GRANT GROUP
HAVING IDENTIFIED IMMEDIATE IN
INCREMENT INDEX INITIAL INSERT
INTEGER INTERSECT INTO IS
LEVEL LIKE LOCK LONG
MAXEXTENTS MINUS MODE MODIFY
NETWORK NOAUDIT NOCOMPRESS NOT
NOWAIT NULL NUMBER OF
OFFLINE ON ONLINE OPTION
OR ORDER PCTFREE PRIOR
PRIVILEGES PUBLIC RAW RENAME
RESOURCE TRIGGER REVOKE ROW
ROWID ROWNUM ROWS SELECT
SESSION SET SHARE SIZE
SMALLINT START SUCCESSFUL SYNONYM
SYSDATE TABLE THEN TO
UID UNION UNIQUE UPDATE
USER VALIDATE VALUES VARCHAR
VARCHAR2 VIEW WHENEVER WHERE

OMS-52210: Begin each cursor name with "c_", followed by the short name of the most important table in the SELECT statement. If a table short name is needed to use in more than one cursor-name in a PL/SQL code segment, add a logical name as a suffix to all cursors to make the cursor names unique.
Rationale This makes it easy to read the code. It is obvious that it concerns a cursor, and what table is involved in the select statement of the cursor. The logical suffix gives an indication on what kind of select the cursor is performing.

OMS-52211: The name of a cursor bind parameter should start with "b_".
Rationale This makes it easy to understand that it concerns a cursor bind parameter.

OMS-52212: Name the record structure after the cursor, replacing the "c_" prefix with an "r_" prefix.
Rationale This makes it easier to read the code. It is easy to see that the record contains the result of the belonging cursor.

OMS-52230: If datatype conversion occurs through a formatting function, add a suffix to the name of the target variable, indicating the new datatype:
"_c" for CHAR or VARCHAR2
"_d" for D ATE
"_n" for NUMBER
If the target variable is a field in a record structure, use a column alias to name the field. If the formatted field does not contain the same information anymore (for example, DD-MM-YYYY vs. YYYY), use a meaningful name.
Rationale In this way, it is immediately visible what the datatype of the target variable is. This is especially important for converted variables in order to easily distinguish between the original value and the converted value.

OMS-52231: Name each temporary or local variable after the database column, prefixed with "l_". Assign temporary or local variables that do not have a direct relationship with a column a meaningful name, prefixed with "l_".
Rationale This makes it easy to identify which variables are local, and what kind of information they represent.

OMS-52240: A cursor loop is named after the cursor it controls, with the "c_" prefix replaced with a "r_" prefix.
Rationale This makes it easier to read the code because it is easy to recognize to which cursor the block is related.

OMS-52250: Name an index variable after the loop by prefixing the loop label name with "i_".
Rationale This makes it easier to read the code because it is easy to recognize to which loop the index belongs. See also OMS-52590.

OMS-52260: Do not declare any exception handler with the same name as a standard Oracle exception handler.
Rationale If you declare exceptions with the same name as standard Oracle exceptions, you are not able to trap the standard Oracle exception of the same name in your exception handler. It only responds to the user-defined exception.

OMS-52270: The name of an exception should start with "e_" and must be a meaningful name (never the same as a standard Oracle exception).
Rationale This makes it easy to identify that it is an exception and what the exception is about.

OMS-52280: The name of a record variable should be suffixed with "_rec".
Rationale This makes it easy to identify that the variable is a record variable.

OMS-52285: The name of a table variable should be suffixed with "_tab". The corresponding index should be prefixed with "i_". This index is the number of an element in the table.
Rationale This makes it easy to identify that the variable is a table variable.

OMS-52290: Functions must be given a meaningful, preferably pronounceable, name. If functions are defined to support other functions (for example, conversion), a short name for the supported function can be part of that function's name. If supporting functions are general, that is common functions which support several other functions, then prefix the function's name with 'gen_'.
Rationale This makes it easier to read, understand and pronounce the code.

OMS-52291: Procedures must be given meaningful, preferably pronounceable, names. If procedures are defined to support other procedures (for example, value checking), a short name for the supported procedure can be part of that procedure's name. If supporting procedures are general procedures, that is common functions which support several other procedures, then prefix the procedure's name with 'gen_'.
Rationale This makes it easier to read, understand and pronounce the code.

OMS-52292: The name of a package should reflect the function(s) that are stored within that package.
Rationale This makes it easier to read and understand the code.

OMS-52820: Prefix variables local to the program unit with 'l_'.
Rationale This makes it easy to identify which variables are local.
Example l_lastfield

OMS-52821: Prefix variables global to all program units in the package body with 'g_'.
Rationale This makes it easy to identify which variables are global.
Example g_client

OMS-52822: Prefix parameters for procedures and functions with 'p_'.
Rationale This makes it easy to identify that they are parameters.
Example p_contract

OMS-52823: Use index variable 'i' or 'j', or prefix index variables (loop counters) with 'i_logical_name'.
Rationale This makes it easy to identify that they are index variables.
Example i_counter

OMS-52824: Suffix PL/SQL Cursor return type declarations with '_curtype'.
Rationale This makes it easy to identify that they are PL/SQL Cursor return types.

OMS-52825: Suffix PL/SQL 8 Object type declarations with '_objtype'.
Rationale This makes it easy to identify that they are PL/SQL 8 Object types.

OMS-52826: Suffix PL/SQL record type declarations with '_rectype'.
Rationale This makes it easy to identify that they are PL/SQL record types.

OMS-52827: Suffix PL/SQL subtype declarations with '_subtype'.
Rationale This makes it easy to identify that they are PL/SQL subtypes.

OMS-52828: Suffix PL/SQL table type declarations with '_tabtype'.
Rationale This makes it easy to identify that they are PL/SQL table types.

OMS-52829: All variable names, PL/SQL Record names, PL/SQL Table names, exception names, parameter names, cursor names and type names should be in lowercase. Underscores ('_') are allowed. (for example: p_emp_sal).
Rationale This makes it easier to write consistent code, as the programmer does not have to change between upper and lower case when typing the code.

Declarative Part
OMS-52300: Only use the following datatypes in PL/SQL declarations:
VARCHAR2
NUMBER
PLS_INTEGER
DATE
BOOLEAN
BLOB
CLOB
Do not use the datatypes RAW, LONG RAW, or the IBM compatible datatypes in PL/SQL variable declarations.
Rationale The datatype CHAR may lead to coding errors when comparing values in PL/SQL code. A
CHAR(1) column does not use less space than a VARCHAR2(1) column.
The IBM compatible types are NUMBER subtypes. NUMBER variables with a maximum length precision are preferred, unless you need it for compatible reasons.
PLS_INTEGER is preferred above BINARY_INTEGER for performance reasons.
PL/SQL cannot interpret RAW data.

OMS-52301: Do not declare index variables for numeric loops; rely on implicit declaration of index variables.
Rationale This makes the code less prone to errors.
Example Use:
begin
...<<hours>>
...for i_hours in 1..24 loop
......(...)
...end loop; --hours
end;

instead of:

declare
...i_hours number(2,0);
begin
...<<hours>>
...for i_hours in 1..24 loop
......(...)
...end loop; --hours
end;

Note: The dots indicate spaces

OMS-52302: If a variable must always have a value, declare the variable as NOT NULL and assign it a (starting) value at declaration.
Rationale This makes it easier to read the code, and thereby to prevent coding errors during initial coding and maintenance.

OMS-52303: If a variable contains a known constant value throughout the program, declare it as a constant.
Rationale This makes it easy to identify that the value is truly a constant and may/will never change.

OMS-52304: Never redeclare globally declared identifiers from the package STANDARD
Rationale PL/SQL declares built-in functions globally in package STANDARD. Redeclaring them locally is error prone because your local declaration overrides the global declaration.

OMS-52310: Declare explicit cursors for all SQL statements that return more than one row or no rows at all.
Rationale This prevents the program ending up in unnecessary exceptions.

OMS-52311: If a SELECT statement requires bind variables, declare these bind variables as cursor parameters.
Rationale This makes it easier to read and understand the code as it is easy to identify which values are used. Also values can be assigned as part of the open cursor statement.

OMS-52320: Use the %TYPE attribute referencing a column in the table(s) used in the SELECT statement to declare the datatype for a cursor parameter.
Rationale This makes your code less vulnerable for changes. If the datatype of the database column changes, then you would not need to change the cursor parameter declaration.

OMS-52321: Specify a full list of all columns in each SELECT statement you declare, instead of simply using the select *. As an exception, you are only allowed to use an asterisk when more than 75% of all columns of a table have been selected.
Rationale The declaration of the necessary record variable for the cursor, reserves space for all columns in the cursor select-statement. This would demand too much unused resources if through the select * space is reserved for a large number of columns that are not used anywhere in the code.

OMS-52330: Use the %ROWTYPE attribute to declare a record structure for each explicitly defined cursor. Do not use the implicit record declaration mechanism. The only exception to this rule is when you use a cursor FOR loop.
Rationale This makes your code less vulnerable to changes. Whenever a column is added or removed from the cursor definition, you do not need to change your %ROWTYPE attribute.

OMS-52340: Use the %TYPE attribute to declare a temporary variable that is used to store a value related to a database column.
Rationale This makes your code less vulnerable to changes. If the datatype of the database column changes, then you would not need to change the temporary variable declaration.

OMS-52360: Place all user-defined exceptions together in the declarative part of the PL/SQL block. First specify all user-defined Oracle exceptions. Then specify all non-Oracle-related exceptions.
Rationale This makes it easier to read and therefore understand the code.

OMS-52361: Explicitly declare exceptions for each of the Oracle error conditions you can expect to occur in normal program execution. In the comment you specify the text of the Oracle error message.
Rationale Declaring and trapping expected errors makes it possible to provide exact error handling and messaging. Providing the text of the error message as a comment makes it easier for the programmer to understand what kind of errors will raise the various exceptions.
Example declare
...(...)
...e_snapshot_too_old...exception;
...-- ORA-01555: Snapshot too old (Rollback segment too small)
...pragma exception_init(e_snapshot_too_old
........................, -1555
........................);
...(...)
begin
...(...)
exception
...when e_snapshot_too_old then
........(...)
end;

Note: The dots indicate spaces

OMS-52362: Define exceptions and exception handlers for functional errors or warning conditions. In the comment, you must specify the condition(s) that raise the exception.
Rationale Grouping all the exceptions into a single handler makes the code easier to read and maintain. Also using exceptions makes it possible to raise the same exception from different parts of the whenever a similar error occurs, that is handled in the same way in the exception handler.
Example declare
...-- No more available seats for re-assignment
...e_out_of_seats...exception;
...(...)
begin
...(...)
...raise e_out_of_seats;
...(...)
exception
...when..e_out_of_seats then
.........(...)
end;

Note: The dots indicate spaces.

OMS-52363: Customize exception handlers that use raise_application_error so that only error number -20000 is used. Customize the message text explicitly to pass module-relevant messages.
Rationale The user-supplied error numbers must be between -20000 and -20999, because Oracle reserves all other numbers for standard errors. Using a single number prevents running out of numbers and lightens the task of having to administer unique message numbers.

Reference: See also the Oracle Headstart documentation, section End User Assistance, Message Handling how you can use qms$errors.show_message to raise application errors and use application specific error numbers.
Executable Part
OMS-52500: Avoid implicit datatype conversion. If you expect implicit datatype conversion to occur, you must place an explicit datatype conversion function around the expression.
Rationale Avoiding implicit datatype conversion makes it easier to follow what is happening in your program. Especially when tracking errors, implicit datatype conversion is easily overseen and thereby the actual error may be difficult to detect.

OMS-52510: Do not use NULL statements (null;) in your PL/SQL code in your completed code. Before you hand over your PL/SQL code, remove all NULL statements that were never originally envisaged.
Rationale NULL statements do not perform any actions. It is only confusing to read the code when threads end up in NULL statements.
Normally, you should only use the NULL statement as a temporary placeholder for PL/SQL code that still has to be developed.
However, in some situations, NULL statements can be useful. Although not recommended (see OMS-52310), an example would be catching a NO_DATA_FOUND exception. In those cases, use inline comments to clarify its use.

Loop Constructs
OMS-52530: Make sure that in each of the loop constructs the condition to end the execution of the loop will eventually occur. Be careful with the occurrence of NULL values in the loop condition that may cause early loop termination or no loop termination at all.
Rationale Otherwise, you may end up in an indefinite loop, and eventually run out of memory.

OMS-52531: Use a cursor FOR loop as the preferred method of handling SELECT statements that return more than one row.
Rationale This is effective coding. You simplify coding by using this as you do not need to perform explicit OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.

Cursor Handling
OMG-10081: Do not use multiple cursors as the source for one record variable and vice versa. Preferably there should be a one-to-one link between the cursor and a record variable. Only when more than one cursor-result record is used at the same time, should you use more than one record variable for one cursor.
Rationale Using multiple cursors as the source for a single variable makes it more difficult to read and understand the code, as you do not immediately know what the record variable is referencing.

OMS-52534: Avoid the use of explicit cursors to perform DML operations.
Rationale This may impede performance.

OMS-52540: You must CLOSE explicitly declared cursors not handled by a cursor FOR loop in exception handlers. Test whether these explicit cursors are still open using the %ISOPEN cursor attribute.
Rationale You can have a limited number of cursors open at the same time. The cursor is released in the close cursor statement.

OMS-52560: Use the CURRENT OF CURSOR mechanism in combination with a SELECT FOR UPDATE statement, instead of making your own reference to a ROWID.
Make explicit use of ROWID when the FOR UPDATE OF will lock too many records too soon and/or you need to perform COMMIT; while the cursor is open.
Rationale This simplifies your code and makes it easier to read and maintain.

OMS-52570: Only specify the BEGIN and END keywords around a subblock when you need to declare local identifiers or perform exception handling in the subblock.
Rationale Unnecessary use of keywords makes the code more difficult to understand and maintain.

OMS-52575: Do not use the GOTO statement.
Rationale Use of GOTO statements are not necessary in PL/SQL. Use of GOTO statements can result in complex, unstructured code (sometimes called spaghetti code) that is hard to understand and maintain.

Labels
OMS-52590: If you want to identify a loop, then use a loop label using the following layout:

The layout of a LOOP is:

<<loop_label>>
loop
...(...)
end loop loop_label;


The layout of a FOR loop is:


<<for_loop_label>>
for i_counter in i_start..i_end loop
...(...)
end loop for_loop_label;


Layout of WHILE loops:


<<while_loop_label>>
while (...) loop
...(...)
end loop while_loop_label;


Note: The dots outside the brackets indicate the number of spaces required.
Rationale This improves the readability of your code, and this method is preferred above comment lines because the name of the label after the end loop is checked by the compiler.

Recursion
OMG-10087 : Be careful about using recursive call of a function or procedure that contains SQL statements. Instead, use loop constructs like a cursor FOR loop, or use the non-procedural capabilities of SQL.
Rationale Using cursor FOR loops are more effective and often easier to understand.

OMS-52532: Make sure that a recursive subprogram has at least two paths -- one that leads to the recursive call and one that leads to a terminating condition.
Rationale This prevents the recursive program from ending in an indefinite loop resulting in the program running out of memory.

Debugging
OMS-52610: Use the standard packages supplied by Oracle to extract debugging information at critical places from stored procedures. Locate the calls to the I/O procedures in your stored procedures, along with a mechanism to activate them through, for example, a global variable.
Rationale Using mechanisms to turn debug information on and off, makes it easier to test and debug your code.

Exception-Handling Part
OMS-52700: Do not place excessive coding in the exception-handling part of a PL/SQL block; only specify the code that is strictly necessary to handle the exception effectively.
Rationale The exception part is only meant to handle the specified exceptions quickly and effectively. Using excessive coding within the exception increases the risk that an exception may occur within the exception. This exception is passed to the enclosing block (if any). It may not be clear what actually should occur in the enclosing exception. In general the code is more difficult to read, understand and thereby maintain. You should not attempt to bypass this rule by calling a procedure that performs the excessive coding. The exception is if this procedure is an error messaging routine that provides standard error messaging.

OMS-52701: Do not use SQLCODE to branch within the exception-handling part.
You should explicitly define exceptions for each of the Oracle error conditions you can expect to occur in normal program execution.
Rationale This makes it easier to see what kind of exceptions may be expected from the program. In addition, it prevents you from having to include excessive coding within the exception handler to distinguish between the various exceptions.

OMS-52702: Do not prefix an exception with the keyword STANDARD.
Rationale Using STANDARD as a prefix may cause problems as this is a public synonym for an Oracle-provided package of stored procedures.

Stored Package, Procedures and Functions
OMG-10086: Instead of using PL/SQL program units outside the server, use stored PL/SQL program units as much as possible.
Rationale Stored procedures and functions are available to all tools accessing the database. Therefore using these as much as possible prevents a lot of code replication.

OMG-10088: Keep track of the persistent state of a package variable or package cursor. This means that you need to understand when you need to initialize the variable or cursor, and when it needs re-initialization.
Rationale It is important to know whenever a package variable or cursor needs to be (re)initialized. The value of a package variable or cursor persists across transactions (until a DBSM_SESSION.reset_package is performed), but not between sessions, either for the same or for different users.

OMS-52805: Store procedures and functions in packages whenever possible.
The criteria for grouping program units together in a package is:
  • They (potentially) share subprogram units, data structures, etc. These objects have to be declared in the package body only (information hiding).
  • They implement general functionality, related to a specific object.
  • Together, they implement a specific functionality or feature.
Rationale Grouping procedures and functions into packages makes it easier to group program units into logical units, and thereby makes it easier to locate the code. Also it is possible to modify a package's definition without causing Oracle to compile calling procedures or functions.
Finally the number of independent objects to manage/administer will be lower.

OMS-52810: Always manually recompile stored procedures or functions when remote referenced objects have been changed. As there is no automatic mechanism for this, you must keep this as a part of your manual programming procedures.
Rationale This is required to ensure proper working of the stored procedures and functions. When a remote referenced object is changed, this does not make the dependent objects invalid.

OMS-52816: Upon calling a package construct, qualify its name with the name of the package.
Rationale This is always required outside the package. When using the package name also within the package (where it is not strictly required) you make the code easier to amend. You can move a program unit without having to change the calls from one package to another whenever required.

OMS-52830: Never declare a package variable in the package specification. Instead, declare the variable in the package body.
Provide a GET function and SET procedure for packaged variables when the variable needs to be accessible from outside the package.
Rationale The primary reason for this standard is information hiding. The underlying data structure (a packaged variable, global, record group or Oracle table) can now be modified without affecting all the code that relies on the value returned.
It also allows control (in the SET procedure) over who is allowed to change the value of a variable and what values and value transitions are allowed.

Package Body
OMG-10082: Package bodies frequently contain a large number of program units. If this is the case, you might consider defining the program units in alphabetical order. If you use overloaded program units, these will be located together. Try to put those with the most frequent usage first. Consider including a comment above each of the overloaded program units, that they are overloaded. If the program units are local to the package body, this probably means you have to use forward declarations.
Rationale This makes it easier to locate the program units.

OMS-52831: Always include the name of the program unit in the end statement.
Rationale This helps finding your way around in the package body.

OMS-61632: Declare the private sections in a package body in the following order:
private types, private constants, private variables, private cursors, private program units. Place the forward declarations prior to the private program units.
Use the following layout:

create or replace
package body my_package
is
/*********************************************************************************
Created by......Allan Turing, Thinking Machines Inc.

(...)

*********************************************************************************/

--
-- private types
--

--
-- private constants
--

--
-- private variables
--

--
-- private cursors
--

--
-- forward declarations
--

--
-- private program units
--

procedure do_something
(...)
end do_something;

end my_package;
Rationale This gives a common and orderly layout which ultimately makes the code easier to read.

OMS-61633: Comment each packaged private program unit in the package body. Each program unit should be documented with its purpose, usage and possible remarks. Use the following layout with double hyphens:


create or replace
package body my_package
is
/*********************************************************************************
Created by......Allan Turing, Thinking Machines Inc.

(...)

*********************************************************************************/

--
-- private types
--

--
-- private constants
--

--
-- private variables
--

--
-- private cursors
--

--
-- forward declarations
--

--
-- private program units
--

procedure do_something
--
-- Purpose..A description of the purpose of this private program unit, just
--..........like the description of the purpose of public program units in
--..........the package specification
--
-- Usage....-
--
-- Remarks..Any remarks, including known limitations and enhancements, for this
--..........program unit.
--
is
begin
...null;
end do_something;

end my_package;
/

Note: The dots indicate the number of spaces required.
Rationale This makes it easier to understand what the procedure is about. Documenting it in the package body is natural as the procedure is private to the body.

Package Specification
OMS-61634: Declare the public sections in a package header in the following order:
public types, public constants, public variables, public cursors, public program units.
Use the following layout:


create or replace
package my_package
is
/*******************************************************************************
Purpose..Package for file IO.
.........A package level description of the purpose of the package.

Usage....Explaining the usage of the package

Remarks..Package level remarks.
********************************************************************************/

--
-- public types
--

--
-- public constants
--


--
-- public cursors
--

--
-- public program units
--

procedure open_file
(.........p_file_name in varchar2...-- Name of file (including path, e.g.
....................................-- 'c:\work\test.txt').
);
(...)

end my_package;


Note: The dots indicate the number of spaces required.
Rationale This gives a common and orderly layout which ultimately makes the code easier to read.

OMS-61635: Comment each packaged public program unit in the package specification. Each program unit should be documented with its purpose, usage and possible remarks. Use the following layout with double hyphens. Each parameter must be listed and described; any requirements/constraints on the values that may be passed in for the parameter must be indicated. Any specific security or performance issues that apply to the program unit should also be given (for example, the procedure may take extremely long to perform or it contains some additional privileges). Post-conditions (changes brought about by the procedure) may be outlined if they are not clear from the purpose of the program unit.

create or replace
package my_package
is
/*******************************************************************************
Purpose..Package for file IO.
.........A package level description of the purpose of the package.

Usage....Explaining the usage of the package

Remarks..Package level remarks.
********************************************************************************/

--
-- public types
--

--
-- public constants
--

--
-- public variables
--

--
-- public cursors
--

--
-- public program units
--

procedure open_file
(.........p_file_name in varchar2...-- Name of file (including path, e.g.
....................................-- 'c:\work\test.txt').
);
-- Purpose..Opens file p_file_name. When the file does not exist the file is
--..........created. When the file exists, the file is opened and the position
--..........is set to end of the file. A program unit level description of the
--..........purpose of the program unit.
--
-- Usage....p_file_name must be a valid path and file name. The usage of the
--..........program unit.
--
-- Remarks..Any remarks, including known limitations and enhancements, for this
--..........program unit.

end my_package;
Rationale This makes it easier to understand what the procedure is about and how it should be invoked.

Application Systems
OMG-10009: Remove all superfluous objects and code from the repository before publishing the Application System. Especially the final version of the Application System should not contain such objects or code.
Rationale Superfluous objects and code obstruct an effective QA or a reliable impact analysis. Mind that the need for impact analysis on the results of the analysis normally starts during the Design & Build phase. Impact analysis on results of the Design & Build phase starts with bug fixing or even sooner.
Example
  • no test tables/modules
  • there should be no domains that are not connected to an attribute or a column
  • there should be no sequences that are not connected to a column
  • there should be no PL/SQL procedures, functions or packages that are not called from a form, report, trigger, or utility

OMS-61569: Do not use the Form Generator preference Text item default date format (TXTDDF) nor the Form Generator preference Text item default DATETIME format (TXTDTF). Make these preferences empty (override the default values).
Rationale This makes it possible to alter the date or datetime format without the need to generate the forms again, and to set the date(time) format dynamically, by using date related variables.
Use the following runtime registry settings for Forms:
  • forms60_output_date_format
  • forms60_user_date_format
  • forms60_output_datetime_format
  • forms60_user_datetime_format
  • nls_date_format
If a forms date item is generated without a format, at runtime it uses the format setting forms60_output_date_format or forms60_user_date_format. If these are not set, it uses nls_date_format.
Because of this dynamic setup, changing the date format is simply changing the registry variable.

Form Generator: You can find these preferences at Application level, under Layout - Text item. You can override them in two ways:
  • specifying an empty value at Application level
  • specifying an empty value in a Preference Set, and linking that Preference Set at Application level
Warning: This only works if you also comply to OMS-61627.
Application Systems - Name
OMS-61605: Include an application code of 3 alphanumeric characters in the application name, in one of the following ways:

1. make the application name equal to the application code

2. append the application code between brackets at the end

3. start with the application code and extend it with a version number

4. start with the application code and extend it with an underscore followed by a logical name
Rationale The application code is used frequently in naming conventions for objects in the application system. Because names always have a limited length, it is useful to have a short code. As a lot of those objects are created in the database, it is necessary to use alphanumeric characters only.
The application code is also proposed as a prefix for message codes (see OMG-10040). Having a fixed length of 3, makes it easy to automatically check the naming conventions and makes the message codes consistent with the format used by the Oracle tools.
The three possible formats are in such a way that the application code can be pulled out of the application name programmatically, for use in utilities.
Example Method 1: HSD
Method 2: HEADSTART(HSD) or HSDEMO (HSD)
Method 3: HSD60
Method 4: HSD_TEST

Application Systems - Notes
OMS-61517: Record the deletion of primary objects such as entities, modules, etc. as revision history in the Notes of the Application System. Add new entries on top.
Rationale Analysts or developers might want to know what happened to the object. If this kind of information is not recorded, the person that can explain, might not be available on the project, if at all.
Putting new entries on top gives quick insight into the latest change.
Example REVISION HISTORY
Label Date Name Description
1.1 12-11-1999 S. Muller Removed function 31
and module ASP0050F
1.0 06-07-1998 L. Jellema Removed entity STATUS

1.1
----
Removed function 31 and module ASP0050F because their functionality
was dropped as result of tighter planning.

1.0
----
Removed Entity STATUS, functionality now in domain STATUS

Domains
OMG-10024: Do not use an enumerated domain if it is likely that the values of the domain are subject to change. Create an entity in that case (a so-called 'reference entity').
Rationale Domains are harder to maintain compared to the values stored in a separate table. You can only prevent maintenance by a developer when the domain has the Dynamic List flag set and a maintenance form on the REF_CODES table exists. It is impossible to create a foreign key relationship between a column that references a domain and the REF_CODES table. So special measures must be taken to prevent the deletion of domain values that are referenced. Also ordering the values of a domain is not possible because the display sequence as entered in Designer, is ignored (see also OMG-10005).

OMS-30801: Do not use values in a subdomain that conflict with the high/low range, format or length of its superset.
Rationale When a domain has been linked to a master domain, it means that the subdomain is a subset of its master. Therefore the domain values should not conflict with the master.

Domains - Name
OMG-10021: Consider suffixing the name of a domain without allowable values with 'FMT'.
Rationale In this way, you can distinguish the domains with and those without allowable values at a glance. In addition, it facilitates automatic checking for format domains that accidentally have allowable values or enumerated or range domains without allowable values.
Example ID FMT
CITY NAME FMT

OMS-60003: Domains created for a specific attribute should be named using the following convention:
[attribute name] [entity name]
Rationale By using the attribute and entity name it is obvious what the domain is for.
By including the attribute first, it is easier to spot a possibility to reuse a domain for another attribute (in which case it should be renamed).
Example CODE COUNTRY, NUMBER EMPLOYEE

OMS-61524: If using hierarchies for domains, then use the following naming convention for the sub domains:
[parent domain name] [logical name]
Rationale This makes it easier to see which domains are the parent and sub domains when selecting a domain for either an attribute or a column.
Example
Domain Description
UNBOUND TYPE Classification of unbound items (parent domain)
UNBOUND TYPE OF Subset of UNBOUND_TYPE specific to Oracle Forms
UNBOUND TYPE OG Subset of UNBOUND_TYPE specific to Oracle Graphics
UNBOUND TYPE VB Subset of UNBOUND_TYPE specific to Visual Basic
UNBOUND TYPE WEB Subset of UNBOUND_TYPE specific to Oracle WebServer
UNBOUND TYPE C++ Subset of UNBOUND_TYPE specific to C++


Domains - Unit Of Measure
OMS-30780: For domains use units for numeric attributes only.
Rationale Units of measure for non numeric attributes do not make any sense.

Domains - Derivation
OMS-30799: Do not use the Derivation property. Instead, use the function object for modeling the derivation rule.
Rationale CDM uses functions to define rules. See chapter Business Rule Modeling in the CDM Standards and Guidelines Library volume 1.

Domains - Default
OMS-30790: Do not use default values that are in conflict with domain specifications.
Rationale This would be incorrect as the value would not fit within the domain specification.

Domains - Description
OMG-10022: Consider to explicitly record exceptions to the following rule in the comment property of a domain using (ABB) or (VAL). For a domain that has all the allowable values with a meaning, set the Show Meaning property for pop-lists, combo boxes or text lists to 'Meaning'.
For a domain that has allowable values with an abbreviation instead of a meaning, the Show Meaning property should be 'Abbreviation Only'. In all other cases the values should be shown.
Rationale In this way, the end user is provided with as much information as possible while it facilitates functionality that automatically sets the 'Show Meaning' property for bound items.
Example 'status of the issue(ABB)' for a domain with the following allowable values:
value abbreviation meaning
1 N New
2 R Rejected
3 C Canceled
4 A Accepted
5 F Closed

would mean that the abbreviation should be used for the pop list.

Domains - Notes
OMS-61608: Revision History of a domain (including its subobjects like allowable values) must be recorded in the Notes, stating the date of creation, the date and the reason for the change, and the change itself.
Add new entries on the top.
Rationale Keeping track of what is changed can explain a lot of what happened during the Requirements Definition and Development. Questions such as "Why was this changed and who did it?" are easily answered. It is advised to also record new entries when the deliverable is offered to the client for approval.
Putting new entries on top gives quick insight into the latest change.
Example REVISION HISTORY
Label Date Name Description
1.1 06-01-1999 L. Jellema Removed allowable value P
1.0 06-07-1998 L. Jellema Initial Creation


1.1
----
Allowable value P (Processed) not needed anymore because of
enhancement request 34.

1.0
----
Initial Creation

Allowable Values
OMS-30802: Do not use overlapping high/low ranges for domain values.
Rationale When overlapping values are used, it is not possible to uniquely identify which domain value should be used in every situation.

Allowable Values - Value
OMS-61529: For domains with Column Datatype of VARCHAR2, VARCHAR or CHAR, the Value property of its allowable values must be in uppercase.
Rationale This prevents confusion, for example, when programming or writing SQL-statements.

Allowable Values - High Value
OMS-30806: Do not use a high value that is larger than the length and precision of the domain specification.
Rationale This would be incorrect as the value would not fit within the domain specification.
Example When the domain length is 6, do not specify a high value of 10.000.000.

Allowable Values - Abbreviation
OMS-30810: An abbreviation must be unique within the domain.
Rationale When the same abbreviation is used for different allowable values of a domain, it is not possible to determine the correct value based on the abbreviation.

Allowable Values - Meaning
OMG-10023: The first letter of any word in a prompt is capitalized, except for prepositions and other small words, such as and and or.
Rationale An uppercase meaning is less readable and consumes more space when used on screens or in reports.
Example New, Rejected, Canceled, Accepted, Closed

OMS-30815: When the allowable value indicates a range (that is when both the Low value and the High value are specified) and the domain also has one or more other allowable values, the range must be provided with a meaning.
Rationale This increases the understanding of the domain and its allowable values. For domains with only one allowable value, the meaning should be clear from the domain description.

OMS-61629: When the allowable value indicates an enumerated value (that is when the High value is empty), the value must be provided with a meaning.
Rationale Usually, the meaning is what is displayed in the GUI item such as pop list, check box, etc. You should be able to assume that a meaning is always given and not have to check each time you use a GUI item.

Storage Definitions - Storage Label
OMS-61592: Use the following naming convention for the Storage Label:
[application code]_[tablespace name]_STOR
Rationale The storage is the default for a tablespace. This naming convention makes it immediately visible for which tablespace.

Storage Definitions - Initial Extent
OMS-61593: Choose one of the following initial extent sizes:
160K
5120K
160M
Rationale This is following the SAFE Standards. This is to avoid free space fragmentation.

Storage Definitions - Min Extents
OMS-42663: Always specify 1 for the minimum number of extents to be allocated, with the exception for Rollback Segments. Use 10 for Rollback Segments.
Rationale This is following the SAFE Standards. This is to avoid free space fragmentation.

Storage Definitions - Max Extents
OMS-61594: Do not enter Max Extents for Storages.
Rationale This prevents errors due to the fact that the maximum number of extents has been reached.

Storage Definitions - Percentage Increase
OMS-42665: Always specify a 0 for the percentage by which the following extent will grow over the last extent allocated.
Rationale The use of Percentage Increase causes the Oracle Server to allocate extents of different sizes in the same tablespace, causing fragmentation.

Storage Definitions - Comment
OMS-42666: Enter a short sentence indicating the use of this storage definition.
Rationale This makes obvious what is the use of the storage definition.

Business Functions
OMS-31000: Use one top function for the full hierarchical function structure of the Application System and another top function for business rules.
Rationale Using different hierarchies makes it easier to separate business rules from the other business functions.

OMS-31002: All functions on the same level of decomposition must have the same level of abstraction, to the extent possible.
Rationale When the same method for decomposing the functions has been used, then this is normally the result. In this way, the tree as a whole is easier to read and understand.

OMS-31003: Functions should have no overlapping functionality that is not covered by an explicit common function.
Rationale It is confusing when the same functionality is included in more than a single function. Also it involves multiple descriptions of the same functionality with the risk of inconsistency.

OMS-31004: Decomposed functions must always be fully covered by the functions on the next lower level.
Rationale Functionality of a parent function that is not included in any of the child functions indicates an incomplete decomposition of the functionality of the master.

OMS-31006: The function hierarchy should be well balanced; that is, all top-level functions should be decomposed to approximately the same number of levels.
Rationale When the same method for decomposing the functions has been used, then this is normally the result. In this way, the tree as a whole is easier to read and understand.

OMS-31007: In the presentation function hierarchy, do not use more than ten subordinate functions per parent function; aim at six.
Rationale This makes the tree easier to read and understand. For the business rule hierarchy it might be necessary to have more subordinate functions, because there might be a lot of rules in the same category. The categorization takes precedence over readability for business rules.

OMS-60007: Always use explicit events to document the triggering of the following classes of business rules:
  • restricted relationship rules
  • other entity rules
  • modify rules
  • change event rules
Rationale For the mentioned rule classes, using events to model the WHEN conditions of business rules, provides structured, valuable support for the system architect, enabling him or her to oversee all situations where the rule must be enforced.
For the other classes of business rules, the triggering events are more or less trivial and can be omitted, although it does not hurt to model them anyway.

OMS-61637: If a business rule function is triggered by events in more than one entity, it must be decomposed. The decomposed business rule functions must each have triggering events in only one entity.
Rationale A business rule design definition can only be triggered by one table, and the Business Rule Transformer (a Headstart Utility) needs to have one business rule function for each business rule design definition.
Also, it is a good opportunity to think about way the parent rule has to be implemented, because an inter-entity rule might decompose into a change event rule, a delete rule and an inter-entity rule on one entity.
Example
Label Short Definition
Parent BR_ORD001_IER An order must have at least one order line
Child 1 BR_ORD001A_IER An order must have at least one order line
Child 2 BR_ORD001B_DEL Do not delete the last order line of an order, unless you also delete the order itself
Parent BR_PAS002_IER The project assignment start date must be greater than or equal to the project's start date
Child 1 BR_PAS002A_IER The project assignment start date must be greater than or equal to the project's start date
Child 2 BR_PAS002B_IER The project's start date must be less than or equal to all of its project assignment start dates

Note: If one of the decomposed functions is triggered by another than the 'main' entity, it should be made into a common function. See OMS-61636.
Business Functions - Label
OMS-31020: Initially, use a sequence number or any other simple system as a generator for unique function labels. Defer labeling functions until you are quite sure that the hierarchical structure will not radically change any further. Then switch to a hierarchical coding system for unique function labels. Use a three-character prefix for the entire system, in addition to a one-character prefix for main branches, representing subsystem functionality.
Rationale This avoids the need of changing the labels every time a new function is discovered, removed or moved. When it has become stable, and labels are provided following a hierarchical system, it is easy to quickly determine where the function belongs by viewing only its label.
Example ACCP402 (meaning: system ACC, main branch P, function 4.0.2)

Application Design Transformer: Oracle Designer's Application Design Transformer does not reuse function labels for modules.
OMS-60005: Functions used to record business rules should be labeled as:

BR_[entity short name]###([decomposition id])_[type abbreviation]

where
  • the entity is the 'main' entity triggering the rule,
  • decomposition id is A or B or C etc.,
  • ### is a 3-digit sequence number making the rule unique within the entity, regardless of the decomposition ID or rule type,
  • the rule type abbreviations that apply are as follows:
ATT Other Attribute Rules
TPL Tuple Rules
ENT Other Entity Rules
RER Restricted Entity Relationship Rules
IER Other Inter-Entity Rules
CRE Create Rules
TRS Attribute Transition Rules
UPD Other Update Rules
MOD Modify Rules
DEL Other Delete Rules
DFT Complex Default Rules
CEV Other Change Event Rules
CEW Change Event Rules without DML
AUT Authorization Rules
Rationale The BR_ prefix makes business rules easy to discern from other functions, and also when the label is referenced in program code that implements the rule, it is easy to see that the code concerns a business rule.
The entity short name groups business rules by their (main) entity, which is useful because those rules often concern the same kinds of things and often have to be implemented at the same time.
The sequence number makes the rule unique regardless of the decomposition ID or rule type, so that when the rule is decomposed, the resulting rule functions can bear the same entity short name and sequence number, but a different decomposition ID and possibly a different rule type.
The rule types are according to the CDM business rule classification.
Example BR_EMP023_IER, meaning Employee rule 23, classified as Inter Entity rule.
This rule might be decomposed into BR_EMP023A_CEV and BR_EMP023B_IER (see also OMS-61636).

OMS-61621: Start the labels of all functions in the business rule hierarchy with BR_ or use the label BR itself.
Rationale The BR_ prefix makes the business rule hierarchy functions easy to discern from other functions.
Example
Label Short Definition
BR Top of the business rule hierarchy
BR_HR Branch for the business rules of the business area Human Resources
BR_FIN Branch for the business rules of the business area Financials
BR_EMP Branch for the business rules related to the Employee entity
BR_DEP Branch for the business rules related to the Department entity
BR_PRJ Branch for the business rules related to the Project entity

OMS-61638: Decomposed business rule functions must have a label with the same entity short name and number as their parent function. They also must include a decomposition id.
Rationale This way it is obvious that they are decomposed, to which parent they belong, and of which rule type they are.
Example
Label Short Definition
Parent BR_ORD001_IER An order must have at least one order line
Child 1 BR_ORD001A_IER An order must have at least one order line
Child 2 BR_ORD001B_DEL Do not delete the last order line of an order, unless you also delete the order itself
Parent BR_PAS002_IER The project assignment start date must be greater than or equal to the project's start date
Child 1 BR_PAS002A_IER The project assignment start date must be greater than or equal to the project's start date
Child 2 BR_PAS002B_IER The project's start date must be less than or equal to all of its project assignment start dates

Note: The decomposed business functions can have (and often do have) a different rule type and a different description. Also, if one of the decomposed functions is triggered by another than the 'main' entity, it should be made into a common function. See OMS-61636.
Business Functions - Short Definition
OMG-10074: Avoid underscores, punctuation marks, and symbols in the function definition. Use active language.
Rationale This makes it easier to read and understand.

OMS-31030: Every function must be written in a clear phrase, local to the business, avoiding ambiguity as much as possible.
Rationale This keeps the technical descriptions separate from the notes of a more functional nature. Also it allows the teams to include technical description at any time during the project.

OMS-31031: Functions should not specify the "how", but the "what" of the business. Use the current "how" of the function explicitly as an example in the function description.
Rationale It has in many situations not yet been decided how the function should be performed. Describing how something should be done, distracts the focus from what actually should be achieved by the function.
Example
Do not use Use
Fill in form A6 Register import request
File in brown binder Archive copy of invoice
Call Communicate
Throw away Delete
Fax order confirmation Confirm order


OMS-31032: Business function definitions that do not record a business rule, should have the main structure
[verb][adjective][noun]
or, depending on the language in use
[adjective][noun][verb] or some other order.
Rationale All functions other than business rule functions can be expressed this way, and this makes it easier to read and understand.
Example Maintain passed examinations
Assign Applicant to time slot

OMS-61623: For business rule functions, describe the rule in the Short Definition. If the Short Definition is not long enough, also use the Description.
Rationale This makes the rule easy to enter and because the short definition shows in the Navigator, it also makes the rule easier to find.

Business Functions - Master Function
OMS-31100: Use a reference to a master function only if the functions are identical. You may refer to a function within another Application System. You may refer to a function in another version of the Application System, although the latter is strongly discouraged.
Rationale The definition of a common function is that it is identical to the master. Also, you can not make any changes to the function when it has been defined as a common function to a master. Only the master function can be maintained.

Attention: The master and slave functions are also known as common functions. The slave function is a copy of the master and cannot be maintained. The function definition field for the slave is automatically erased to emphasize the slave role.
OMS-61636: If you organize your business rule hierarchy by entity, and you have a decomposed business rule function that is triggered by a different entity than its parent, you have to do the following. Create another business rule function under the triggering entity branch, with the same description and with the short name of the triggering entity in its label. Make the decomposed business rule function a 'common' function of this new business rule function. Both these rules must have the same rule type in the label.
Rationale If you organize your business rule hierarchy by entity, you must record all business rules related to an entity under that entity branch. It allows you to clearly document the work associated with each entity, easily do completeness checks and communicate to the end user all rules they can run into in relation to a certain entity.

The decomposed function is linked to the new rule function as a 'common' function to document that these two rules are really the same rule. The Business Rule Transformer (a Headstart Utility) ignores common functions so only the master function will get transformed into a business rule design definition. Following this standard enables the Business Rule Transformer to correctly name the rule when it creates the Design Definition. In other words, the business rule design definition will be named after the table in whose CAPI it will be implemented.
Example
Label Short Definition
Parent BR_ORD001_IER An order must have at least one order line
Child 1 BR_ORD001A_IER An order must have at least one order line
Child 2, common to BR_ORL002_DEL BR_ORD001B_DEL Do not delete the last order line of an order, unless you also delete the order itself
Master of BR_ORD001B_DEL BR_ORL002_DEL Do not delete the last order line of an order, unless you also delete the order itself
Parent BR_PAS002_IER The project assignment start date must be greater than or equal to the project's start date
Child 1 BR_PAS002A_IER The project assignment start date must be greater than or equal to the project's start date
Child 2, common to BR_PRJ003_IER BR_PAS002B_IER The project's start date must be less than or equal to all of its project assignment start dates
Master of BR_PAS002B_IER BR_PRJ003_IER The project's start date must be less than or equal to all of its project assignment start dates

Business Functions - Elementary ?
OMS-31001: Functions must be decomposed to elementary level.
Rationale The elementary level of a function indicates a working unit that once started, must either
complete successfully, or, if it cannot complete successfully, must undo any effects that it has had up to the point of failure. This level of detail is required for further design.

Application Design Transformer: The Application Design Transformer only transforms functions that are marked as elementary into Modules.
OMS-31035: Set check box Elementary to checked, only if the function, when executed, always fully succeeds or, if not successful, fully removes its trails.
Rationale By doing this you comply to the definition of elementary functions.

Attention: Elementary functions may be, but rarely are, decomposed.
OMS-61607: Elementary functions can only be decomposed into non-elementary functions.
Rationale The elementary level of a function indicates a working unit that once started, must either complete successfully, or, if it cannot complete successfully, must undo any effects that it has had up to the point of failure.
If this is valid for the parent function, it cannot also be valid for one of its children.

Business Functions - Frequency
OMS-31110: Use Frequency and Unit only if there is a regular basis for it.
Rationale By specifying the Frequency and Unit, it is expected that the function will occur the number specified in Frequency per every unit. For example 2 times every MONTH. Therefore, if there is no regular basis for the function to occur, the information provided in Frequency and Unit will be incorrect, and thereby only confusing.

OMS-31112: Specify frequency using the smallest possible unit that forms a regular basis.
Rationale By using the smallest unit the regularity of the occurrences is most exactly described.
Example Use 800/MONTH rather than 10,000/YEAR if the function is likely to happen every now and then. Use 10,000 if a peak is likely. Also describe the peak in the Function Description, paragraph description.

Business Functions - Frequency Unit
OMS-31116: If a function always happens on the last day of a month, then enter MONTH and create a time event that triggers this function (see also OMS-31071).
Rationale In this situation, it is the fact (the event) that it is a certain day of the month that triggers the function to occur. Therefore modeling the actual event provides more exact information than only specifying that the function runs with a frequency of once per month.

Business Functions - Description
OMS-31120: Use different templates for different types of functions.

For a function with immediate response needed, provide fixed sections for each of the following topics:

  • goal
  • description
  • example of typical use
  • miscellaneous

    If you already know the function will be implemented as a report, add the following sections:

  • input parameters
  • selection criteria
  • ordering

    For a function with no immediate response needed, provide fixed sections for the following topics:

  • goal
  • input parameters
  • output parameters
  • processing
  • miscellaneous

Business rules recorded as functions only consist of a description of the rule, and then only if there is not enough space in the Short Definition property to describe the rule.
Rationale Using templates such as this will trigger the team to obtain the required information for further design, and at a later stage makes it easier to find the information quickly.
Example Example Immediate Response Function :

GOAL
Maintain elementary stages in the history of a [PATIENT].

DESCRIPTION
This function enables the user to search for an existing [PATIENT], then allows the user to enter a new [PATIENT.status] (period) for the [PATIENT], the [PATIENT.date of change] and a remark on the [PATIENT.reason of change].

EXAMPLE OF TYPICAL USE
This function is used after the admission of a [PATIENT], when the R (Registered) status that was automatically set during the registration must be changed. The function is also used when the patient is discharged or has been moved to another hospital.

MISCELLANEOUS


Example Report :

GOAL
Produce a list of [EMPLOYEE.telephone number]s of [EMPLOYEE]s working for a specific [DEPARTMENT].

DESCRIPTION
Per [DEPARTMENT] ([DEPARTMENT.number], [DEPARTMENT.name])all current employees ([EMPLOYEE.name], telephone number) are listed.

EXAMPLE OF TYPICAL USE
The list is used to find telephone numbers.

INPUT PARAMETERS
- department number (optional)

SELECTION CRITERIA
Only current employees ([EMPLOYEE.enddate] is null or employee.enddate > sysdate) are selected

ORDERING
department.name, employee.name

CALL METHOD
online, locally

MISCELLANEOUS
The report contents can be hard-coded, no dynamic formatting has to be done. There should be sufficient space to print the default company header with logo.

Business Functions - Notes
OMS-31130: Enter technical notes that are important for the persons involved in the next stages in the system development process.
Rationale In some cases, the team has information that facilitates the next stage in the system development that otherwise might get lost. Recording it in the notes prevents this.
Example DESIGN HINTS
Make use of tab sheets if necessary. Make sure the name and address of the employee are always visible.
This function will be used very often. Therefore for this function it is important that business rules are validated at client level as much as possible. Try to use a call to a stored function that validates the rule.

OMS-31131: Do not enter business rules in the Notes of Business Functions.
Rationale Business rules should be expressed as separate functions. See chapter Business Rule Modeling in the CDM Standards and Guidelines Library volume 1.

OMS-31133: Revision History of a business function (including its subobjects such as entity and attribute usages) must be recorded in the Notes, stating the date of creation, the date and the reason for the change, and the change itself.
Add new entries on the top.
Rationale Keeping track of what is changed can explain a lot of what happened during the Requirements Definition phase. Questions such as "Why was this changed and who did it?" are easily answered. It is advised to also record new entries when the deliverable is offered to the client for approval.
Putting new entries on top gives quick insight into the latest change.
Example REVISION HISTORY
Label Date Name Description
1.1 06-01-1999 L. Jellema Removed Invoice functionality
1.0 06-07-1998 L. Jellema Initial Creation


1.1
----
The invoice functionality was removed because it is now covered by function XXX. Also the entity usage of INVOICE was removed.

1.0
----
Initial Creation

Function Entity Usages
OMG-10018: Do not record entity usages that are not visible to the end user, except when it concerns a business rule function.
Rationale Recording non-visible entity usages makes it harder for the end user to validate the specifications.

Function Entity Usages - Entity
OMS-31140: In case of business functions (that is, not business rules) make at least an entry for every entity that is basic for the function. That is, in more technical terms, for every entity that would lead to a module component (block) in a screen module or a group in a report. Also enter the usage of an entity when a non-primary key attribute of that entity must be displayed or otherwise used.
Rationale Facilitating the design of the module, relevant entity usages should be recorded. Doing this by entity usages, is the most consistent way to record it. It also facilitates the validation of the specifications as well as impact analysis.

OMS-31141: If an entity is used twice (this can happen, for example, if the entity plays several roles), then only one entry can be made. Enter the union of the individual usages. Create a comment on this particular usage.
Rationale In this way, the multiple usages are properly documented.

OMS-31143: If a certain function creates or changes a relationship, enter the entity of the many side of the relationship with an R (Relationship) in the Common field for this particular usage.
If the function has no other usages for that entity, do not specify any CRUD (Create/Read/Update/Delete) usages.
Rationale A relationship usage can not be defined properly because Oracle Designer does not support Function-Relationship Usage. In this way, it is documented that the function performs some activity related to that entity, and more specifically that the activity is related to one or more of the relationships for that entity.

Attention: The level of detail of Function-Entity Usage that is stated in OMS-31140 through OMS-31143 is adequate for normal use.
OMS-31145: Do not record entity usages that are implied by the existence of certain relationships:
  • foreign key check (for example, Is this a valid reference?)
  • restricted delete check (for example, Is this entity occurrence referred to?)
Rationale This information is implied in the relationships themselves.

Function Entity Usages - Archive ?
OMS-31151: Functions that have the Archive check box checked should usually have the Delete check box checked as well.
Rationale A function that archives the entries of a specific entity, normally also deletes these same entries.

Function Attribute Usages
OMG-10020: Do not record attribute usages that are not visible to the end user, except when it concerns a business rule function.
Rationale Recording non-visible attribute usages makes it harder for the end user to validate the specifications.

OMG-10025: Come to an agreement with the customer beforehand on what attribute usages will and will not be specified for functions.
Rationale Often the benefit of fully specifying the attribute usages is not large enough compared to the required effort to complete these usages. The added value of specifying attribute usages is limited. It is often more efficient to postpone defining this kind of detailed information until system design, where bound items must be specified for the Oracle Generators for Forms and Reports.

OMS-31161: Be consistent in specifying usages. If you specify attribute usages for base entities only, you should do it for all base entities and for all attributes of those entities. If you specify attribute usages for lookup entities, you should do it for all lookup entities.
Rationale This ensures consistency throughout your model.

Function Attribute Usages - Insert ?
OMS-31166: Check the Insert check box only if Create or Update is allowed at the entity level.
Rationale It makes no sense to check the create flag of attributes if this conflicts with the usage at entity level.

Function Attribute Usages - Update ?
OMS-61527: Check the Update check box only if Update is allowed at the entity level.
Rationale It makes no sense to check the update flag of attributes if this conflicts with the usage at entity level.

Function Attribute Usages - Nullify ?
OMS-61528: Check the Nullify check box only for non-mandatory attributes.
Rationale Mandatory attributes cannot be nullified.

Function Attribute Usages - Other ?
OMG-10019: Consider checking both the 'retrieve' and 'other' usage flags for attributes that are part of the search criteria.
Rationale This is the most consistent way to record what attributes should be 'queryable', and to distinguish them from attributes that are retrieved but not queryable. It facilitates functionality that automatically sets the query flag of the corresponding bound items for the corresponding module.

Process Events
OMS-31050: Create trigger and outcome events only if the events add substantially to the user's or developer's understanding of the business processes supported by the system.
Rationale Adding events that do not provide any clarification make the process models unnecessarily complex to read.

Process Events - Name
OMS-31051: Use self explanatory, descriptive, preferably short event names for triggering and outcome events used in processes.
Rationale Using good event names makes the processes more readable in the process modeler, as the name is displayed in the events.
Example ORDER RECEIVED
CONTACT DECEASED
INVOICES PRINTED
CANDIDATE WITHDRAWN

Attention: If you record a function-trigger-function entry, Oracle Designer automatically creates a system event, named similar to END-ACCP402, where ACCP402 is the function label of the Triggered By function. You can change this name. When the function label changes the event name does not change with it.
OMS-61624: For Events of Event Type Change, use the naming convention
[entity usage type] [entity name](.[attribute or relationship description])
where
  • the entity usage type is CREATE, UPDATE or DELETE
  • the entity usage type must be UPDATE if the event concerns an attribute or relationship
  • the entity name must be equal to the name in the Entity property of the Event
  • the attribute or relationship description consists of either [relationship name]->[other entity name], or [attribute name]
  • if the attribute name is used, it must be equal to the name in the Attribute property of the Event
  • if the resulting Event name would be longer than 40 characters, only the first 39 characters are used followed by a sequence number
  • if a relationship description was abbreviated in the event name, then the full relationship description must be entered in the Event Description property
Rationale This way the Events that refer to Entities are named in a structured and clear way.
The dot between Entity name and Attribute or Relationship makes a clear distinction between those components, because the Entity, Attribute and Relationship names should not contain any dots.
The Relationship name is followed by ->[other entity name] because the Relationship name in itself needs not be unique within the entity, and the -> symbol makes it clear that it is a link towards something else.
The abbreviation to 39 characters followed by a sequence number is necessary to make the name unique when there are (or will be) more event names that have the same first 39 characters, while staying within the maximum event name length of 40.
If the relationship description must be abbreviated because of the length, putting the full description in the Event Description is necessary to be able to exactly identify the relationship, because there might be (now or in the future) other relationships which would result in the same abbreviation.
Example DELETE EMPLOYEE
UPDATE EMPLOYEE.SALARY
UPDATE PROJECT ALLOCATION.MADE BY->EMPL1 (with Description: made by->EMPLOYEE)

Attention: If you also comply to OMS-61622 (On Condition) and OMS-31076/OMS-61619 (Attribute Reference), the Event Name is a concatenation of the values in the properties On Condition, Entity and Attribute.
Process Events - Type
OMS-31056: Use Other only if Change, System, Time, Internal or External is not appropriate, or when the type is not yet known.
Rationale When entering an event, the default is Other. By changing the default to the correct type of the event, more exact information is provided about the event.

Attention: The categories Internal and External can not be chosen directly in the Process Modeler, but only in the Repository Object Navigator.
OMS-61626: For events triggering business rule functions, the type must always be Change.
Rationale Business rules are always triggered by certain actions on entities, attributes and/or relationships. If the function is triggered by something else, it is a different kind of function (for example, a batch function).

Process Events - On Condition
OMS-61622: For Events of Event Type Change, record in the On Condition property the Entity usage type: CREATE, UPDATE or DELETE.
Rationale This information is important to facilitate generation of business rule implementations.

Note: Each event should only represent one usage type.
Process Events - Date
OMS-31060: For time events, specify the exact date, if known.
Rationale By specifying the exact date, more information about the event in itself is provided.

OMS-31061: Do not specify multiple dates (by creating multiple events) if these can be expressed by a frequency/unit combination.
Rationale By specifying more events, you unnecessarily provide duplicate information in the repository, while in fact it is the same event repeating itself.

Attention: Only fully specified dates are accepted here. The date format is dependent on the NLS_DATE_FORMAT setting.
Process Events - Time
OMS-31062: For time events, specify the exact time (HH24:MI), if known.
Rationale By specifying the exact time, more information about the event in itself is provided.

OMS-31063: Specify a daily recurring event, as a time without a specific date, when the time on which the event occurs is known.
Rationale By specifying the exact time, more information is provided, than by only specifying once per day (Frequency 1, Frequency Unit Day).

Process Events - Frequency
OMS-31065: Use Frequency and Unit only if there is a regular basis for the event in the scope of that unit.
Rationale By specifying the Frequency and Unit, it is expected that the event will occur the number specified in Frequency per every unit. For example 2 times every MONTH. Therefore, if there is no regular basis for the event, the information provided in Frequency and Unit is incorrect, and thereby only confusing.

OMS-31066: Use only positive integer values for Frequency.
Rationale A negative value has no meaning. An event never occurs a negative amount of times.

OMS-31071: If the event must indicate a certain day of each month, enter MONTH in the Frequency and specify which day of the month in the event Name.
Rationale In this way, it is clear which time event is meant.
Example If the event should specify the last day of the month, enter frequency MONTH and name LAST DAY OF MONTH.

Process Events - Frequency Unit
OMS-31067: Specify frequency using the smallest possible unit that will form a regular basis.
Rationale By using the smallest unit the regularity of the occurrences is most exact described.
Example Use 800/MONTH rather than 10,000/YEAR if the event is likely to happen every now and then. Use 10,000 if a peak is likely.

Process Events - Attribute
OMS-31076: Enter an attribute name when the event is triggered by a change in an attribute.
Rationale Entering the changing attribute that triggers the event provides more exact information about the event.

OMS-61619: If you enter an Attribute reference, there must also be an Entity reference and the Attribute must exist in that Entity.
Rationale Entering an existing attribute in combination with its entity prevents confusion to which entity the attribute belongs, and thereby what is actually triggering the event.

Process Events - System Description
OMS-31080: Do not use 'System Description', use multi-line text description instead.
Rationale The System Description text is limited. Also by centralizing this kind of information in the description of the event, you avoid duplicating information.

Process Events - Description
OMS-60006: Only use the description if the event name is not self-explanatory.
Rationale Adding a description when the event is self-explanatory is duplicating information. The data must in that situation be maintained in more than one place, with the risk of introducing inconsistencies.

Datastores - Name
OMS-31503: Use self-explanatory, descriptive, preferably short data store names.
Rationale This makes it easier to read and understand them.
Example Mortgages and Assurances
Addresses
Open Invoices

Datastores - Comment
OMS-31505: Do not use the Comment field to list data store contents; merely provide a short global description.
Rationale The data store content is expected in the attributes, or data items of the data store, so entering this kind of information in the Comment increases the risk that this information is overseen at a later stage.

Datastores - Description
OMS-61526: Do not use the Description field to list data store contents; merely provide a global description.
Rationale The data store content is expected in the attributes, or data items of the data store, so entering this kind of information in the Description increases the risk that this information is overseen at a later stage.

Entities
OMG-10005: Consider to provide each 'reference entity' with a sort order attribute, with one standard default value, for example 1.
A 'reference entity' is like a domain, in the sense that its main function is to offer a list of choices for other entities, see also OMG-10024.
Rationale In practice, it might prove to be inconvenient to sort the occurrences of 'reference entities' based on other attribute values. When no sort order attribute is modeled, it is likely that during design no corresponding column will be introduced either.
Example The entity COUNTRY is a typical reference entity, it contains a list of available countries which can be linked to addresses. It could also be modelled as a domain for attributes called COUNTRY.

OMG-10006: When modeling history, consider modeling it in such a way that also the actual data is stored in the same entity.
Rationale When the history of data is used by a business function, the actual data is almost always needed also. When the actual data is recorded in the same entity (table), describing the functionality and implementing it often becomes easier.

OMS-31150: For every entity, there must be at least one business function that allows Create, Retrieve, or Delete (the latter for the sake of completeness).
Rationale In this way the complete life-cycle for an entity is covered.

OMS-33013: If an entity has subentities, its subtypes should form a complete set of mutually exclusive classes (a partition of the supertype).
Rationale Subtypes are used to indicate a specific to the more generic. When subtypes are used, then it should be possible to classify the entries of the generic into one and only one of the specific. Therefore the subtypes need to be mutually exclusive.

OMS-33014: Supertype entities should include only those attributes common to all its subtype entities.
Rationale By entering all common attributes in the supertype redundancy is avoided, as you do not have to enter all these attributes for every subtype.

OMS-61500: Subtypes never come alone. Each supertype entity should always have at least two subtypes.
Rationale When you model subtypes, each occurrence of the supertype should belong to exactly one subtype. Specifying only one subtype for a supertype indicates incorrect modeling.
Example A supertype VEHICLE has been defined with one and only one subtype CAR.
If the situation is such that you only deal with VEHICLEs of type CAR, then you should model CAR only and remove the VEHICLE entity. On the other hand, it might have been modeled like this because it is not always known what kind of VEHICLE it is. In this situation it is obvious that at least one subtype is missing, as it is possible that it is not a car.

OMS-61501: Subtype entities should only represent one classification at a time.
Rationale Using one classification generally simplifies the models. Using more classifications might radically increase the number of required subtypes.
Example If you need to classify PERSON in ADULT and CHILD, and then the ADULT in PARENT or NON-PARENT, then you should not use both classifications at the same time (for example Adult Parent). Instead use subtypes for ADULT and CHILD, and if required again two subtypes for ADULT (PARENT and NON-PARENT). Also keep in mind that in many situations subtypes may be replaced by attribute indicators, or via relationships to other entities. In our example, the PARENT/NON-PARENT property may also be solved by an attribute Parent Indicator, or even better, if you will always have the Child information available, by including a relationship between PARENT and CHILD.

OMS-61514: For ranges like start and end date or time the range should be defined as to include both the start date (time) and the end date (time).
Rationale To make implementing comparisons easier, it is important to use one generic convention. The Oracle between operator takes the begin and end value both into account.
Example Hiredate is the first working day of the employee. Firedate is the last working day of the employee (and not the day after).

Entities - Name
OMS-30530: Entity names must consist of at most three words.
Rationale Since entity names are referred to many times, entity names with a lot of words may make text unreadable.

OMS-30531: Only use the alphanumeric characters, spaces and '-' in entity names.
Rationale The Entity Name and the Entity Plural should resemble each other. When running the Database Design Transformer, the Entity Plural becomes the default for the Table Name.
Names of objects in the Oracle Server can contain only alphanumeric characters from the database character set and the characters _, $, and #. Underscores are superfluous as the Database Design Transformer converts spaces to underscores. The character '-' is transformed to an underscore. Use of $ and # can lead to interpretation conflicts when using certain tools to access the data.
Example PROJECT PLAN instead of PROJ. PLAN or PROJECT_PLAN

OMS-61000: Entity names must be singular.
Rationale There is a separate item for the plural name.

OMS-61001: Do not use prepositions in Entity names.
Rationale Most of the time using prepositions means that your modeling is not right. There are exceptions such as 'Bill of Material'.
Example The entity MOTHER OF CHILD can be resolved dependent of the importance for the business.
If it is not important to record any information about the child, then it is sufficient to call the entity MOTHER.
If some information about the child is needed, then it would be most correct to split the entity into two entities MOTHER and CHILD, with a relationship between them. Also this construction allows you to record information about an unlimited amount of children.
If only minor information about the child is needed, for example the name, birth date and sex of the first born child, then it is sufficient to have an entity MOTHER, including three attributes for the first born child. This is naturally a more limited solution than the previous one, but might be a better solution for the business you are modeling.

Entities - Short Name
OMG-10003: Short names should be highly predictable from the full name, but also logical. An entity short name may be the same as the entity name itself. Make sure that the most frequently used entities get 'the best' short names.
Rationale Entity short names are the default for the aliases of the related tables. The table aliases are used a lot during build and therefore should be highly predictable and logical.

OMS-30534: Entity short names must consist of three alphanumeric characters.
Rationale When running the Database Design Transformer, the entity short name becomes the table alias. This table alias is then used for the generated key constraint names, as well as for the generated default indexes for the table. The table alias is also used in naming conventions for PL/SQL Definitions, Module Components etc.
As the table aliases are used so much, the shorter the better is a good rule. The shortest entity short name that allows for enough possibilities is 3 characters long.
To be able to identify the table alias easily from the names of the above mentioned objects, the characters must be alphanumeric.
Example APPROVAL -> APP

Exception: Large systems may change this standard to 4 characters, if OMS-61530 and OMS-61599 are also changed accordingly.
Note: Do not make an exception for journal entities, as you should model them in a different way (see OMS-61513).
Entities - Plural
OMS-30542: Make the plural no longer than 26 characters.
When the logical plural would be longer, make it shorter by removing every second and following vowels of a word. Start at the last word of the plural moving forward, until the name is short enough.
As an alternative well known abbreviations might be used, for example NO for NUMBER or QTY for QUANTITY. These abbreviations must be recorded in the Business Terminology.
Rationale The plural of an entity name is used as the table name by the Database Design Transformer. Table names may not be longer than 30 characters. The standard is to prefix the table name with the three letters of the application code and an underscore (OMS-42100), so that leaves 26 characters for the entity plural.
Example EMPLOYEE ASSIGNMENT HISTORY becomes
EMPLOYEE ASSIGNMENT HISTR (25 characters long)

Warning: In case of Oracle7 the name must be unique within the first 21 characters, so the entity plural should be unique within the first 17 characters. For Oracle8 this restriction is no longer valid. A structured and consequent abbreviation technique should be used so that abbreviations can be reconstructed without the need to consult the repository every time. Normally the entity plural is not communicated to the end user, in which case there is no issue about the plural not being clear.
Exception: If subtype entities are implemented using a supertype table, the subtype entity plural is not used for the table name, so in that case this standard is not necessary.
OMS-61509: Only use the alphanumeric characters, spaces and '-' in the entity plural.
Rationale When running the Database Design Transformer, the Entity Plural becomes the default for the Table Name.
Names of objects in the Oracle Server can contain only alphanumeric characters from the database character set and the characters _, $, and #. Underscores are superfluous as the Database Design Transformer converts spaces to underscores. The character '-' is transformed to an underscore. Use of $ and # can lead to interpretation conflicts when using certain tools to access the data.

Entities - Average
OMS-61002: Use a fixed time frame (for instance 5 years) as a base for estimating when storage is important. Use the same time frame across all estimates. Record this time frame in the project-specific standards in the Application System Notes.
Rationale If you want to estimate a volume of the entity you should work against a given time frame otherwise the estimate does not make any sense.

Entities - Maximum
OMS-61003: Use a fixed time frame (for instance 5 years) as a base for estimating when storage is important. Use the same time frame across all estimates. Record this time frame in the project-specific standards in the Application System Notes.
Rationale If you want to estimate a volume of the entity you should work against a given time frame otherwise the estimate does not make any sense.

Entities - Notes
OMG-10002: If applicable, record design hints in the notes of the entity.
Rationale In some cases the analyst has information that facilitates the Design phase which information otherwise might become lost. Recording it in the notes prevents this.
Example DESIGN HINTS
The [CUSTOMER.name] will often be used as a search criterion. Therefore it is advised to create an index on the related column.
When showing [CUSTOMER] information, make certain that the address is also shown (for example as lookup items or in lists of values).

Entities - Description
OMS-30550: The description of an entity should have fixed sections for each of the following topics:
  • definition
  • examples
  • default sort order attributes
  • default descriptor attributes
  • design decisions
Use a generic fixed template to record this information. When describing an entity that is used to populate a list of values, record what the descriptive attributes are when these are not the same as the sort order attributes. When a topic is not applicable, record 'N/A' for it or the character '-'. Explicitly record it if the sort order is descending.
Rationale This provides the necessary input for the Design phase when the sort order and descriptor columns need to be determined. Recording design decisions explicitly prevent that one have to think this over again and again. A fixed template reduces the risk that recording certain information is forgotten.
Example DEFINITION
A division of the Oracle corporation.

EXAMPLES
Consulting, Customer Support, Education, Facilities, Human Resources, Marketing, Sales

SORT ORDER
name

DESCRIPTOR ATTRIBUTES
name, location

OMS-30551: Avoid excessive use of entity and attribute names in description texts. However, if you refer to entity names in description and notes texts, do so in uppercase.
Rationale Excessive use makes text unreadable. With the annotation convention of putting entities in uppercase it is always clear that you refer to an entity.

Attention: If you use 'Cherry Pie' place the entity names between brackets [] if you want them hyperlinked to the entity.
OMS-30552: Describe the life-cycle of the entity, if possible, but limit the overlap in text between entity and function descriptions to a minimum.
Rationale Describing the life-cycle of the entity, when it is created, updated and deleted, adds a lot of value to the understanding of the entity.

Entities - Notes
OMS-30554: Revision History of an Entity (and its subobjects such as attributes and relationships) must be recorded in the Notes, stating the date of creation, the date and the reason for the change, and the change itself.
Add new entries on top.
Rationale Keeping track of what is changed can explain a lot of what happened during the Requirements Definition. Questions such as "Why was this changed and who did it?" are easily answered. It is advised to also record new entries when the deliverable is offered to the client for approval. Another change you should record is the deletion of a Secondary Access Element such as an attribute or relationship.
Putting new entries on top gives quick insight into the latest change.
Example REVISION HISTORY
Label Date Name Description
1.1 12-11-1999 S. Muller Removed attribute POS_IND
1.0 06-07-1998 L. Jellema Initial Creation

1.1
----
Removed attribute POS_IND as result of decision of users, recorded
in document X.Y.Z

1.0
----
Initial Creation

Synonyms
OMS-61005: If there are other names that could also be used for an entity, then record those as synonyms.
Rationale Recording synonyms prevents that these names are used for another entity.

Attributes
OMG-10011: Consider keeping the length of attribute names limited to 22 characters maximum, including spaces. When the name is longer than 22 characters, make it shorter by removing every second and following vowels of a word. Start at the last word of the plural and moving forward, until the name is short enough.
Well-known abbreviations might be used as an alternative, for example NO for NUMBER or QTY for QUANTITY, etc. These abbreviations must be recorded in the Business Terminology.
Rationale This avoids trimming of column names when using the Database Design Transformer to generate tables.
Example ESTIMATED TIME OF ARRIVAL becomes
ESTIMATED TIM OF ARRVL (22 characters long)

OMS-30560: Do not enter attributes that represent relationships, as foreign key attributes do not exist.
Rationale The Database Design Transformer creates foreign key columns, wherever appropriate.

OMS-30563: Every attribute must range over at least two possible values (NULL is seen as a value as well).
Rationale If this would not be the case, the attribute is just a constant that does not add value.

OMS-30564: Avoid modeling derived attributes.
Rationale Since this information is derived, it is not necessary to model this information explicitly.
Example Do not model [order total].

OMS-30655: Do not use Attribute Allowable Values.
Rationale Allowable Values are best implemented in domains. The same domain is used for the related column and therefore a domain makes changing these values easy.

OMS-61502: An attribute should only be added to a subtype entity if it is specific for that subtype only.
Rationale Subtype entities inherit the attributes of the supertype entity automatically.

OMS-61519: During analysis define explicit organization / project specific conventions standards for:
  • date/ month/ year/ time attributes
  • amount attributes
  • currency attributes
  • change history attributes
  • description attributes
  • flag (Yes/No) attributes
  • status attributes
  • number attributes
  • sequence (within parent) attributes
Rationale When this is omitted the risk is introduced of an inconsistent way of naming similar attributes, also leading to an inconsistent way of naming similar columns.
Example The following naming conventions are suggested:
Type Naming Convention
Date Attributes ... DATE
Time Attributes ... TIME
Amount Attributes ... AMT
Currency Attributes ... CURR
Description Attributes ... DESC
Flag Attributes ... FLAG
Status Attributes ... STATUS
Number Attributes ... NUM
Sequence within parent Attributes SEQ IN P

Attributes - Name
OMS-30565: An attribute name does not include its own entity name (it may include the name of another entity).
Rationale Including its own entity name in an attribute name is normally redundant. This is because at any time an attribute name is used, it should be prefixed with the entity name (such as later on the column name should be prefixed with the table alias). Including the entity name consumes space and often makes reading text more difficult.
Example [DEPARTMENT.department number] should be [DEPARTMENT.number]
[EMPLOYEE.employee name] should be [EMPLOYEE.name]
[EMPLOYEE.in department from] is allowed

OMS-30566: Attribute names must consist of at most three words.
Rationale By following this standard you keep attribute names short and understandable.

OMS-30567: Only use the alphanumeric characters, spaces and '-' in attribute names.
Rationale When running the Database Design Transformer, the attribute names are used for the column names.
Names of objects in the Oracle Server can contain only alphanumeric characters from the database character set and the characters _, $, and #. Underscores are superfluous as the Database Design Transformer converts spaces to underscores. The character '-' is transformed to an underscore. Use of $ and # can lead to interpretation conflicts when using certain tools to access the data.

OMS-30568: Do not use Oracle reserved words as attribute names.
Rationale Attribute names are used to create column names by the Database Design Transformer, and reserved words are not allowed as column names.
Example Do not create an attribute called SELECT

Attention: Oracle 8 reserved words are:
ACCESS ADD ALL ALTER
AND ANY AS ASC
AUDIT BETWEEN BY CHAR
CHECK CLUSTER COLUMN COMMENT
COMPRESS CONNECT CREATE CURRENT
DATE DECIMAL DEFAULT DELETE
DESC DISTINCT DROP ELSE
EXCLUSIVE EXISTS FILE FLOAT
FOR FROM GRANT GROUP
HAVING IDENTIFIED IMMEDIATE IN
INCREMENT INDEX INITIAL INSERT
INTEGER INTERSECT INTO IS
LEVEL LIKE LOCK LONG
MAXEXTENTS MINUS MODE MODIFY
NETWORK NOAUDIT NOCOMPRESS NOT
NOWAIT NULL NUMBER OF
OFFLINE ON ONLINE OPTION
OR ORDER PCTFREE PRIOR
PRIVILEGES PUBLIC RAW RENAME
RESOURCE TRIGGER REVOKE ROW
ROWID ROWNUM ROWS SELECT
SESSION SET SHARE SIZE
SMALLINT START SUCCESSFUL SYNONYM
SYSDATE TABLE THEN TO
UID UNION UNIQUE UPDATE
USER VALIDATE VALUES VARCHAR
VARCHAR2 VIEW WHENEVER WHERE

OMS-30569: Do not use the same attribute name for an attribute at super- and subtype levels, nor for attributes on the level of brother subtype entities.
Rationale When one cannot think of different names for both attributes, they probably relate to the same property, indicating a bad design. If they relate to the same property, but there are restrictions on the applicable values for the different subtypes, you should record a business rule to model that.
When the same attribute name is used for more than one subtype, and the supertype with its subtypes are mapped to one table, the Database Transformer (arbitrary) maps only one of them to a column with this name. The other attributes are mapped to columns that get prefixed with the alias of the subtype. For reasons of clarity, it's better only to prefix foreign key and discriminator columns with entity/table aliases.

OMS-30571: For frequently used words within attribute names (for example, date, indicator, status), always put these words in the same position. Always use the same word(s) for a specific notion.
Rationale When the same word is put at different positions, one tends to make errors when using attribute names, such as writing BIRTH DATE instead of DATE OF BIRTH.
Example Do use END DATE, BIRTH DATE, FIRST COLLECTION DATE (Date is always at the end.).

Do not use END DATE, DATE OF BIRTH, FIRST DATE COLLECTION (Emphasis is placed on the combination of the words and the word order.)

OMS-61007: Attribute names must be singular.
Rationale If you want to use a plural for attributes it probably indicates that you did not normalize correctly. There are exceptions such as 'Christian Names'.

OMS-61008: Do not use prepositions in attribute names.
Rationale By following this standard you keep attribute names short and understandable.

Attributes - Sequence in Entity
OMS-60001: For each entity provide the attributes with a logical ordering from a functional point of view. Use the following ordering sequence for attributes:
1. first unique identifier attribute(s)
2. other unique identifier attribute(s)
3. all other attributes
Rationale Ordering the attributes logically makes it easier to understand the model, especially for entities with many attributes.
Example EMPLOYEE
# NUMBER
* FIRST NAME
* SURNAME
o TITLE
* HIREDATE
o FIREDATE

Database Design Transformer: The Database Design Transformer carries forward this sequence to the corresponding table.
Attributes - Domain
OMG-10012: Consider basing an attribute that is part of the first unique identifier on a domain. This can be a generic domain like ID for all system defined unique identifiers.
Rationale The domains for unique identifiers are inherited by the corresponding primary key and foreign key columns after running the Database Design Transformer. Recording a domain for unique identifiers enables you to change formats of corresponding primary key columns, whereby referencing foreign key columns are updated simultaneously.

OMG-10015: Do not use parent domains for an attribute.
Rationale When using the Database Design Transformer, domain references are transferred to columns. It is recommended not to use parent domains for columns (see OMG-10014).

OMS-30581: If an attribute is specified to be in a domain, its properties must match the properties defined in the domain or must be a subset of the properties in that domain.
Rationale In this way your model is consistent.

Warning: If you change the attributes Format or Maximum length the domain reference is removed.
Attention: If you change properties of a domain after you have associated this domain with one or more attributes, you must run the 'Update attributes in a domain' utility to cascade the changes to these attributes of the domain. Note however, that not all of the attributes change by running this utility. Only the length specifications are updated (maximum attribute length, aver age attribute length and attribute decimal places).
OMS-61009: Use domains for attributes with the same name, format, max. length, unit and default.
Rationale Using domains in this case allows you to rapidly enter property information. It makes your application consistent and it is easy to make a global change by using the utility 'Update attributes in a domain'.
Example ADDRESS

Suggestion: Domains can be created from the Entity Relationship Diagrammer by invoking the Edit Domains dialog box that is accessible through the Edit - Domain menu entry.
Attributes - Format
OMG-10070: For attributes including the word NUMBER, DATE or CODE in their name, use the formats NUMBER, DATE, or CHAR, respectively.
Rationale It is less confusing if the attribute names are consistent with the formats used. Therefore, by using this guideline attributes (and the resulting columns) are easier to use.

OMS-30770: Do not use the following attribute formats:
  • VARCHAR2
  • INTEGER
Rationale By making a distinction between CHAR and VARCHAR2 one might want to express that CHAR attributes have a fixed and VARCHAR2 attributes a variable length. However, in the end the database does not enforce CHAR columns to have a character for each position. The server simply adds spaces if necessary. So, when you want to express explicitly that a CHAR attribute has a fixed length, you need to record a business function anyway.
In the end INTEGER attributes get mapped to NUMBER(38) columns. To prevent that very large values can be stored it is better to explicitly specify the dimensions by using the NUMBER datatype with 0 decimals.
Also, using formats that the user understands decreases the communication gap between users and developers.

Database Design Transformer: The following table shows how the attribute format is translated by the Database Design Transformer into a column data type and a column display data type. The Server Generator subsequently translates the data type, in the event that the column data type as recorded in Oracle Designer is not known by Oracle8.
Attribute Format Column Data Type Column Display Data Type Column Data TypeCreated by Server Generator
CHAR VARCHAR2 Text VARCHAR2
DATE DATE Text DATE
IMAGE IMAGE N/A LONG RAW
INTEGER INTEGER Text NUMBER
MONEY NUMBER Text NUMBER
NUMBER NUMBER Text NUMBER
PHOTOGRAPH LONG RAW N/A LONG RAW
SOUND SOUND N/A LONG RAW
TEXT LONG N/A LONG
TIME DATE Text DATE
TIMESTAMP DATE Text DATE
VARCHAR2 VARCHAR2 Text VARCHAR2
VIDEO LONG RAW N/A LONG RAW
Attributes - Maximum Length
OMS-30590: Ignore special formats for numeric values when determining the maximum length.
Rationale Special formats are not recorded in the database and since attributes are translated to columns by the Database Design Transformer you do not want to specify an attribute with a bigger maximum length than necessary.
Example $ 9,999,999.99 should be recorded as length 9 (not length 14) including two decimal places;

OMS-61520: Always provide a maximum length for each attribute, except for DATE, IMAGE, SOUND or VIDEO type of attributes.
Rationale Do not rely on maximum lengths that are assigned by the Database Design Wizard or the server DDL generator.

Attributes - Decimal Places
OMS-30591: Always enter the number of decimal places for attributes of format NUMBER.
Rationale Not specifying this number will lead to floating decimal separator.

Attributes - Optional ?
OMS-30586: When the attribute value is unknown at the time of creation of the entity, but will get a value in due time, you should define the attribute as optional.
Rationale Although sometimes it looks that an attribute is mandatory it can happen that the information is not known at the time of recording an occurrence.
Example All persons have a 'birth date' but the date is not always known at the moment of recording that person.

OMS-61611: If an attribute has a default value, it should be a mandatory attribute.
Rationale The attribute is implemented by a mandatory column, and at insert the column always gets a value. It rarely is intended that during update the column can be nullified.

Exception: If subtype entities are implemented using a supertype table, mandatory subtype attributes are implemented by optional columns, so in that case this standard is not necessary.
OMS-61613: If an attribute has an enumerated domain of format CHAR or VARCHAR2, it should be a mandatory attribute.
Rationale A mandatory attribute is translated into a mandatory column. Mandatory columns have better performance because searches for the null value are not necessary. Also programming for this column is easier because the programmer does not need to make exceptions when the value is null.
This would argue for all attributes to be mandatory, but only attributes with an enumerated character domain have the possibility to become mandatory by adding another allowable value which indicates that the attribute has no value.
Example If you have an optional attribute in a domain with the allowable values OPEN and CLOSED, you can make it a mandatory attribute by adding an allowable value UNKNOWN (and setting the Optional property to No).

Exception: If subtype entities are implemented using a supertype table, mandatory subtype attributes are implemented by optional columns, so in that case this standard is not necessary.
Attributes - On Condition
OMS-30620: Do not use the On Condition property. Specify tuple rules instead.
Rationale CDM uses functions to define rules. See chapter Business Rule Modeling in the CDM Standards and Guidelines Library volume 1.

Attributes - Units
OMS-30630: Use units for numeric attributes only.
Rationale Units for non-numeric attributes do not make any sense.

Attributes - Derivation
OMS-30650: Do not use the Derivation property. Instead, use the function object for modeling the derivation rule.
Rationale CDM uses functions to define rules. See chapter Business Rule Modeling in the CDM Standards and Guidelines Library volume 1.

Exception: There is one exception to this standard. If the attribute needs to be UPPERCASE, you can put the word UPPERCASE in the derivation.
Attributes - Comment
OMS-61521: Always record a comment for the attribute that gives a comprehensive description of the purpose, aimed at end users.
Rationale For each attribute the purpose should be clear. The comment is transferred by the Database Design Wizard to the user hint text of the corresponding column.

Attributes - Derivation Expression
OMS-61596: Do not use the Validation Rules property for an attribute.
Rationale CDM uses functions to define rules. See chapter Business Rule Modeling in the CDM Standards and Guidelines Library volume 1.

Relationships
OMS-61503: A relationship should only be attached to a subtype entity if it is specific for that subtype only.
Rationale Subtypes inherit the relationships of the supertype automatically.

OMS-61504: A relationship should only be attached to a supertype entity if the relationship is valid for all its subtypes.
Rationale If a relationship is valid for only some of the subtypes, then you must not attach the relationship to the supertype as that implies the relationship is valid for all subtypes. If a relationship is valid for all subtypes, you should attach it only once to the supertype in order to avoid redundancy.

OMS-61516: Special conditions that apply to relationships, such as:
  • relationships that involve time-dependency (always one current at any moment, but many in time)
  • conditions for relationships that cannot be diagrammed; for example, several relationships that maintain an inclusive OR relation (versus the exclusive OR expressed by an arc)
  • conditions for transfer
should be modeled as either other inter-entity rules or other create/update/delete/modify rules and recorded in the function object.
Rationale These kind of conditions cannot be modeled within the standard modeling techniques. Therefore, these must be modeled specifically as business rules.
See chapter Business Rule Modeling in volume 1 of the CDM Standards and Guidelines Library.

Relationships - Relationship Name
OMS-30850: Name relationships in lowercase only.
Rationale Lowercase letters take less space than uppercase. This makes it more readable on the entity relationship diagrams.

OMS-30851: Name a relationship in such a way that you can read it, together with the entity name, as a normal sentence.
It normally consists of
  • a verb and
  • a preposition or
  • an expression that ends with a preposition
All names try to describe how the entities are related.
Rationale This makes a diagram more readable.
Example use / used by
consists of / assigned to
is responsible for / is under authority of

Attention: The standard report of Oracle Designer uses the following construction to document entities with their relationships:
Each occurrence of This Entity:
MAY BE / MUST BE [relationship name] ONE AND ONLY ONE/ONE OR MORE [other entity name/plural].

For example for a PERSON entity:
Each occurrence of This Entity:
MAY BE parent for ONE OR MORE PERSONS.
MAY BE married to ONE AND ONLY ONE PERSON.
OMS-30852: Only use the alphanumeric characters, spaces and '-' in relationship names.
Rationale Relationship names are used to clarify how two entities are related in one short sentence: [entity name] [relationship name] [other entity name]. Symbols are normally not used in natural language.
Also, if you use the Database Design Transformer, the relationship names will be used in foreign key and index names when there are more relationships between the same two entities.
Names of objects in the Oracle Server can contain only alphanumeric characters from the database character set and the characters _, $, and #. Underscores are superfluous as the Database Design Transformer converts spaces to underscores. The character '-' is transformed to an underscore. Use of $ and # can lead to interpretation conflicts when using certain tools to access the data.

OMS-30853: For the often occurring many-to-one relationships between a particular entity and its type, always use the same naming.
Rationale This ensures consistency throughout the diagram, and thereby makes it easier to read and understand.
Example is classified by
WOCKLE >>----------------- - - - - - - - - - WOCKLE TYPE
classifies
is classified by
PROJECT >>---------------- - - - - - - - - - PROJECT TYPE
classifies

OMS-30854: Relationship names should not contain entity nor attribute names.
Rationale This is superfluous information. If other entity names are used than those to which the relationship connects, then this may be an indication of bad design.

OMS-61515: Only use abbreviations that are agreed upon and listed in the Business Terminology.
Rationale Relationship names are used to clarify how two entities are related in one short sentence. Therefore only language understandable to the users should be used.

Relationships - Minimum Cardinality
OMG-10007: Avoid relationships that are mandatory on both sides.
Rationale The server does not support this kind of relationship. It can only be enforced at the client side, so when it is possible, use a different feasible construction.

OMS-61522: For Minimum Cardinality only use one of the following values
  • 0 for optional
  • 1 for mandatory
  • empty no restriction on the number of occurrences
When there is another restriction on the cardinality of a relationship, for example for each Line exactly two Points should be recorded record a business rule of the type Other Inter Entity Rule instead.
Rationale The Database Design Wizard does not use any other values than the mentioned one, so any other information of rules about the cardinality of the relationship gets lost. To prevent this a business rule should be recorded instead.

Relationships - Maximum Cardinality
OMS-61523: For Maximum Cardinality only use one of the following values
  • 0 for optional
  • 1 for mandatory
  • empty no restriction on the number of occurrences
When there is another restriction on the cardinality of a relationship, for example for each Line exactly two Points should be recorded, record a business rule of the type Other Inter Entity Rule instead.
Rationale The Database Design Wizard does not use any other values than the mentioned one, so any other information of rules about the cardinality of the relationship gets lost. To prevent this a business rule should be recorded instead.

Relationships - In Arc
OMS-30842: Use arcs only to group relationship ends that are all optional or all mandatory.
Rationale An arc indicates that an instance of the entity may at one point of time be included in one and only one of the relationships included in the arc, or none of them. If it can be none of them, then all relationships included in the arc are optional. If it must be one of them, then all relationships included in the arc are mandatory.

OMS-30843: Use arcs only to group relationship ends that are all of the same degree.
Rationale Including relationships of different degree indicate incorrect modeling, as relationships within the same arc are normally of the same nature. For example, in a Booking Registration system a TOURIST travels one section either by FLIGHT, CAR, SHIP, BUS or TRAIN or non of these (for example walking or cycling). The nature of these relationships are all the same; 'traveling using'.

Relationships - Transferable ?
OMS-30870: If special conditions apply to the transferability of a relationship, these conditions should be recorded in the Function object.
Rationale CDM uses Business Functions to record this type of Business Rules. See chapter Business Rule Modeling in the CDM Standards and Guidelines Library volume 1.

Relationships - Notes
OMG-10013: For 1:1 relationships that are mandatory or optional on both sides, record in the notes at what side of the relationship the foreign key should be modeled.
Rationale During analysis it is obvious what side the foreign key should be modeled, and thus easy to record. During design it might not be that obvious anymore.

OMS-30880: Explicitly comment on the following special relationships:
  • one-to-one relationships
  • recursive relationships
  • relationships that are either mandatory or optional at both ends of the relationship
Rationale These kinds of relationships are not common, so they might indicate bad design. However, there might be a good reason for having them. By clearly documenting this confusion can be prevented about their existence.

Unique Identifier Entries - Attribute
OMS-30894: Only use mandatory attributes as part of the primary unique identifier.
Rationale The primary identifier of an entity is meant to uniquely identify every instance of that entity. Therefore every part of that identifier must always be present to be able to make the unique identification.

Database Design Transformer: When running the Database Design Transformer, the primary key is built from the columns created from the attributes that are included in the primary unique identifier. A primary key can only consist of mandatory columns.
Unique Identifier Entries - Relationship
OMS-30895: Only mandatory to one relationships are allowed as part of the primary unique identifier.
Rationale The primary identifier of an entity is meant to uniquely identify every instance of that entity. Therefore every part of that identifier must always be present (mandatory) to be able to make the unique identification.
The other end must have a cardinality of one, otherwise the relation will not be transformed into a foreign key column in the table, and cannot be part of the primary key.

Dataflows - Name
OMS-31530: Use self-explanatory, descriptive, preferably short dataflow names.
Rationale Using good dataflow names makes the diagrams more readable in the dataflow diagrammer, as the name is displayed in the events.
Example Department, Yearly Turnover Data
Personal Wages
Invoice number

Attention: Dataflow names do not need to be unique. Often, dataflow names are recycled and used for dataflows on different levels. A dataflow is uniquely identified by its name, source, and destination.
Dataflows - Definition
OMS-31531: Do not use this field for listing of dataflow contents; merely describe the flow globally.
Rationale The dataflow content is expected in the attributes, or data items of the dataflow, so entering this kind of information in the description increases the risk that this information is overseen at a later stage.

Relation Definitions - Name
OMS-61601: Only use the alphanumeric characters and '_' in the name of tables, views and snapshots.
Rationale Names of objects in the Oracle Server can contain only alphanumeric characters from the database character set and the characters _, $, and #. Use of $ and # can lead to interpretation conflicts when using certain tools to access the data.

Table Definitions
OMG-10075: Use surrogate primary keys (a sequence without meaning to the end users) for all tables.
Rationale Primary keys are not updateable. If you use surrogate primary keys, the end users are allowed to update any column that is meaningful to them (usually the surrogate primary key is not even shown).
Also, it avoids primary keys of more than one column. This avoids storage and coding implications of concatenated keys used as foreign keys.
If, for programming reasons, you want to have a certain foreign value available in a table, you can add a denormalized foreign key which is not part of the primary key of the table.
Example For the table HSD_DEPARTMENTS, the end users are only interested in the Department Name and Location, which make a department unique. The Department Id is a surrogate key, filled by an Oracle Sequence. The id is not shown to the users, they are not interested in its value.
This id is used as a foreign key for the Employees table, so that in HSD_EMPLOYEES there is a DEP_ID column identifying the Department, instead of a DEP_NAME and DEP_LOCATION column.

Database Design Transformer: When creating tables from entities, you can set the 'Create surrogate keys for each new table' check box in the Keys tab of the Settings dialog. If you do so, also specify that those columns should be called ID (see OMS-42257): go to the Other Settings tab and uncheck 'Surrogate key columns' and 'Columns' at the Elements for which you want prefixes generated.
OMS-61617: If a table has a surrogate primary key (a sequence without meaning to the end users), the table must have at least one unique key (often referred to as the 'natural' key).
Rationale A surrogate key is meaningless to the end users. Therefore they need another way to uniquely identify a row, that is they need a unique key. Otherwise, it is not possible to specify which row is to be used in a foreign key from another table.
Example If the table HSD_DEPARTMENTS has a surrogate key (the ID column), the users need another unique key, for example the combination of NAME and LOCATION. Now, if the users must assign an employee to a department, they only need to specify the Department Name and Location, and the program will determine its Id.

Table Definitions - Name
OMS-42100: Prefix table names with the application code.
Rationale In this way, naming conflicts with database objects from other applications are avoided. In addition, it is immediately visible to which application a certain database object belongs.
Example Oracle Applications consist of many different application modules. The application code for General Ledger is GL, and the application code for Accounts Receivables is AR.
In both application modules there are tables containing lookup values, namely GL_LOOKUPS and AR_LOOKUPS. These tables are created in separate schemas, but all tables are granted to one application user, often called APPS, which again has synonyms defined for the granted objects. If the application code had not been used in this situation, there would have been a naming conflict. Also by using the application code, it is immediately visible which table belongs to which module.

OMS-42101: Define table names in plural.
Rationale The name of the table normally indicates what is the content of the table. Tables physically contain more entries of that content, and is therefore perceived as plural. An Entity on the other hand, which is defined in singular, represents the definition of an object of importance within the business that is modeled and are therefore perceived as singular.

Database Design Transformer: When using the Database Design Transformer to create tables from entities, then the plural property of the entity is used as the table name.
OMS-42103: Name tables derived from an entity using the convention
[application code]_[entity plural]
Rationale By using this naming convention it is immediately visible from which entity the table is derived.
Example OMS_PRODUCTS

OMS -- application code
PRODUCTS -- entity name in plural

OMS-61560: Name journal tables after the table with the actual data, suffixed by _JN.
Rationale This is consistent with the naming convention used by Oracle Designer. Also, when using the RON, the journal table is shown directly under the table with the actual data. Journal tables can easily be recognized. In addition, it is easier to create and name journal tables by using a utility.
Example QMS_EMPLOYEES has the QMS_EMPLOYEES_JN as journal table.

OMS-61561: When the name of the table exceeds 30 characters, make it shorter by removing every second and following vowels of a word. Start at the last word of the plural and move forward, until the name is short enough.
Well-known abbreviations might be used as an alternative, such as for example NO for NUMBER or QTY for QUANTITY. These abbreviations must be recorded in the Business Terminology.
Rationale Table names cannot be longer than 30 characters. The table names are used a lot during build and therefore should be highly predictable and logical.
Example MAS_EMPLOYEE_ASSIGNMENT_HISTORY becomes
MAS_EMPLOYEE_ASSIGNMENT_HISTR (29 characters long)

Table Definitions - Alias
OMS-42104: Define an alias for each table.
Rationale Table aliases are used in SQL Statements to indicate to which table a certain column belongs. Also, the table alias is used in naming conventions for key constraints, indexes, PL/SQL Definitions, module components, etc.

Reference: See PL/SQL standards for more details about using the alias in SQL statements.
Database Design Transformer: The table alias is used by the Database Design Transformer when generating constraint names for primary, unique, and foreign keys, and when generating default indexes for the table.
OMS-61530: The table alias should be exactly three alphanumeric characters, except for journal tables, which should be four.
Rationale Table aliases are used in SQL Statements to indicate to which table a certain column belongs. Also the aliases are used in the naming conventions for key constraints, indexes, PL/SQL Definitions, module components, etc.
When the alias is used so heavily, the shorter the better is a good rule. Three letters is the shortest that leaves enough possibilities. For journal tables see OMS-61531.
To be able to identify the table alias easily from the names of the above mentioned objects, the characters must be alphanumeric.

Exception: Large systems may change this standard to 4 characters (5 for journal tables), if OMS-30534 and OMS-61599 are also changed accordingly.
OMS-61531: The alias of a journal table is the same as that of the table with the actual data, followed by the character J.
Rationale This makes it easier to create journal tables by using a utility.
Example QMS_EMPLOYEES has alias EMP, QMS_EMPLOYEES_JN has alias EMPJ.

Table Definitions - Col. Prefix
OMS-61625: Do not use the Column Prefix property for Tables.
Rationale A column is always used in the context of its table, so the information would be superfluous.

Table Definitions - Display Title
OMG-10026: Enter a Display Title for the table to indicate a default.
If the table maps one to one to an entity, then use the plural entity name as the Display Title.
Rationale A default makes it easier to provide consistent titles throughout the Application. The entity name is meaningful for the business, and is therefore in most situations a good choice for the display title.

Application Design Transformer: When running the Application Design Transformer after having run the Database Design Transformer, the Display Title of the table becomes the default display title of the Module Component. However, the window title is NOT populated by this title; it is left empty.
When the window title property itself is not populated, and it is not the first module component in the module, then the Forms Generator uses the module component as the window display title.
When it is the first module component, and there has not been specified any window title, then the module display title is used. Only when also the display title is used, then the display title of the module component is also used for the first window title.
Table Definitions - Journal
OMS-60009: When using journaling, set the Journal property to Server.
Rationale If you use Client or Client calls server procedure, the Form Generator creates code in all related forms to populate the journal table. This behavior is undesirable, because the nature of journaling makes server-side implementation imperative.

Exception: If you want to use the Headstart Utility for Journalling, set the Journal property to None. Headstart has a different way of specifying which tables need to have which journalling. See the Headstart Utilities User Guide for more information.
Table Definitions - Start Rows
OMS-42107: Define the expected initial number of rows in a table as estimated for the Production environment. Specify, if necessary, what the source for this information is in the Notes, using the keyword NO OF ROWS START.
Rationale This information is required for determining the physical requirements of the production environment.
Example BALDRICH 12-NOV-1995 11:00 NO OF ROWS START
Based on a count of the number of records in file
FGM887 after eliminating duplicates

Table Definitions - End Rows
OMS-42108: Define the expected final number of rows in each table. Specify, if necessary, the source for this information in the Notes, using the keyword NO OF ROWS FINAL.
Rationale This information is required for determining the physical requirements of the production environment.
Example BALDRICH 12-NOV-1995 11:30 NO OF ROWS FINAL
File FGM887 grew with an average of 15,000 records a year for the last five years. Taking duplicates entries into account, the table will grow to 1,000,000 rows in the first year.

OMS-42122: Define the expected final number of rows as estimated for the Production environment based on a fixed point in the future; for example one year. Use this point, called the Database Sizing Horizon, consistently for all table definitions of the application.
Rationale This provides consistency throughout your estimating.

OMS-42123: If the sizing estimation point for the Table differs from the standard Database Sizing Horizon, as defined for the Application System, specify the Table's sizing estimation point in the Description for the Table.
Rationale This makes it clear what is the estimating point when it differs from the default.

Table Definitions - Comment
OMS-42105: Describe the purpose of a table in a maximum of 60 characters.
Rationale This provides a quick understanding of the purpose of the table. This is especially useful when quickly scanning through the tables within the various navigators.
Example All departments of the company in Europe.

Table Definitions - Description
OMS-42106: Define the purpose and usage in detail for tables that do not 1:1 implement an entity. Use a similar template as used to document entities.
Rationale The purpose of the table that implement an entity is maintained only within the entity. This prevents duplication of information and thereby the risk of inconsistencies. The purpose of the tables that do not implement an entity is naturally not documented anywhere else, so this should therefore be maintained within the table itself.

Table Definitions - Notes
OMS-60070: Revision History of a Table (including its subobjects such as columns and constraints) must be recorded in the Notes, stating the date of creation, the date and the reason for the change, and the change itself. Add new entries on the top.
Rationale Keeping track of what is changed can explain a lot of what happened during the Development. Questions such as, "Why was this changed and who did it?" are easily answered. It is advised to also record new entries when the deliverable is offered to the client for approval.
Putting new entries on top gives quick insight into the latest change.
Example REVISION HISTORY
================
Label Date Name Description
1.1 06-01-1999 L. Jellema Change History Columns added
1.0 06-07-1998 L. Jellema Initial Creation


1.1
----
Added change history columns CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY to enable initial creation, and last update information for every record.

1.0
----
Initial Creation

View Definitions
OMG-10065: Views can be defined in two ways:
1. by recording base relations only
2. by recording a free format select statement
When using free format select statements, also record the base relations. (rat: impact analysis)
It is not always possible to define a view by recording the base relation, for example, if the select expression of a column takes more than one line to record.
Define the view by recording base relations if possible.
Rationale Defining the view this way ensures that the relationship between the view and the tables or views on which this view is based, are explicitly recorded. This facilitates a better quality of impact analysis.

View Definitions - Name
OMS-42201: Define view names in plural.
Rationale The name of the view normally indicates what is the content of the view. Views physically contain more entries of that content, and are therefore perceived as plural.

OMS-42202: Name views according to the following convention:
[application code]_V_[logical name]
Rationale This makes views easier to recognize, for example, in case of a select from one of the static dictionary views of the Oracle Server.
Example
OMS_V_CUSTOMERS
OMS Application code
V View indicator
CUSTOMERS Logical name in plural
AIF_V_ORDERS
AIF Application code
V View indicator
ORDERS Logical name in plural

View Definitions - Alias
OMS-60025: Define an alias for each view.
Rationale View aliases are used in SQL Statements to indicate to which view a certain column belongs. Also, the view alias is used in naming conventions for key constraints, PL/SQL Definitions, module components, etc.

Reference: See the PL/SQL standards for more details about using an alias in SQL statements.
OMS-61599: Name the view alias following the convention V_[three alphanumeric characters].
Rationale If you start the view alias with V_ it is clear that it concerns a view and you have the possibility to follow it with an existing table alias (for example, when the view is based primarily on one table).
View aliases are used in SQL Statements to indicate to which view a certain column belongs. Also the aliases are used in naming conventions for key constraints, PL/SQL Definitions, module components, etc.
When the alias is used so heavily, the shorter the better is a good rule. For the last part of the alias, three characters is the shortest that leaves enough possibilities.
To be able to identify the view alias easily from the names of the above mentioned objects, the characters must be alphanumeric.
Example V_EMP

Exception: Large systems may change this standard to 4 characters, if OMS-30534 and OMS-61530 are also changed accordingly.
View Definitions - Col. Prefix
OMS-42210: Do not use a column prefix for view columns.
Rationale Using the view alias as a prefix would be superfluous as column names should always be prefixed by the table alias when used in SQL statements.

View Definitions - Display Title
OMG-10045: Enter a Display Title for the view to indicate a default if it is likely to be used as a module component usage.
Rationale A default makes it easier to provide consistent titles throughout the Application.

Application Design Transformer: When running the Application Design Transformer after having run the Database Design Transformer, the Display Title of the view becomes the default display title of the Module Component. However, the window title is NOT populated by this title; it is left empty.
When the window title property itself is not populated, and it is not the first module component in the module, then the Forms Generator uses the module component as the window display title.
When it is the first module component, and there has not been specified any window title, then the module display title is used. Only when also this display title is used, then the display title of the module component is also used for the first window title.
View Definitions - Comment
OMS-42205: Describe the purpose of the view in a maximum of 60 characters.
Rationale This provides a quick understanding of the purpose of the view. This is especially useful when quickly scanning through the views within the various navigators.

View Definitions - Description
OMS-42212: Define the purpose and usage in detail for the view. Use a similar template as used to document entities.
Rationale This helps your team members understand what is the purpose and usage of the view.

View Definitions - Notes
OMS-61609: Revision History of a View (including its subobjects such as columns and base table usages) must be recorded in the Notes, stating the date of creation, the date and the reason for the change, and the change itself. Add new entries on the top.
Rationale Keeping track of what is changed can explain a lot of what happened during the Development. Questions such as, "Why was this changed and who did it?" are easily answered. It is advised to also record new entries when the deliverable is offered to the client for approval.
Putting new entries on top gives quick insight into the latest change.
Example REVISION HISTORY
================
Label Date Name Description
1.1 06-01-1999 L. Jellema Change History Columns added
1.0 06-07-1998 L. Jellema Initial Creation


1.1
----
Added change history columns CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY to enable initial creation, and last update information for every record.

1.0
----
Initial Creation

Columns
OMS-42404: Do not use the Allowable Values for Columns.
Rationale The use of domains is preferred. This allows for reuse. If no suitable domain exists, consider creating one.

Columns - Name
OMS-42207: Assign to columns in a view the same name as the columns in the underlying tables/views. In case of naming conflicts, include the underlying table/view alias as a column prefix.
Rationale In this way, it is immediately obvious to what columns the view columns relate. This information is valuable during development and maintenance.

OMS-42250: Define column names in singular.
Rationale Each column should represent singular information. If a plural is used, this indicates bad naming or design; either more columns are required or another table is required to maintain multiple entries for this information.
Example If the column TELEPHONE_NUMBERS is used for a CUSTOMER, then this could either be changed into a number of columns :
FAX_NUMBER, HOME_TEL_NUMBER, MOBILE_NUMBER, WORK_NUMBER, or another table may be required, like CONTACT_NUMBERS, including indicators like FAX, HOME, MOBILE etc.

OMS-42251: Do not use the table/view alias as a prefix in column names, except for discriminator columns (suffixed by _TYPE) and columns that are in a recursive relationship (also known as pig's ear).
Rationale Using the table/view alias as a prefix would be superfluous as column names should always be prefixed by the table/view alias when used in SQL statements.

Reference: View the PL/SQL standards for more details.
OMS-42252: Keep the column names short, but still logical and self-descriptive.
Columns that implement an attribute should have the same, or very similar, names as the attribute they implement. Use an underscore where the attribute contains spaces.
When the name of the column exceeds 30 characters, make it shorter by removing every second and following vowels of a word. Start at the last word of the plural and move forward, until the name is short enough.
As an alternative well-known abbreviations might be used, for example NO for NUMBER or QTY for QUANTITY. These abbreviations must be recorded in the Business Terminology.
Rationale A column name may not exceed a length of 30 characters. When a column implements an attribute, using the same name as the attribute makes it easier to determine which column is the implementation of which attribute.

OMS-42255: Name discriminator columns using the naming convention [super entity short name]_TYPE
Rationale This is the Database Design Transformer's default. It also makes it easier to immediately determine what is the discriminator column without checking the repository (for example, when doing a describe in SQL Plus).
Example PRN_TYPE
PERSON -- super-entity name
PRN -- super-entity short name
TYPE -- suffix indicating the use of the column

OMS-42256: Define organization-/project-specific naming conventions for:
  • date/ month/ year/ time columns
  • code/mnemonic/abbreviation columns
  • amount columns
  • currency columns
  • change history columns
  • description columns
  • flag (Yes/No) columns
  • status columns
  • number columns
  • sequence within parent columns
Rationale This ensures consistency throughout your system.
Example The following naming conventions are suggested:
Type Naming Convention
Date Columns ..._DATE
Time Columns ..._TIME
Code Columns ..._CODE
Amount Columns ..._AMT
Currency Columns ..._CURR
Description Columns ..._DESC
Flag Columns ..._FLAG
Status Columns ..._STATUS
Number Columns ..._NUM
Sequence within parent columns SEQ_IN_P

OMS-42257: Define system-generated primary key columns using the name ID.
Rationale This makes the surrogate keys easier to identify, and it makes programming easier because the name is short and predictable. In this way you can also automatically create sequences for the surrogate key columns using a Headstart utility.

Database Design Transformer: You can let the Database Design Transformer create Surrogate Primary Keys. If you do so, you can specify that those columns should be called ID.
Both are specified with the Settings button of the Database Design Transformer.
To let the Transformer create surrogate keys, go to the Keys tab and check 'Create surrogate keys for all new tables'. If unchecked, surrogate keys are generated only when no unique identifier exists for an entity in the run set.
To let the Transformer use ID as the name for those columns, go to the Other Settings tab and uncheck 'Surrogate key columns' and 'Columns' at the Elements that you want prefixes generated for.
OMS-42553: Do not repeat the table/view name in the column name.
Rationale Since a column only makes sense in the context of a table/view it is not necessary to include the table/view name. When using column names in PL/SQL the standard is to always use the table/view alias as follows [alias].[column_name]. Putting the table/view name in the column name is therefor an unnecessary repetition that should be avoided.

OMS-60013: Name foreign key columns using the convention:
[ref table/view alias]_[ref table/view primary key column name]
where ref table/view means the referenced table or view.

When there are more foreign keys with the same referenced table and a uniqueness conflict would occur, use the following naming convention:
[ref table/view alias]_[ref table/view PK column name]_[logical name]
Use the same logical name as the relevant foreign key constraint name.
Rationale This makes it easy to see that a column is a foreign key column, but also what the foreign key column references.
In addition, it is easy to make a cross reference to the table or view referenced by the foreign key column as the name of the primary key of that table is known. This information is valuable during development and maintenance.
Example
PRN_ID
OMS_PERSONS Referenced table
PRN Alias referenced table
ID Primary key column referenced table
ADR_ID_BILL_TO and ADR_ID_SHIP_TO
OMS_ADDRESSES Referenced table
ADR Alias referenced table
ID Primary key column referenced table
BILL_TO Indicates that it is the Bill To address
SHIP_TO Indicates that it is the Ship To address

OMS-60071: Do not start column names with P_.
Rationale The Form Generator confuses such a column with parameters and will fail with an error message.

OMS-61600: Only use alphanumeric characters and '_' in column names.
Rationale Names of objects in the Oracle Server can contain only alphanumeric characters from the database character set and the characters _, $, and #. Use of $ and # can lead to interpretation conflicts when using certain tools to access the data.

OMS-61606: Do not use Oracle reserved words as column names.
Rationale Reserved words are not allowed as column names in the database server.
Example Do not create a column called SELECT

Attention: Oracle 8 reserved words are:
ACCESS ADD ALL ALTER
AND ANY AS ASC
AUDIT BETWEEN BY CHAR
CHECK CLUSTER COLUMN COMMENT
COMPRESS CONNECT CREATE CURRENT
DATE DECIMAL DEFAULT DELETE
DESC DISTINCT DROP ELSE
EXCLUSIVE EXISTS FILE FLOAT
FOR FROM GRANT GROUP
HAVING IDENTIFIED IMMEDIATE IN
INCREMENT INDEX INITIAL INSERT
INTEGER INTERSECT INTO IS
LEVEL LIKE LOCK LONG
MAXEXTENTS MINUS MODE MODIFY
NETWORK NOAUDIT NOCOMPRESS NOT
NOWAIT NULL NUMBER OF
OFFLINE ON ONLINE OPTION
OR ORDER PCTFREE PRIOR
PRIVILEGES PUBLIC RAW RENAME
RESOURCE TRIGGER REVOKE ROW
ROWID ROWNUM ROWS SELECT
SESSION SET SHARE SIZE
SMALLINT START SUCCESSFUL SYNONYM
SYSDATE TABLE THEN TO
UID UNION UNIQUE UPDATE
USER VALIDATE VALUES VARCHAR
VARCHAR2 VIEW WHENEVER WHERE

Columns - Sequence in Table
OMS-42270: Use the following ordering sequence for table columns:
1. primary key columns
2. unique key columns
3. foreign key columns

If spacing is an issue, then order the rest of the columns as follows:
4. all other mandatory columns
5. all other optional columns

For clarity, it is sometimes better to keep columns together, for example, BEGIN_DATE (mandatory) and END_DATE (optional).
Rationale In this way, optional columns end up at the end of a row not taking any space in the database. This also makes it easier to understand and verify the structure and completeness of the table in terms of columns.

Columns - Domain
OMG-10014: Do not use parent domains for a column.
Rationale The parent domain does not inherit the allowable values of the child domains.
Example LETTERS Parent domain (no allowable values)
LETTERS_A_TO_K Subdomain of LETTERS with allowable values from A to K
LETTERS_L_TO_Q Subdomain of LETTERS with allowable values from L to Q
LETTERS_R_TO_Z Subdomain of LETTERS with allowable values from R to Z
When selecting the domain LETTERS, you select NO allowable values.

OMS-42261: Any column that ranges over a fixed set of predefined values should be associated with a domain that describes that set of values.
Rationale This allows for reuse of the predefined values, and makes maintenance easier as whenever a value is changed, added or removed, this only has to be changed in the domain.

OMS-60012: For discriminator columns, use a domain with the discriminator values of the subtype table/entity usages ('Mapped to Entity') as the allowable values.
Rationale If the allowable values do not match the discriminator values, the Generators will create the wrong code. Using a domain instead of column allowable values is covered by OMS-42404.

OMS-60034: For view columns that are based on a table column, if the underlying column is in a domain, the view column should be in the same domain.
Rationale This prevents other values from being entered that are allowed by the table column.

OMS-61564: Link a column to the same domain as the corresponding attribute. When no domain has been specified for the attribute, a domain for the column may be introduced.
Rationale Otherwise, a discrepancy between analysis and design is introduced.

Columns - Datatype
OMS-42271: Only use the datatypes
  • DATE
  • BLOB
  • CLOB
  • NUMBER
  • VARCHAR2
If your database version does not support the BLOB and CLOB datatypes, then use LONG or LONG RAW instead.
Rationale The datatype CHAR may lead to coding errors when comparing values in PL/SQL code, and a CHAR(1) column does not use less space than a VARCHAR2(1) column. The other datatypes not mentioned either have no corresponding attribute format (such as DECIMAL and FLOAT) or are mapped by the server to a different internal datatype (such as DECIMAL, IMAGE).
When no maximum length and precision are specified
INTEGER and SMALLINT default to NUMBER(38),
REAL defaults to FLOAT(63),
DOUBLE PRECISION to FLOAT(126),
FLOAT defaults to FLOAT(12) and
NUMBER defaults to NUMBER.
To prevent unattended mappings in this matter, NUMBER columns with a maximum length precision specified should be used instead.

Note: The CLOB datatype is sensitive to NLS settings, and the BLOB datatype is not.
OMS-61563: In the case of Oracle7, do not define more than one column of the table to have the datatype LONG or LONG RAW. In the case of Oracle8, do not use LONG or LONG RAW. Use CLOB and BLOB instead.
Rationale You cannot create a table in the server that has more than one LONG (RAW) column. LONG and LONG RAW have become obsolete with Oracle8. Instead CLOB and BLOB should be used. There can be more than one column with these datatypes for one table.

OMS-61575: When the column of a View is 1:1 based on a base column, the property Datatype of the view column must be equal to that of the base column.
Rationale Discrepancies might lead to unwanted behavior (a different datatype might lead to an implicit time consuming datatype conversion) and probably an inconsistent look and feel.

Columns - Maximum Length
OMS-42273: Define the Maximum Length for number columns.
Rationale Number columns with no length will be 38 long in the database. This is often more than required.

OMS-61566: Set the maximum length of a column to the maximum length of the corresponding attribute.
Rationale Otherwise, a discrepancy between analysis and design is introduced.

OMS-61576: When the column of a View is 1:1 based on a base column, the property Maximum Length of the view column must be equal to that of the base column.
Rationale Discrepancies might lead to unwanted behavior (a different maximum length allows the user to enter data that is longer than the data that can be stored) and probably an inconsistent look and feel.

Columns - Decimal Places
OMS-42274: Define the number of decimal places necessary for number columns.
Rationale Not specifying this number leads to a floating decimal separator.

OMS-61567: Set the decimal places of a column to the decimal places of the corresponding attribute.
Rationale Otherwise, a discrepancy between analysis and design is introduced.

OMS-61577: When the column of a View is 1:1 based on a base column, the property Decimal Places of the view column must be equal to that of the base column.
Rationale Discrepancies might lead to unwanted behavior. A different value for the decimal places allows or restricts the user to enter decimal places where the actual column does not have the respective decimal places. In addition, it will probably lead to an inconsistent look and feel.

Columns - Optional ?
OMS-61532: Define discriminator columns indicating a subtype as mandatory.
Rationale If a discriminator column allows null values, it is not possible to determine to which type a record belongs that has the null value. Discriminator columns should ensure that every record is classified as one of the types it discriminates between.

OMS-61565: Set the optional flag of a column to the optional flag of the corresponding attribute. In case of super/subtype implementation by one table the only exception that does not need to be documented is the mapping of a mandatory attribute at the subtype level to an optional column.
Rationale Otherwise, a discrepancy between analysis and design is introduced.

OMS-61610: If a table column has a default value, it should be a mandatory column.
Rationale At insert the column always gets a value. It rarely is intended that during update the column can be nullified.

Note: If the column represents a subtype attribute which has a default value, the column should not get a default value but the default should be implemented by a business rule which applies only to that specific subtype.
OMS-61612: If a table column has an enumerated domain of datatype VARCHAR2, VARCHAR or CHAR, it should be a mandatory column.
Rationale Mandatory columns have better performance because searches for the null value are not necessary. Also programming for this column is easier because the programmer does not need to make exceptions when the value is null.
This would argue for all columns to be mandatory, but only columns with an enumerated varchar2 domain have the possibility to become mandatory by adding another allowable value which indicates that the column has no value.
Example If you have an optional column in a domain with the allowable values OPEN and CLOSED, you can make it a mandatory column by adding an allowable value UNKNOWN (and setting the Optional property to No).

Exception: If the column represents a subtype attribute, the column can not be mandatory.
Columns - Uppercase ?
OMG-10062: Define a column that is linked to a domain with only uppercase values, as uppercase.
Rationale This makes manual entering of data more user friendly.

Columns - Default Value
OMS-61568: Set the default value of a column to the default value of the corresponding attribute.
Rationale Otherwise, a discrepancy between analysis and design is introduced.

Exception: If the column represents a subtype attribute which has a default value, the column should not get a default value but the default should be implemented by a business rule which applies only to that specific subtype.
Columns - Initial Volume
OMS-42276: Define the initial volume for each table column; 100% for non-optional columns.
Rationale This is required to perform proper sizing.

Columns - Final Volume
OMS-45005: Define the final volume for each table column; 100% for mandatory columns.
Rationale This is required to perform proper sizing.

Columns - Display Type
OMG-10027: Use radio group only when two to four values are applicable, and the list will be static throughout the life of the product.
Rationale Using too many values is an inefficient use of the screen space. If the list is not static throughout the life-cycle of the product, this would mean that the screens using the radio groups would need to be upgraded for every addition or change to the values.

OMG-10028: Use pop list only when two to twenty values are applicable, and the list is never expected to grow beyond twenty.
Rationale Having too many entries in the pop list makes it more difficult for the user to find and select the appropriate values. For example, using a list of values instead, makes it possible to enter search criteria.

OMG-10029: Avoid using combo boxes.
Rationale When the domain is not fixed, it is recommended to use reference tables.
Also, the combo boxes are not supported in Web applications.

OMS-58107: Use check box when only one value out of Yes or No is applicable, and the Yes/No statement is not contrived or obscure.
Rationale In this way you ensure that it is obvious what the unchecked value means.
Example Do use
(x) Allow Override
(x) Receipt Required

Do not use
(x) Male (contrived to force Male/Female to a yes/no response)

Do not use
(x) Root Menu (opposite of root menu isn't obvious)

In each of these 'Do not use' cases, a radio group would present a more intuitive set of choices to the user.

Columns - Format Mask
OMS-61616: Do not use the Format Mask property for Date or Datetime columns.
Rationale If you entered a value here, it would be used as the default for the Display Format of Data Bound Items based on this Column. OMS-61627 specifies that this item property must not be used, so to prevent having to delete the value, it is better to not specify it for columns as well.

Columns - Prompt
OMG-10030: The first letter of any word in a prompt is capitalized, except for prepositions and other small words, such as and and or.
Rationale This is a commonly used standard in window applications. By using this standard your application becomes more consistent with the look and feel of other applications.
Example Name of Mother:
Telephone Number:

Form Generator: You can specify post-field prompts by placing a post-prompt marker in the prompt text. The value for the post-prompt marker can be set by preference ITMPPM. You can specify multiple line prompts by placing a split-prompt marker in the prompt text. The value for the split-prompt marker can be set by preference ITMSPM.
OMG-10031: For check box columns, use a question that can be answered with Yes or No.
Rationale This property is used as the default for bound items. This question form makes it easier for the user to decide whether to check or uncheck the check box.
Example Vegetarian?

Columns - AutoGen Type
OMS-42282: Only use the AutoGen Type types Date Created, Date Modified, Created By and Modified By in addition to server-side implementation when these fields need to be displayed in the generated form. When using the autogenerate field types, check the Record Time on Change History Columns check box on General Generator Options dialog to include the time. Set the Display Datatype of the associated columns to Datetime.
Rationale If you use these types the Form Generator creates code in all related forms to populate the journal table. However, the nature of such data auditing makes server-side implementation, using database triggers, imperative. In case of a server side implementation data auditing also takes place when data is inserted or updated in other ways than by using a form, for example, in case of batch modules.

Reference: Refer to the CDM Standards and Guidelines Library, Volume 2, Design and Generation of Multi-Tier Applications, Chapter "Logical Database Design", in the section entitled, "Data Auditing", for more information.
OMS-61533: When using an AutoGen type, then set Server Derived to Yes.
Rationale Derivation is by nature something done by the server. The derivation also takes place when data is inserted or updated in other ways than by using a form, for example, in the case of batch modules.

Columns - Source Attribute
OMS-61570: For each column that has a relationship with a source attribute, this relationship must be recorded.
Rationale When the definition of an attribute changes, these changes must be propagated to the associated column(s) and visa versa. Recording the relationship makes this easier. Normally columns without a relationship with an attribute are introduced for technical reasons.

Columns - Suggestion List ?
OMS-60024: Set the Suggestion List property to Yes if you use combo box as the display datatype for the column.
Rationale This ensures that the Oracle Form Generator generates a combo box as the display type.

Form Generator: If this property is unchecked while the display datatype is combo box, the Forms Generator issues a warning stating that the combo box list style is invalid, and ignores the display datatype.
Columns - Validation Error Message
OMS-61538: Do not record the actual message text in the validation Failure Message. Instead use a message code that refers to the message text in a message table.
Rationale This offers you the flexibility to easily change message text online and implement multi-lingual messaging.

Form Generator: If the column has a domain with allowable values, and you use a text item (with LOV) to represent the column, this message code is used when the user enters an invalid value.
Generation: The Headstart Template Package provides a complete messaging environment. See the Headstart Template Package User Guide for more details.
Columns - Hint
OMG-10032: Do not include key stroke references in the hint text.
Rationale This is used as a default for item hint texts, and OMG-10083 states that they should not include key stroke references.
Example Do not use: Hit F9 for a List of Values.

OMS-42299: Do not include the allowable values for an item in the hint text.
Rationale The hint text of a column is used as the default for the hint text of a corresponding item, and OMS-61628 says the same about item hint texts.

Attention: Where the column is derived from an attribute, the hint text is inherited from the Attribute Comments. Change the hint text, if necessary.
Columns - Comment
OMS-60023: If the column has a Derivation Expression which implements a business rule, you should record a reference to this rule here.
Rationale Referring to the related business rule makes it obvious to all team members that the business rule is implemented by this derivation expression.
Example BR_EMP012_TPL

Attention: By applying a pre-defined syntax for this reference, you can create your own user-defined cross-reference report based on the repository tables
Columns - Description
OMS-42278: Describe the purpose of the column if this is not clear from the column name itself.
Rationale This makes it easier to all team members to understand what the column is about.

Columns - Where/Validation Condition
OMS-45010: Do not use the Where/Validation Expression on the column level. Instead, define any Where/Validation Expression on the table level.
Rationale A validation that can be entered here, can be entered by using a check constraint. You should record your validations consistently throughout the application (that means as a check constraint). Also the where/validation condition on column level is ignored by the Server generator. A check constraint is not.

Columns - Notes
OMS-60014: Columns that are optional and where a null value has a different meaning than value unknown, should have a short column note that explains the meaning of a null value occurring for that column.
Rationale This prevents misinterpretation of data.

Warning: Do not confuse this with the Null Value property. The Null Value property indicates which value should be stored if the column has no value. This is meant for non-Oracle databases. Note: This value is not used by any generators.
Primary Keys - Name
OMS-42113: Name primary key constraints using the convention
[application code]_[table/view alias]_PK
Rationale This prevents naming conflicts on the server. In addition it makes it easy to determine what kind of constraint it is, and to which application and table it belongs.
Example AIF_LOC_PK

AIF -- application code
LOC -- table/view alias
PK -- indicates the primary key constraint

Primary Keys - Complete ?
OMS-60028: For tables, set the Complete Flag to Yes.
Rationale The Complete property indicates that the constraint is ready to be created by the Generate Database from Server Model utility.

Primary Keys - Enable ?
OMS-45012: For tables, set the Enabled property to Yes.
Rationale Only in this way the primary key constraint will be enforced.

Primary Keys - Validate in
OMG-10041: Set the 'Validate in' property in the to Both or Server for primary keys of a table.
Rationale In this way, you ensure that the primary key constraint always is validated, and not only when a record is manipulated through a form. Having the constraint validated on the client in addition to the server provides the end user with immediate feedback, rather than having to wait until the record is committed to the database.

OMG-10071: For views, set the Validate In property for Primary Key Constraints to Client or Both.
Rationale It is meaningless to set the Validate In property to Server as it is not possible to create Primary Key Constraints on views in the database.

Attention: You can choose one of the following approaches:
  • Set Validate in property to 'Client'. This prevents the ORA error when the constraint on the underlying table violates in the server.
  • Set Validate in property to 'Both', and add code to the message handling package (set through preference MSGSFT) to trap the ORA error and replace it with a user friendly message. This technique has been applied in Headstart. It requires a message table that holds the name of the constraint, to be able to lookup the correct message that should be displayed when a server-side violation of a declarative constraint takes place.
Primary Keys - Error Message
OMG-10033: Do not record the actual message text in the Error Message property of the constraint. Instead use a message code that refers to the message text in a message table.
Rationale This offers you the flexibility to easily change message text online and implement multi-lingual messaging.

OMG-10037: For message codes, use a three-letter Application short name abbreviation followed by five numbers with leading zeros as appropriate.
Rationale This makes it easier to verify from which product (application) the message is originated, and provides a unique identifier that makes it easier to deliver proper application support. It is also consistent with the format used by the Oracle tools.
Example OMS-00004 (error message originated from the OMS application)

Unique Keys
OMS-60019: Columns in a unique key constraint should be either all defined as optional or all defined as not optional.
Rationale When mixing optional (NULL) and not optional (NOT NULL) values within a unique key constraint, you face problems when comparing the values of the unique key columns. NULL is seen as a true value when inserting or updating a record. You get a constraint violation when there exists another record with a NULL value for the same column, and the values of the other columns within the constraint of the records are identical.
On the other hand, when comparing records in select statements, the NULL value is seen as no value; when comparing a NULL value with another NULL value, these are not seen as identical.

OMS-60020: If the columns in a unique key constraint are defined as NULL, your application logic should enforce that for each row in the table either one of the following is true:
  • All unique key columns are null.
  • All unique key columns do have a value.
Rationale The ISO SQL92 standard states that if at least one unique key column value is null, the key value should never be treated as a duplicate. However, this is not supported by the Oracle Server.
Example Assume Table T with a unique key constraint on (C1, C2) and the following rows:

C1......C2
==========
1.......1
2.......2
null....1
1.......null
null....null
null....null

If you try to insert a row with the values (1, null) the Oracle Server returns an error message, indicating a duplicate key.

Form Generator: To generate the required application logic, set preference NIVVKC (Null is valid value in key constraint) to N and define the unique key constraint as Optional if all columns are defined as Optional.
Unique Keys - Name
OMS-42112: Name unique key constraints using the convention
[application code]_[table/view alias]_UK#
where # is a sequence number starting at 1, added to the constraint name to make it unique in case there are more unique key constraints defined for the same table.
Rationale This prevents naming conflicts on the server. It also makes it easy to determine what kind of constraint it is, and to which application and table it belongs.
Example AIF_LOC_UK2

AIF -- application code
LOC -- table alias
UK2 -- indicates unique key constraint and sequence number

Unique Keys - Complete ?
OMS-61535: For tables, set the Complete Flag to Yes.
Rationale The Complete property indicates that the constraint is ready to be created by the Generate Database from Server Model utility.

Unique Keys - Enable ?
OMS-45014: For tables, set the Enabled property to Yes.
Rationale Only in this way the unique key constraint will be enforced.

Unique Keys - Update ?
OMS-42118: Do not allow a unique key to be updated, if it is referenced by a foreign key.
Rationale Otherwise, a cascade update of the foreign key needs to be performed. The Oracle Server does not support a cascade update.

Unique Keys - Validate In
OMG-10044: Set the 'Validate in' property in the to Both or Server for Unique keys of a table.
Rationale In this way you ensure that the unique key constraint always is validated, and not only when a record is manipulated through a form. Having the constraint validated on the client in addition to the server provides the end user with immediate feedback, rather than having to wait until the record is committed to the database.

OMG-10073: For views, set the Validate In property for Unique Key Constraints to Client or Both.
Rationale It is meaningless to set the Validate In property to Server as it is not possible to create Unique Key Constraints on views in the database.

Attention: You can choose one of the following approaches:
  • Set Validate in property to 'Client'. This prevents the ORA error when the constraint on the underlying table violates in the server.
  • Set Validate in property to 'Both', and add code to the message handling package (set through preference MSGSFT) to trap the ORA error and replace it with a user friendly message. This technique has been applied in Headstart. It requires a message table that holds the name of the constraint, to be able to lookup the correct message that should be displayed when a server-side violation of a declarative constraint takes place.
Unique Keys - Error Message
OMG-10035: Do not record the actual message text in the Error Message property of the constraint. Instead use a message code that refers to the message text in a message table.
Rationale This offers you the flexibility to easily change message text online and implement multi-lingual messaging.

OMG-10039: For message codes, use a three-letter Application short name abbreviation followed by five numbers with leading zeros as appropriate.
Rationale This makes it easier to verify from which product (application) the message is originated, and provides a unique identifier that makes it easier to deliver proper application support. It is also consistent with the format used by the Oracle tools.
Example OMS-00004 (error message originated from the OMS application)

Unique Keys - Description
OMS-61010: For tables, document the reasons of the following deviations:
  • Validate in property is set to Client, Server or None.
  • The unique key constraint is not enabled.
Rationale Documenting these deviations help your team members understand the decisions that have been made.

Foreign Keys - Name
OMS-45019: Name foreign key constraints using the convention
[application code]_[table/view alias]_[ref table/view alias](_[logical name])_FK(_CG)
where ref table/view is the referenced table or view and the logical name is required when there is more than one foreign key constraint defined for the same referenced table or view. Use the same logical name as for the foreign key columns.

Suffix foreign key constraints that are only introduced for forms generation (that is, when they should not be created at server level), with _CG. This includes foreign keys of views.
Rationale This makes it easy to determine what kind of constraint it is, to which application and table it belongs, and which is the joined table. The logical name makes the foreign key constraint unique and should make it easier to see which relationship the foreign key constraint represents.
Suffixing it with _CG makes (automated) checking of the properties Complete?, Enable? and Validate In easier.
Example
AIF_LOC_PRS_FK
AIF Application code
LOC Table alias
PRS Table alias of joined table
FK Indicates foreign key constraint
AIF_CTR_ADR_BILL_FK
AIF_CTR_ADR_SHIP_FK
AIF Application code
CTR Table alias
ARD Table alias of joined table
BILL
SHIP		 Indicates Bill To relationship,
Indicates Ship To relationship
FK Indicates foreign key constraint
ADR_ID_BILL ADR_ID_SHIP Name of foreign key columns, see OMS-60013

Foreign Keys - Complete ?
OMS-61534: For foreign keys without the _CG suffix, set the Complete Flag to Yes. Foreign keys with the _CG suffix must have the Complete Flag set to No.
Rationale The Complete property indicates that the constraint is ready to be created by the Generate Database from Server Model utility. See also OMS-45019. If the foreign key is only introduced for generation purposes, it should not be created in the database.

Foreign Keys - Enable ?
OMS-45016: For foreign keys without the _CG suffix, set the Enable? property to Yes.
Rationale Only in this way the foreign key constraint will be enforced in the database. Foreign keys with _CG suffix are for Client Generation only (see also OMS-45019) and do not need to be enforced in the database, so the Enable? property does not matter for them.

Foreign Keys - Validate in
OMG-10043: If the Complete and Enable flags of the foreign key are both set to Yes, set the 'Validate in' property to Both or Server.
Rationale Foreign keys with Complete=Yes and Enable=Yes are enforced in the database.
By setting the Validate in property in this way, you ensure that the form knows that the database server will validate it and that the form does not need to validate it itself at commit time.
Having the constraint validated in the form in addition to the database (value: Both) provides the end user with immediate feedback, rather than having to wait until the record is committed to the database.

Note: If the foreign key complies to OMS-45016, OMS-45019 and OMS-61534, then this applies only to foreign keys without the _CG suffix.
OMG-10072: For foreign keys with the _CG suffix in the name, set the Validate In property to Client or Both.
Rationale It is meaningless to set the Validate In property to Server as _CG foreign keys will not be created in the database (see also OMS-45019).

Attention: You can choose one of the following approaches:

  • Set Validate in property to 'Client'. This prevents the ORA error when the constraint on the underlying table violates in the server.

  • Set Validate in property to 'Both', and add code to the message handling package (set through preference MSGSFT) to trap the ORA error and replace it with a user friendly message. This technique has been applied in Headstart. It requires a message table that holds the name of the constraint, to be able to lookup the correct message that should be displayed when a server-side violation of a declarative constraint takes place.
Foreign Keys - Error Message
OMG-10034: Do not record the actual message text in the Error Message property of the constraint. Instead use a message code that refers to the message text in a message table.
Rationale This offers you the flexibility to easily change message text online and implement multi-lingual messaging.

OMG-10038: For message codes, use a three-letter Application short name abbreviation followed by five numbers with leading zeros as appropriate.
Rationale This makes it easier to verify from which product (application) the message is originated, and provides a unique identifier that makes it easier to deliver proper application support. It is also consistent with the format used by the Oracle tools.
Example OMS-00004 (error message originated from the OMS application)

Foreign Keys - Delete Rule
OMS-61614: Do not use the value Defaults for the Foreign Key Delete Rule.
Rationale This option only applies to database systems that do not understand Null values. The default value then is interpreted as 'no parent row exists'. In an Oracle environment, you should, in this situation, define the foreign key as optional and choose the Nullifies option (if you do not want a Restricted or Cascades option); This is more in line with relational database theory.

Foreign Keys - Update Rule
OMS-61615: Do not use the value Defaults for the Foreign Key Update Rule.
Rationale This option only applies to database systems that do not understand Null values. The default value then is interpreted as 'no parent row exists'. In an Oracle environment, you should in this situation define the Foreign Key as optional and choose the Nullifies option (if you do not want a Restricted or Cascades option); this is more in line with relational database theory.

Check Constraints
OMS-61573: Let a check constraint never implement more than one business rule at a time.
Rationale Maintenance of business rules becomes easier this way. For example, when a business rule becomes obsolete, you only have to remove the check constraint, instead of adjusting it.

Check Constraints - Name
OMS-42115: Name check constraints using the convention
[application code]_[table/view alias]_[logical name]
where the logical name is an indication of what the check constraint is about.
Rationale This makes it easy to determine what kind of constraint it is, and to which application and table it belongs.
Example
AIF_LOC_CHK_DATE
AIF application code
LOC table alias
CHK_DATE logical name
OMS_ACT_9_DIGITS
OMS application code
ACT table alias
9_DIGITS logical name

Check Constraints - Complete ?
OMG-10079: Do not create check constraints in the database (so set the Complete flag to No), implement the business rules in the rule layer (in the Custom API) instead. See the chapter CDM RuleFrame for more information.
Rationale
  • Usage of Check Constraints implies that no longer all rules are placed in the business logic layer. Check constraints are part of the data layer, which means that a fundamental principle of multi-tier architectures is violated: there is no longer a clear separation between presentation services, business logic and the data layer.
  • Role responsibilities in system maintenance are less clear. The data layer is usually the responsibility of the database administrator (DBA), the application developer is responsible for the business logic layer. Good coordination between both roles is required to effectively maintain the check constraints.
  • When all tuple rules are placed in the Custom API, they can be enforced at transaction level together with all the other rules. Although tuple rules could as easily be enforced at row level, this has the benefit that error messages of all violated rules can be presented at once to the user. A violated check constraint would abort the transaction right away, without the possibility of reporting back other rule violations.

OMS-60030: The Complete property should be No if the check constraint references pseudo columns or uses the function call construct.
Rationale Check constraints referencing pseudo columns or function calls cannot be implemented declaratively in the server.
Example Pseudo columns are sysdate, user.

Check Constraints - Validate In
OMS-45017: If the check constraint must not be included in your Forms (or other client level modules), set the Validate in property to Server.

If the check constraint must be included in your Forms, set the Validate in property to either Client or Both. Use Both if the corresponding business rule is also implemented in the rule layer or data layer.
Rationale By following this standard, your repository definitions are in synch with the actual implementation.
Also, if the corresponding business rule is implemented in the rule or data layer as well as in Forms, the Form Generator will know that it does not need to check this rule at commit time.
Example Check constraints always represent a business rule.

If the rule is only implemented in your Forms (or other Client level modules), set Validate in to Client.

If the rule is only implemented in the Rule or Data Layer (for example in a database check constraint, a database trigger, the Table API or the Custom API), set Validate in to Server.

If the rule is implemented both in your Forms and in the Rule or Data Layer, set Validate in to Both.

Attention: A utility could be created that takes the code in the check constraint, and implements it in the rule layer (in the Custom API). This utility would do so for check constraints with Validate in set to Server or Both. This would include function call check constraints, but if the function does not exist in the database this would go wrong. For those check constraints you should set Validate in to Client and set the VLDTTC generator preference to 'F' (Field) to make sure that the function is not called at commit time. By the way, if you use this utility, all check constraints should have the Complete property set to No, to prevent them from being created in the data layer.
Check Constraints - Error Message
OMG-10036: Do not record the actual message text in the Error Message property of the constraint. Instead use a message code that refers to the message text in a message table.
Rationale This offers you the flexibility to easily change message text online and implement multi-lingual messaging.

OMG-10040: Use the following convention for message codes: [application code]-##### where the application code consists of 3 letters and ##### means 5 numbers with leading zeros as appropriate.
Rationale This makes it easier to verify from which product (application) the message is originated, and provides a unique identifier that makes it easier to deliver proper application support. It is also consistent with the format used by the Oracle tools.
Example OMS-00004 (error message originated from the OMS application)

Check Constraints - Comment
OMS-60021: If the check constraint implements a business rule which is recorded during requirements modeling, you should record a reference to this rule here.
Rationale Referring to the related business rule makes it obvious to all team members that the business rule is implemented by this check constraint.
Example BR_EMP015_ATT
BR_EMP107_TPL

Reference: Also refer to the standards on labeling functions that represent business rules.
Attention: Because this reference always starts with BR_, you can create your own user-defined cross-reference report based on the repository tables.
OMS-60022: If the Comment field does not contain a reference to a business rule, you should describe in natural language the validation performed in the check constraint.
Rationale This makes it obvious to all team members what is implemented by this check constraint.

Check Constraints - Where/Validation Condition
OMG-10063: When comparing two columns with <, <=, >, >= use the following construction:
column1 < column2
or column1 is null
or column2 is null
even when both columns are mandatory.
Rationale This prevents that the check constraint needs to be altered when one of the columns becomes optional at some point in the future. The optionality of a column is already controlled by the NOT NULL constraint. Also without the extra or-statements, Forms raises a warning when only one of the values is entered.
Example startdate < enddate
or startdate is null
or enddate is null

OMG-10064: When a business rule is only applicable to one subtype in case of a super/subtype mapping to one table, the corresponding check constraint should always take the discriminator column (_TYPE) into consideration. Exceptions should always be documented in the table notes.
Rationale Otherwise, business rules become applicable to subtypes while this is not intended.
Example In this example a dot (.) represents a space.

(..ads_type <> 'PAS'
or ( -- check that applies to the subtype PAS only
...)
)

OMS-42154: Enclose each separate column-level constraint between brackets. With multiple column-level constraints, place AND and OR on a separate line. Use extra brackets to separate OR conditions from AND conditions.
Ensure that the opening bracket and the closing bracket are on the same line or are aligned with the logical operators.
Rationale By aligning code in this way it becomes easier to read and understand, and also fewer errors are made with the number of brackets.
Example In this example a dot (.) represents a space.

(..(...pay_mode = 'AUT'
...and adm_period = 'MND'
...and ( ao_preferred_date between 1 and 28 )
...)
or ( ao_preferred_date is null )
)

Attention: The syntax rules of a check constraint are the same that apply to the WHERE-clause of an SQL statement, performing a SELECT on the table to which the check constraint belongs. This means a check constraint can reference columns of the table to which it belongs, constants, or pseudo columns (for example, sysdate, user, etc.). All SQL-functions, such as to_char, max, truncate, etc., are allowed, except for the Decode function. The Forms Generator actually validates the syntax by executing such an SQL statement, for example:

select ?1?
from emp
where

Specify the where/validation condition as the correct situation, not the fault situation. Example:

Select ?1?
from emp
where nvl(enddate, hiredate) >= hiredate
Relation Indexes
OMS-42302: Index all foreign keys, unless
- you predict that the index will not deliver any performance gain or
- you predict that maintaining the index will create unacceptable overhead, or
- there is already an index created over the foreign key as part of a larger index, or
- when the foreign key is a dummy foreign key created for generation purposes only.
Rationale This improves performance for range scans, especially in master-detail situations. In addition it prevents locking problems as only the index entries are locked when manipulating a master record, while when no index is defined the entire detail table is locked.

OMS-42304: Index those columns that you reference frequently in WHERE clauses.
Rationale This improves the performance of your queries.

Relation Indexes - Name
OMS-42303: Name the index for a foreign key identical to the foreign key itself.
Rationale This makes it obvious for which foreign key the index is created.

OMS-42307: Name any other index created for the purpose of speeding up database operation using the convention,
[application code]_[table alias]#
where # is a sequence number beginning with 1, added to the index name to make it unique in case there are more indexes defined for the same table.
Rationale This makes it immediately visible to which application and table the index belongs.

Relation Indexes - INDEX_TYPE
OMS-42306: Do not define unique indexes. Define unique key constraints instead.
Rationale When defining unique constraints, a unique index is automatically created for you. You can also enable and disable constraints, but not separately create unique indexes. This is needed when deferred checking is required.

Index Entries - Usage Sequence
OMS-61585: Use the same sequence of the columns within an index as used for the corresponding key constraints definition.
Rationale This prevents confusion.

Database Triggers - Name
OMS-61620: Name an INSTEAD OF trigger using the convention [application code]_[view alias]_IO .
Rationale This makes it visible to which application and view the trigger belongs.
Example HSD_V_EMP_IO

Database Triggers - PL/SQL Definition
OMS-45024: The name of the PL/SQL definition for a trigger should be the same as the name of the trigger itself.
Rationale This makes it easier to quickly locate the belonging trigger module in the repository.

Database Triggers - Complete ?
OMG-10080: Do not create your own triggers in the database (so set the Complete flag to No), implement the business rules in the rule layer (in the Custom API) instead.
Rationale In the past, rule validation logic was often placed into database triggers, or database triggers directly called individual rule validation program units. This usage of database triggers does not fit in the CDM RuleFrame. All rule validation logic is centralized in the CAPI, and invocation of the rule program units is centralized in the TAPI.
Only the TAPI database triggers, which can be generated from Designer just like the TAPI itself, might play a role in calling the TAPI procedures.

Reference: See the chapter on CDM RuleFrame in Volume 2 of the CDM Standards and Guidelines Library.
Relation View Usages - Alias
OMS-60033: Use the same alias as used for the underlying table itself. If a table is used more than once in the view definition, add a sequence number to the table alias.
Rationale It would be confusing if other aliases are used, especially if the alias match another table alias. Using the same alias prevents this from happening. In addition, this makes it easy to see on which table the view is based.

Table Entity Usages - Discriminator Column
OMS-60010: If the table implements more than one (subtype) entity, record the name of the discriminator column in the supertype Entity Usage ('Mapped to Entity').
Rationale This makes it immediately visible which column is the indicator, to which subtype each entry of that table belongs and which entity is the supertype.

Note: This element type can only be seen and entered through the Repository Object Navigator (RON), under the Usages node of the Table Definition.
Database Design Transformer: When using the Database Design Transformer for the super entity, this is done automatically for you.
Table Entity Usages - Discriminator Value
OMS-60011: If the table implements more than one subtype entity, record the entity short name in the subtype Entity Usages ('Mapped to Entity') as a discriminator value .
Rationale This shows for each subtype what is the value that indicates an entry of that subtype.

Database Design Transformer: When using the Database Design Transformer for the super entity, then the discriminator value is automatically created for both the subtype and supertype entity. It is recommended to remove the value for the supertype entity as every record should belong to one of the subtypes.
Note: This element type can only be seen and entered through the Repository Object Navigator (RON), under the Usages node of the Table Definition.
Cluster Definitions - Name
OMS-42150: Name clusters using the convention,
[application_code]_CLUSTER#
where # is a sequence number beginning with 1, added to the cluster name to make it unique in case there are more clusters defined for the same application.
Rationale This makes it obvious to which application the cluster belongs.

Sequence Definitions - Name
OMS-42353: Name a sequence that generates a value for exactly one column in a table using the convention
[application code]_[table/view alias]_SEQ#
where # is a sequence number starting at 1, added to the sequence name to make it unique in case there are more sequences defined for the same table.
Rationale This prevents naming conflicts and makes it easy to determine which application and table use the sequence.
Example OMS_CUS_SEQ1

OMS-42354: Name a sequence that generates a value for multiple columns using the convention:
[application code]_[logical name]_SEQ
where logical name refers to the use of the sequence.
Rationale This prevents naming conflicts and makes it easy to determine what is the usage of the sequence and to which application it belongs.
Example OMS_TRANSACTION_ID_SEQ

OMS-61603: Only use alphanumeric characters and '_' in sequence names.
Rationale Names of objects in the Oracle Server can contain only alphanumeric characters from the database character set and the characters _, $, and #. Use of $ and # can lead to interpretation conflicts when using certain tools to access the data.

Sequence Definitions - Code Control ?
OMG-10046: Set the Sequence Type to Oracle Sequence only if gaps in the sequence numbering are allowed. If gaps are not allowed, you should set this property to Code Control Sequence.
Rationale The Oracle Sequence option allows gaps, so when this is not allowed you must use the Code Control Sequence.

PL/SQL Definitions
OMS-45033: For packages, include in the package specification, at the top of the PL/SQL text, a header with the following items:
  • Purpose of the package
  • Usage of the package
  • Remarks
  • For each public routine, an explanation of parameters used, a purpose, usage and remarks

    Include in the package body, at the top, a header with the following items:
  • Revision history including the date, who performed the change, what has changed and the construct. The newest entries should be at the top. The first (oldest) line should state 'Creation' in the appropriate language.
  • For each private routine, an explanation of parameters used, a purpose, usage and remarks

    For independent PL/SQL routines (that is those not included in a package), add at the top of the PL/SQL text a header with the following items:
  • Purpose of the routine
  • Purpose and Use of each argument
  • Remarks
  • Revision history including the date, who performed the change and what has changed. The newest entries should be at the top. The first (oldest) line should state 'Creation' in the appropriate language.
Rationale In this way, it is properly documented what is the purpose of the routines. This makes it easier during development and maintenance.

Keeping track of what is changed can explain a lot of what happened during the Development. Questions such as, "Why was this changed and who did it?" are easily answered.
Putting new entries on top gives quick insight into the latest change.
Example Package Specification
========================
package my_package
is
/*************************************************************************************
Purpose:..Package for file IO. A package level description of the purpose of the package.

Usage:....Explaining the usage of the package

Remarks:..Package level remarks.
*************************************************************************************/
--
-- public program units
--

procedure open_file
( p_file_name in varchar2 -- Name of file (including path)
);
-- Purpose: Opens file p_file_name. When the file does not exist the file is
-- created. When the file exists, the file is opened and the position
-- is set to end of the file. A program unit level description of the
-- purpose of the program unit.
--
-- Usage: p_file_name must be a valid path and file name. The usage of the
-- program unit.
--
-- Remarks: Any remarks, including known limitations and enhancements, for this
-- program unit.

end my_package;

Package Body Specification
=============================
package body my_package
is
/*****************************************************************************************
Revision History

When.........Who............................Construct
..Revision...What
31-12-1980...Richard Voss...................wrap_line_when_too_long
..1.1........Added rendering algorithm, using a variation of classic mid-point
.............displacement.
01-01-1978...Allan Turing
..1.0........Creation
*****************************************************************************************/
--
-- private program units
--

procedure do_something
--
-- Purpose A description of the purpose of this private program unit.
--
-- Usage -
--
-- Remarks Any remarks, including known limitations and enhancements.

is
begin
null;
end do_something;

end my_package;

Server Generator: The text entered here is used by the Generate Database from Server Model utility to generate create statements for stored PL/SQL objects.
PL/SQL Definitions - Short Name
OMS-61543: Use as Short Name the same name as the Implementation Name of the PL/SQL Definition, prefixed with the Package name if the PL/SQL Definition is included in a package.
If the result is too long for the Short Name (max. 20 characters), then shorten the name using the following algorithm: Remove every second and following vowels of a word, starting at the last word and moving forward, until the name is short enough. Well-known abbreviations might be used as an alternative, such as for example NO for NUMBER or QTY for QUANTITY. These abbreviations must be recorded as business terminology.
Rationale This makes it easier to recognize by Short Name to which PL/SQL definition a function/procedure belongs.

PL/SQL Definitions - Name
OMS-61540: For Custom API (CAPI) packages, use the following naming convention:
[application code]_[table alias]_CAPI

For other packages, use the naming convention
[application code]_[logical name]
Rationale Starting with the application code makes it easy to see to which application a generated package belongs.
A CAPI package is always for one table, so adding the table alias makes it easy to see to which table the CAPI belongs. Suffixing it with CAPI leaves the possibility for creating another package called [application code]_[table alias] for procedures and functions that are not related to business rules.
Example HSD_EMP_CAPI
HSD_UPLOAD_EMP

Attention: When using one procedure or function from a package, the whole package is loaded into the SGA. When a package grows very large, this may cause problems. In these situations, you should split the packages, and include a sequence number at the end of the name.
Reference: For more information about the CAPI, see the chapter on CDM RuleFrame in Volume 2 of the CDM Standards and Guidelines Library.
OMS-61542: For procedures and functions use the following naming convention:

For packaged procedures and functions (when defined in separate PL/SQL Definition):
[package name].[action indicator]_[logical name]

For standalone procedures and functions:
[application code]_[action indicator]_[logical name]

The action indicator is used to indicate the purpose such as for example:
CHK when the module is a function that performs a simple check
GET when the module is a function returning a value that is derived from the database
SET when the module is a procedure setting a value in the database
Rationale Procedures and functions might be called from different modules, and therefore should not be named after the module. The action indicator is standardized to name similar procedures or functions in the same structured way. A logical name is used to express the purpose of the PL/SQL Definition.
All names should start with the application code to be able to easily identify in the database all objects belonging to an application.
Example Packaged functions/procedures:
MAS_DATES.GET_DAY_OF_WEEK

Standalone procedures/functions:
MAS_GET_APP_TITLE

Exception: Procedures and functions that represent a Custom CAPI Service seem to be a standalone procedure/function, but actually they are included in the CAPI Package. For those program units, the following naming convention is suggested: CS_[table alias]_[logical name] (for example: CS_ORD_GET_PRICE) .
OMS-61558: The name of the PL/SQL definition for a trigger should be the same as the name of the trigger itself.
Rationale This makes it easier to quickly locate the belonging trigger definition in the repository.

PL/SQL Definitions - Implementation Name
OMS-61547: Use the lowercase of the PL/SQL Definition name for the implementation name, except for packaged PL/SQL Definitions. Remove the package prefix on these.
Rationale Using the same implementation name as the name makes it easier to recognize the link between the repository definition and the physical database object.
Example Name: MAS_DATES.GET_DAY_OF_WEEK
Implementation Name: get_day_of_week

OMS-61604: Only use alphanumeric characters and '_' in implementation names of PL/SQL Definitions.
Rationale Names of objects in the Oracle Server can contain only alphanumeric characters from the database character set and the characters _, $, and #. Use of $ and # can lead to interpretation conflicts when using certain tools to access the data.

PL/SQL Definitions - Return Type (Scalar)
OMG-10066: Do not make functions return a Boolean, but instead a TRUE or FALSE as a varchar2.
Rationale In this way the functions can be used in SQL statements (in a select statement no Boolean values can be selected).
Example function oms_get_query_allowed
(p_user_name in varchar2
) return varchar2
is
begin
if oms_get_query_allowed(p_user name)
then
return('TRUE');
else
return('FALSE');
end if;
end oms_get_query_allowed;

Suggestion: In case of a function that returns a Boolean, a ?wrapper function? can be created that calls the Boolean function and has the same name but returns a varchar2. This wrapper function can not have a Boolean input parameter, so you can not use a generic wrapper but you must use a specific wrapper for each Boolean function you want to use in SQL statements.
Modules - Short Name
OMS-44152: Choose a naming convention for modules where the name should at least start with the application code and will not be longer than 8 characters. The only exception is for Query Only modules, they can have a suffix _QO on top of the 8 characters.

The following two standards have been used successfully in projects:

1. [application code]####[module type indicator](_QO)

where #### is a sequence number starting at 0001, to make the module short name unique.

2. [application code][table/view alias]#[module type indicator](_QO)

where the table/view alias is the alias of the 'most important' table or view that is used. In the case where more than one base table is used the following strategy is used to choose the table/view alias:
Use the first table usage with an insert, update or delete usage.
In case of a query only form or a report, use the first table usage.
In both standards the module type indicator is used to indicate the type of module as follows:
indicator module type
F Form
R Report
M Menu
U Utility
L Library
W Web Server Generator
Rationale The application code makes it immediately visible to which application the module belongs. This is especially useful in multi-application environments. The module type indicator makes it easy to recognize the type of module.
The module names are kept at 8 positions, to prevent problems with file servers that do not support long names. Query only modules are allowed a longer name because they are usually dummy modules that are not generated.

The benefit of using the first approach is that you do not have to spend time in finding meaningful names for the modules, but especially with a large number of modules it will be difficult to remember what every module is about.
The benefit of using the second approach is that it is immediately visible what is the main base table or view in the module, and thereby it is easier to remember what the module is about. The limitation is that it is only possible to define a maximum of 10 modules with a specific table as the main base table. This might not be sufficient, especially for large application systems. This problem may be avoided by using the alphabet after the tenth module.
Example Example first standard
MAS0076F Forms module
MAS0077R Reports module

Example second standard
MASEMP1F Forms module
MASEMP1L Library module
MASDEP1R Reports module

OMS-61539: If a library module is the module specific library for another module, then give this module specific library the same sequence number as the form module to which it belongs.
Rationale In this way it is easy to recognize to what forms module the library belongs.
Example Example linked to first standard
MAS0076F Forms module
MAS0076L Module specific library for MAS0076F

Example linked to second standard
MASEMP1F Forms module
MASEMP1L Module specific library for MASEMP1F

Modules - Name
OMS-44151: Define module names using logical names, with only alphanumeric characters, spaces and '-'.
Rationale This makes it easier to quickly understand what the module is about. In this way the module name can look similar to the first window title (see OMG-10051).
Example BOOK SEATS
NEW MEMBERS

Modules - Implementation Name
OMS-45029: Set the implementation name to the value of the module short name in lowercase.
Rationale Using the same implementation name as the short name makes it easier to recognize the link between the repository module and the physical module.
Using lowercase prevents problems on case sensitive environments, like UNIX environments.

Modules - Short Title
OMS-43309: When the module is used in a menu or in a button calling the module, then the Short Title must consist of a one or two-word expression. The preference is a one-word expression of the following form:
[noun]
If a two-word expression is used, it should have the following form:
[verb noun]
If any abbreviations are used, these must be self-explanatory.
Rationale In this way, a short and meaningful indication of the module usage is provided. This is useful as the Short Title is used on menus, buttons, and hyperlinks generated to navigate to this module.

OMS-60058: Provide each module that is used in a Forms menu with a mnemonic access letter, which is unique within the sub-menu the entry belongs to.
Rationale Allows the user to choose between the keyboard and the mouse to perform their task.
Example E&xit -- Shown as Exit in generated menu

Exception: This standard does not apply to Menu Separator Modules, that is Modules where the Language property is empty.
Form Generator: The short title of screen and menu modules is used as the label for the menu entry in a pull-down menu. The mnemonic access letter can be indicated by placing the ampersand (&) in front. If no & is indicated, then the first letter becomes the mnemonic access letter.
Attention: The mnemonic access letter is currently only working for Client Server forms, not for Web Forms. However, this will be fixed in Developer 6i. Enforcing this standard now will avoid problems when upgrading to Developer 6i.
Modules - Runtime Path
OMS-42231: Do not specify a hardcoded Runtime Path. Define a set of logicals if supported.
Rationale This makes it easier to port the module from one environment to another.
Example %%AIF_SRCE%%

Modules - Source Path
OMS-44165: Do not specify a hard-coded Source Path.
Rationale This makes it easier to port the module from one environment to another.
Example %%AIF_SRCE%%

Modules - Help File Name
OMS-60101: Use the three-letter application code as the help file name when you use an application wide help file.
Use the module short name as the help file name when you use a help file per module.
Rationale This makes it easier to determine to which application and/or module the help file belongs when viewing the help files outside the context of the application.

Modules - User/Help Text
OMS-44162: For default modules (that is, not for libraries or menus), the user help text should at least contain a brief description of the module, summarizing the functionality.
Rationale The user help text is targeted towards the users of the application under development. This makes it possible for the users to understand what is the intended purpose of the module.
Help text for libraries or menus is generally not generated into the end user help system, so it is not necessary to include help text for these types of module.

Note: Copy the Function Description as the starting point for the User Help Text.
OMS-44166: Specific actions performed by the module must be presented here, in a form understandable by the future users of the application for assessment purposes.
(Later on, the user help text can be finalized and refined by a technical writer to make it suitable as online help text.)
Rationale The future users must be able to assess the usefulness and correctness of a module through the user help text.
Example By using this module, you can select a department, together with the employees working for that department. You can change the columns containing salaries and commission. You cannot enter or delete employees here.

Generation: Oracle Headstart provides guidelines on how to generate on line context sensitive help text. As an alternative to MS Help, also HTML Help can be generated using the Headstart Utilities. View the Oracle Headstart User Guide for more information.
Modules - Notes
OMS-60064: Revision History of a Module (and its subobjects like module components and items) must be recorded in the Notes, stating the date of creation, the date and the reason for the change, and the change itself.
Add new entries on top.
Rationale Keeping track of what is changed can explain a lot of what happened during the Development. Questions such as "Why was this changed and who did it?" are easily answered. It is advised to also record new entries when the deliverable is offered to the client for approval. Putting new entries on top gives quick insight into the latest change.
Example REVISION HISTORY
Label Date Name Description
1.1 06-01-1999 L. Jellema Lookup table usage L_VER added
1.0 06-07-1998 L. Jellema Initial Creation


1.1
----
Added lookup table usage L_VER in Module Component PRD to enable users to select existing versions for the Product.

1.0
----
Initial Creation

Modules - Description
OMS-61544: For modules that are generated using Oracle Generators, the module description should contain
  • a detailed list of the post-generation activities performed (if any) including a reason
  • module specific generator settings
  • special constructions required, for example work arounds for bugs
  • special template/library objects used
Rationale This makes it possible for the team to reproduce a module with post-generation steps whenever required. In addition it is clear for the developer why the post-generation steps were required, as well as why specific generator settings were required for the specific module.
Example POST-GENERATION ACTIVITIES
Activity - Reason
-------------------------------------------


GENERATOR SETTINGS
Activity - Reason
-------------------------------------------


SPECIFIC CONSTRUCTIONS
Activity - Reason
-------------------------------------------


SPECIFIC TEMPLATE/LIBRARY OBJECT USAGES
Activity - Reason
-------------------------------------------

OMS-61545: Do not include functional description in the module description when the module implements a business function.
When the module does not implement a business function, but has user interaction, then functional description should be provided in the User Text.
For other modules, functional description should be documented in the Module Description.
Rationale This prevents duplication of data, and therefore decreases the risk of inconsistencies.

Application Design Transformer: When you run the Application Design Transformer, the Function Description is copied into the Module Description. Cut and Paste this text into the User Help Text property to provide a good starting point for the user help.
Modules - User/Help Text
OMS-61546: When the module does not implement a business function, but has user interaction, then the functional description should be provided in the User Text, and not in the Module Description.
Rationale This prevents duplication of data, and therefore decreases the risk of inconsistencies.

Arguments
OMS-61631: Do not use the Allowable Values for Arguments.
Rationale The use of domains is preferred. This allows for reuse. If no suitable domain exists, consider creating one.

Arguments - Name
OMS-44350: Name parameters using the convention, p_[logical name] .
For parameters that are related to a database column, the logical name element of the parameter should include the column name.
Rationale This makes it easily visible that it concerns a parameter, and also what kind of information the parameter carries. When the parameter refers to a column normally the logical part of the name should be equal to the column name, but when you have more than one parameter referring to the same column, some kind of qualifier is also needed in the name.
Example p_airport_code
p_civil_state
p_old_civil_state

Arguments - Input Output
OMS-44354: For PL/SQL Definitions, always specify whether a parameter is IN, OUT, or IN/OUT (modify).
Rationale When it is not explicitly defined, the generator uses IN as default. This is not obvious when viewing the argument when this has not been explicitly defined.

Arguments - Position
OMS-44352: For PL/SQL Definitions, always specify a sequence indicating the position of the parameter when calling the routine.
Rationale This ensures that the parameters are generated in the order as expected, and thereby there is no risk for confusion when positioning the parameters when calling the module.

Arguments - Datatype
OMS-44353: For Forms and Reports Modules, only use the following datatypes for arguments:
  • VARCHAR2
  • NUMBER
  • DATE
Rationale Oracle Forms and Oracle Reports only support the Datatypes Char, Number and Date for parameters.

OMS-61597: For PL/SQL Definitions where the argument is based on a column, use the PL/SQL construct [table name].[column name]%type .
Rationale In case the datatype or dimension of a column changes, you do not need to change the definition of the parameter in case the %type construction is used.

Exception: When the %type expression exceeds 70 characters (the maximum length of the property), use a datatype from the pop list.
Note: You do not have to choose a value from the pop list of this property, you can also type in your own value which does not need to occur in the pop list.
Arguments - Prompt
OMG-10047: The first letter of any word in a prompt is capitalized, except for prepositions and other small words, such as and and or.
Rationale This is a commonly used standard in window applications. By using this standard your application becomes more consistent with the look and feel of other applications.
Example Name of Mother:
Telephone Number:

Module Networks
OMS-61582: The module network should be complete. That is also including the calls from module specific forms libraries or PL/SQL blocks to other modules.
Rationale This enables better impact analysis.

Windows - Title
OMG-10051: If the form does not perform a specific task, then the title is the name of the first object displayed within the Window.

If the form performs a specific task, the title is of the form, [verb] [noun].
The verb may be a contrived or "marketing-ese" word. Avoid the terms Maintain, Update, Define or View, unless the terms substantially clarify the use of the forms that act on the same objects.

The Window Title should be plural where the number of records displayed is larger than one, otherwise the Window Title should be singular.
Rationale This makes the titles consistent with the users activities and perception
Example Titles for windows with no specific tasks:

Sales Orders
Journal Entries
QuickCodes
Periods

Titles for windows with specific tasks:

Transfer Items
Post Journals
Import Receipts

OMS-58505: If the context of the primary window is displayed in the secondary window, then use the following format:
[fixed window title detail window] ([context],[context],..) .
Rationale This makes it easier for the end user to remember to which master data the details belong when working within a detail window.
Example Journal Lines (NOV93.123)
Assignment: OrgA 12-JUN-1992 (John Doe)
Benefits (John Doe, Development)
Shipments (123, A27-K12)

Generation: Oracle Headstart contains a feature to easily generate window context. See the Headstart User Guide for more information.
Windows - Scrollable ?
OMS-60039: Set the Scrollable? Property to No.
Rationale Windows should show the full width and height of the content canvas. This is more user friendly as no items will be unexpectedly hidden for the user.

Windows - Width
OMS-61551: Use the Canvas width and height of the Module Component rather than the Window width and height in the Window object.
Rationale The Form Generator automatically sizes the window according to the content canvas width, which is specified through the width and the height of the first Module Component visible in the window.

All Module Components - Name
OMS-61549: When the Module Component has a base table usage that is based on a true table or a view (that is a table or view that is true to the business), then use the same Module Component name as the table alias for that table usage. If there is more than one Module Component within the same module with the same base table usage, express the purpose of the module component in the name with a few letters: [table alias base table](_[purpose module component])
Rationale In this way it is immediately obvious what the base table usage is of that Module Component. The Module Component name becomes the block name when it is generated, and thereby it is also immediately visible what the base table usage is of the block.
Example DEP
EMP_UNASSIGN
EMP_ASSIGN

OMS-61550: For reusable module components which have a base table, use the following naming convention: [application code]_[table/view alias]_[logical name]

For reusable module components without a base table, use the following naming convention: [application code]_[logical name]
Rationale This makes it easy to determine the usability of the reusable module component.

OMS-61700: When you create a subcomponent for a module component, name the subcomponent with the name of the parent module component plus a sequence number: [PARENT_MC]_S#, where PARENT_MC is the parent module component name and # is a sequence number beginning at 1.
Rationale This helps identify the relationship between the subcomponents and the parent module component.
Example Parent: EMP, Subcomponent 1: EMP_S1, Subcomponent 2: EMP_S2

All Module Components - Title
OMS-58044: Display Titles of Module Components containing a Table Usage of Base Usage Type are singular only if the user commonly works with a single instance of data within the block; otherwise they are plural.
Add a verb to the Display Title, only if it otherwise is not obvious to the user what is the usage.
Rationale This ensures that the block title is consistent with the users activities and perception.

All Module Components - Rows Displayed
OMG-10049: Determine a standard for the number of rows displayed for multi-record blocks
  • for the number of detail records in a window including both the master and detail block
  • for the number of records in window with only one block included
Determine a number of alternatives to choose from.
Rationale This ensures a consistent look throughout the application.

All Module Components - Overflow
OMG-10050: Limit the use of Spread Table as overflow style. Consider to use Tab Sheets. Use Overflow Area below or Overflow Area right instead.
Rationale The Spread Table is experienced to be less user friendly, as the user has to scroll back and forth to view data.

All Module Components - Width
OMS-60037: Use the Canvas width and height of the Module Component rather than the Window width and height in the Window object.
Rationale Form Generator automatically sizes the window according to the content canvas width, which is specified through the width and the height of the first Module Component visible in the Window.

OMS-60038: Make the Canvas size "just-to-fit".
Rationale The Window looks better with no large amount of empty spaces. Also using smaller windows enables the user to view more windows at the same time.

Module Component Table Usages
OMS-61618: If a lookup table has a surrogate primary key (a sequence without meaning to the end users), the items displayed in an LOV must include all columns in the unique key of the lookup table.
Rationale Surrogate keys are usually not shown to the end users, so for identification of a row of the lookup table, something else is needed. OMS-61617 already states that you need a Unique Key for this.
If you do not display all columns of the unique key in the LOV, it would be possible to have duplicate values in the LOV, and that makes it impossible to validate the foreign key from the display values.
Example If the table HSD_DEPARTMENTS has a surrogate key (the ID column), do not show that ID in the List of Values (it is meaningless to the user), but show the columns of the Unique Key (NAME and LOCATION).
There will never be two departments with the same combination of NAME and LOCATION, so the LOV will not show duplicate rows and the correct DEP_ID will be stored in HSD_EMPLOYEES.

Module Component Table Usages - Usage Alias
OMS-60103: Use the same alias for a base table usage as defined for the table or view on which the usage is based.
For a lookup table usage, use the following naming convention:
L_[table/view alias](#)
where the table/view alias is identical to the alias of the table or view on which the usage is based, and the # is a sequence number starting with 1. This sequence number is only required when there are more lookup table usages based on the same table or view within the same Module Component.
Rationale This makes it immediately obvious on which table or view the usage is based, and ensures uniqueness of table usages within one Module Component.

Module Component Table Usages - Where/Validation Condition
OMS-60040: Always enclose the WHERE clause in brackets when using the OR operator.
Rationale The Form and Report Generators sometimes add their own code to the generated WHERE-clause. Using the OR operator without brackets can lead to unexpected behavior of the query.

OMS-60067: If the Where Clause of Query implements a documented business rule, provide a reference to this business rule as a comment in the Where Clause of Query property.
Rationale This provides a reference for the team, and thereby makes it easier to understand why the where clause has been included.
Example /* BR_ORD023_RER */
code = 'PR';

Module Component Table Usages - Validation Where Clause
OMS-61552: If the Lookup Validation Where Clause implements a documented business rule, provide a reference to this business rule as a comment in the Lookup Validation Where Clause property. If the business rule is not yet described, do so in this description.
Rationale This provides a reference for the team, and thereby makes it easier to understand why the where clause has been included.
Example /* BR_EMP023_RER */
code = 'PR';

OMS-61598: Always enclose the WHERE clause in brackets when using the OR operator.
Rationale The Form and Report Generators sometimes add their own code to the generated WHERE-clause. Using the OR operator without brackets can lead to unexpected behavior of the query.

Items - Prompt
OMG-10052: The first letter of any word in a prompt is capitalized, except for prepositions and other small words, such as and and or.
Rationale This is a commonly used standard in window applications. By using this standard your application becomes more consistent with the look and feel of other applications.
Example Name of Mother :
Telephone Number :

Form Generator: You can specify post-field prompts by placing a post-prompt marker in the prompt text. The value for the post-prompt marker can be set by preference ITMPPM. You can specify multiple line prompts by placing a split-prompt marker in the prompt text. The value for the split-prompt marker can be set by preference ITMSPM.
OMG-10053: Button labels are always short and succinct.

Do use
Lines
Print
Margins
Apply Notes

Do not use
Enter Order Lines
Run Print Report
Set Document Margins
Automatically Apply Notes
Rationale The button size does not allow for long labels. Keeping the text concise makes it easier for the user to understand their purpose.

OMG-10054: Buttons that perform an action, but require some other piece of information from the user, always end with an ellipsis (...).

A button that is used to navigate to a detail block does not end with an ellipsis, even if the block is in a modal window. This is because the button action is to navigate, not to enter data in the destination block. The navigational action can be performed without the user supplying additional information.

Buttons that open a confirmation window, because they are potentially destructive or instigate irreversible actions, but do not require additional information, do not end with an ellipsis.

A button without an ellipsis may open a modal dialog box, but a button with an ellipsis must always open a modal dialog box.
Rationale This provides a consistent look and feel for buttons throughout the application, and thereby makes it easier for the user to understand the meaning of the button.
Example Choose Options...
Print...

Items - Width
OMG-10055: When the preference ITMBIF = Y (Base Item Sizes on the Font), a proportional font is used, and the preference TXTSCA = 20 (Scale Factor for DATE, NUMBER and Uppercase Items), then use the following guideline to set the width of an item:
Column Length Display Length
Non-GUI items 1 to 4 positions long Column length + 2
Non-GUI items 5 to 8 positions long Column length + 1
Non-GUI items more than 9 positions long Default column length, change only in case of character columns when necessary
Pop lists, combo-boxes and text-lists At least maximum column length + 4
Check boxes in a multiple record group 2
Check boxes in a single record group At least the length of the prompt + 3
Radio groups At least the maximum column length + 3
Pop list abbreviation, combo-boxes abbreviation, test-lists abbreviation At least the maximum abbreviation length
Pop list meaning, combo-boxes meaning, test-lists meaning At least the maximum meaning length
Current records indicators 1
Rationale All data should be visible on the screen when possible. You should reserve space for the list handler in case of list items.

Items - Format Mask
OMS-61627: Do not use the Format Mask property for Date or Datetime items.
Rationale This makes it possible to alter the date or datetime format without the need to generate the forms again, and to set the date(time) format dynamically, by using date related variables.
Use the following runtime registry settings for Forms:
- forms60_output_date_format
- forms60_user_date_format
- forms60_output_datetime_format
- forms60_user_datetime_format
- nls_date_format
If a forms date item is generated without a format, at runtime it uses the format setting forms60_output_date_format or forms60_user_date_format. If these are not set, it uses nls_date_format.
Because of this dynamic setup, changing the date format is simply changing the registry variable.

Warning: This only works if you also comply with OMS-61569.
Items - Default Value
OMS-45007: When a check box is defined as the display type, the default value must be set, unless the check box column is optional.
Rationale A check box can have only two values (checked or unchecked), it should not be 'undefined'. To the end user the difference between unchecked and 'undefined' is invisible.

Form Generator: When the check box column is optional, this implies that the domain must have only one allowable value. An optional column with more than one allowable value cannot be implemented as a check box.
OMS-61553: When a default value has been defined on table level, you must also define a default on module level.
Rationale The Oracle Server only applies default values not included in the insert statement. When a column is included in a form (even when it is non-displayed) it is always included in the insert statement, even when the value is NULL.

Items - Hint
OMG-10083: Do not include key stroke references in the hint text.
Rationale This is dependent on the individual keyboard mapping.
Example Do not use: Hit F9 for a List of Values.

OMS-61628: Do not include the allowable values for an item in the hint text.
Rationale Then the hint text must be changed when the allowable values are changed which is often forgotten, and the user should be able to see the allowable values anyway: for example in a pop list or list of values.

Bound Items - Name
OMS-61701: When naming a bound item in a subcomponent, include the subcomponent suffix in the item name: [ITEM_NAME]_S# where ITEM_NAME is the name of the bound item and # is the number of the subcomponent.
Rationale The parent module component and all subcomponents will be generated into a single block in the form. Each item in the block must have a unique name. If you do not give the items a unique name, Designer will create one for you. By using this convention, you know exactly which items go in which subcomponents. Programming with these items will be much easier and more clear.
Example Module Component: EMP, ID, NAME; Subcomponent 1: EMP_S1, ID_S1, NAME_S1

Bound Items - Display Type
OMS-61580: Never use a pop list or text list as the first item in a multiple record block.
Rationale Using a pop list or text list as the first item in a multiple record block makes navigation with the arrow keys more difficult.

Unbound Items
OMS-61630: Do not use the Allowable Values for Unbound Items.
Rationale The use of domains is preferred. This allows for reuse. If no suitable domain exists, consider creating one.

Module Function Usages - Function
OMS-44159: Define all business/system functions that are implemented by this Module or PL/SQL Definition, as well as business rules recorded as a function and implemented in this module.
Rationale This makes it easier to determine the impact when something changes to any of these.

Attention: Only include those Business Rules that are directly implemented by the module, that is that the functionality for the rule is physically programmed within that module (and not via a call to another module).
Note: This element type can only be seen and entered through the Repository Object Navigator (RON), under the Usages node of the Module or PL/SQL Definition.
Databases - Comment
OMS-42607: Enter a short sentence indicating the use of this database.
Rationale This makes it obvious what is the use of the database.

Oracle Databases
OMS-42640: Define at least the following tablespaces:
Tablespace Description
SYSTEM data dictionary segments only
TEMP temporary segments only
RBS rollback segments only
TOOLS general-purpose tools only
USERS default tablespace for users with resource privilege
Rationale This is according to the OFA standards.

Attention: The SYSTEM tablespace is automatically created on database creation.
Oracle Databases - Name
OMS-42600: Although the database name can be up to eight characters, you should limit its length to four characters.
Rationale This is the OFA standard for database names. It results in shorter paths for all datafiles.

Oracle Databases - Reuse Control File ?
OMS-42601: Do not reuse any existing control files.
Rationale In this way you avoid inadvertently losing a complete database by destroying a set of existing control files.

Oracle Databases - Max Data Files
OMS-42605: The maximum number of data files accessible to your instance is operating system specific. Enter the maximum number for your platform.
Rationale This allows for future growth.

Oracle Databases - Archive Redo Files ?
OMS-42603: Set the Archive Redo Flag for a database to Yes.
Rationale This enables point in time recovery and thereby avoids loosing data.

Oracle Databases - Max Log History
OMS-61590: Do not set the Max Log History of a database too low, use 100 at a minimum.
Rationale The longer the list, the longer you can perform automatic recovery.

Oracle Databases - Max Log Members
OMS-61591: Do not use more than 4 Max Log Members for a database.
Rationale Keeping more than 4 copies takes up too much space unnecessarily.

Rollback Segments - Name
OMS-42650: Name rollback segments using the convention
RBS##
where ## is a sequence number beginning with 01 to make the rollback segment unique.
Rationale In this way, it is immediately visible that the segment is a rollback segment.

Rollback Segments - Public ?
OMS-42651: Do not use public rollback segments.
Rationale With private rollback segments, it is always known which rollback segments are on-line at any time. When public rollback segments are used, they are randomly selected.

Rollback Segments - Tablespace
OMS-42652: Locate all rollback segments (with exception of the SYSTEM rollback segment) in a separate tablespace, called RBS.
Rationale This is according to the OFA Standards.

Rollback Segments - Optimal Size
OMS-45027: Define the optimal size of a rollback segment in bytes. This storage parameter is unique to rollback segments.
Rationale This is to ensure that rollback segments do not grow larger than necessary.

Rollback Segments - Comment
OMS-42654: Enter a short sentence indicating the use of this rollback segment.
Rationale In this way, it is obvious what is the usage of the rollback segment.

Database Users
OMS-44355: Do not grant access to individual users. Use roles instead.
Rationale Defining user roles provides a more flexible security system.

Note: This applies to both Database Object Privileges and Module Access.
Table Implementations - Storage Definition
OMS-61583: Do not use Storage Definitions for tables.
Rationale Using only the default Storage Definition of the tablespace eliminates the risk of fragmentation.

Table Implementations - Init Trans
OMG-10068: Use for Init Trans a default value of 2 for the table implementations. Do not deviate from this default unless you predict that the table will form a "hot spot", requiring many users to work on a small number of rows simultaneously.
Rationale This is based on best practices. Using this figure ensures in most scenario's that most net storage space is maintained. If the figure is set too low, you encounter locking problems.

Table Implementations - Max Trans
OMS-42126: Do not limit the Max Trans for a table.
Rationale Limiting the maximum number of transaction slots is hardly ever useful. Only if a large amount of transactions are expected once for a table, this might be useful to prevent that unnecessary space is taken by transaction slots. Transaction slots are never released for rows when they once have been used. If the figure is set too low, a higher amount of transactions are required than available transaction slots. This results in locking problems.

Table Implementations - Percent Free
OMS-42117: Specify values for both Pctfree and Pctused.
Rationale Otherwise, the default is used. The figure required is dependent of the DML behavior against that table, and should therefore explicitly be considered.

Suggestion: The figures 10% for Pctfree and 70% for Pctused have proven to be efficient for most tables.
Sequence Implementations
OMS-42352: Do not make maximum and minimum values larger than the length of the column for which the sequence is used.
Rationale This prevents attempting to enter a value in the column that is too large.

Sequence Implementations - Cycle ?
OMS-42357: Do not cycle sequences.
Rationale This may lead to unexpected Oracle errors due to duplicate values.

Cluster Implementations - Tablespace
OMS-42155: Assign a dedicated tablespace for each cluster.
Rationale This provides better space management.

Cluster Implementations - Storage Definition
OMS-42151: Do not enter a Storage Definition for clusters.
Rationale The storage definitions should be obtained from a dedicated tablespace.

Cluster Implementations - Init Trans
OMS-61587: Do not enter Init Trans for a cluster.
Rationale The storage definition should be based on the definitions of the dedicated tablespace.

Cluster Implementations - Max Trans
OMS-61588: Do not enter Max Trans for a cluster.
Rationale The storage definition should be based on the definitions of the dedicated tablespace.

Cluster Implementations - Percent Free
OMS-42152: Specify values for Pctfree for a Cluster.
Rationale Otherwise, the default is used. The figure required is dependent of the DML behavior within the cluster, and should therefore explicitly be considered.

Note: The figure 10% for Pctfree has proven to be efficient in most situations.
Cluster Implementations - Percent Used
OMS-61586: Specify values for Pctused for a Cluster.
Rationale Otherwise, the default is used. The figure required is dependent of the DML behavior within the cluster, and should therefore explicitly be considered.

Note: The figure 70% for Pctused has proven to be efficient in most situations.
User Object Index Storages - Tablespace
OMS-42162: Put the cluster index in a tablespace based on its expected size. Use the same tablespace as the cluster data when the extent sizes of the cluster and the index are the same.
Rationale When the cluster index is significantly smaller than the cluster, putting it in the same tablespace would waste space. When the cluster index has the same extent size as the cluster, putting them together in one tablespace makes space administration for the cluster easier.

User Object Index Storages - Storage Definition
OMS-42161: Do no enter a Storage Definition for a cluster index.
Rationale The Storage Definition is defined via the dedicated tablespace.

User Object Index Storages - Init Trans
OMS-42163: Do not enter Init Trans for a cluster index.
Rationale The storage definition should be based on the definitions of the dedicated tablespace.

User Object Index Storages - Percent Free
OMS-61589: Do not enter Percent Free for a cluster index.
Rationale The storage definition should be based on the definitions of the dedicated tablespace.

Index Storages - Tablespace
OMS-42309: Assign a tablespace to each index, based in the size of the index, and the estimated rebuild frequency. Group the indexes with similar sizes and rebuild frequencies together.
Rationale This allows for better space management.

Index Storages - Init Trans
OMG-10069: Use for Init Trans a default value of 3 for the index implementations.
Rationale This is based on best practices. Using this figure ensures in most scenario's that most net storage space is maintained. If the figure is set too low, you encounter locking problems.

Index Storages - Max Trans
OMS-61584: Do not limit the Max Trans for an index.
Rationale Limiting the maximum number of transaction slots is hardly ever useful. Only if a large amount of transactions is expected once for a table, this might be useful to prevent that unnecessary space is taken by transaction slots. Transaction slots are never released for rows when they once have been used. If the figure is set too low, a higher amount of transactions is required than available transaction slots. This results in locking problems.

Index Storages - Percent Free
OMS-42312: Specify a value for Pctfree for indexes.
Rationale Otherwise, the default is used. The figure required is dependent of the DML behavior against that table, and should therefore explicitly be considered.

Tablespaces - Name
OMS-42642: Use tablespace names with eight or fewer characters.
Rationale This is according to the OFA standards.

Tablespaces - Storage Definition
OMS-42643: Each tablespace should have a default storage assigned to it.
Rationale The default storage definition is used for all segments residing within this tablespace.

Tablespaces - Comment
OMS-42644: Enter a short sentence indicating the use of the tablespace.
Rationale This makes it obvious what is the usage of the tablespace.

Application Logic
OMS-61639: Do not put actual validation code in event triggers. Rather, in the event trigger make calls to routines that have been defined as named routines, custom CAPI services, or pl/sql library routines. This should always be a single call.
Rationale Application logic that is entered at event level is not reusable. By putting the application logic in a separate procedure or function, this logic can be called from other events, procedures and functions.
Example The following code might be entered for the When-Validate-Record event.
emp.when_validate_record;

OMS-61641: If you are adding named routines to the module in Oracle Designer, put all named routines at module level.
Rationale In this way all named routines will be accessible from one place, giving a good overview of the module logic and hence supporting maintenance.

OMS-61646: When the named routines for a module are being put in a module specific library, group all named routines that are specific to one module component into the same package. If a named routine is not specific to a module component, put that routine in a form wide package.
Rationale From an ?object oriented? perspective it makes sense to group all named routines that are specific to a module component or items within that module component into one package. Having too many separate routines in a library has a negative impact on the overview of the code. With Developer one can easily find out what routines are in a package by expanding the subprograms tree of the package specification.

Reusable Lists of Values - Name
OMS-61702: When naming a re-usable LOV component, use the following conventions.
  • If the LOV displays the primary key:
    [APP]_[TABLE ALIAS]_PK_LOV;
  • If the LOV displays the unique key:
    [APP]_[TABLE ALIAS]_UK#_LOV;
  • otherwise: [APP]_[TABLE ALIAS]_[LOGICAL NAME];
where APP is the application short name, TABLE ALIAS is the alias of the base table, # is the number of the corresponding unique key, and LOGICAL NAME describes the purpose of the LOV in one or two words.
Rationale This allows developers to quickly identify which re-usable LOV component to use in a given situation.
Example HSD_EMP_PK_LOV, HSD_EMP_UK1_LOV, HSD_EMP_MANAGERS_LOV

All Lists of Values - Title
OMG-10048: The LOV Title is used as the title in the list of values window. Such titles should start with the verb Find (or a language equivalent), followed by the object name.
Rationale Lists of values are always used to find objects, and the window title should reflect that.
Example Find Departments

Application Logic - Event - Name
OMS-61640: Name the user defined code segment of a module event after the procedure or function that is being called. In the rare case that there is no such call use a logical name expressing the purpose of the user defined code segment.
Rationale This makes it easy to identify the purpose of user defined code segments of events, without needing to open the PL/SQL logic editor.

Application Logic - Named Routine - Routine Name
OMS-61642: Give a module named routine, as entered in Designer, a logical name expressing the purpose of the routine.
Rationale This allows the routines to be easily identified, without needing to review the code.
Example emp_increase_salary
dep_call_employee_report

OMS-61643: Do not include the name of the event trigger in the name of a named routine, unless it is an event handling routine.
Rationale If you need to move the call to another event or to multiple events, the name becomes invalid.
Example Do not use:
emp_increase_salary_wbp (when-button-pressed)
emp_chk_hire_date_wvi (when-validate-item)

OMS-61644: Prefix named routines that are specific to a module component with the name of the module component. Prefix named routines that are specific to an item with the name of the module component in which that item is found.
Rationale In this way it is easy to identify to what module component a named routine relates. Further, named routines in a module specific library will use the format module_component.name while named routines in the module will use the format module_component_name. This means that when reviewing code segments of module events, it will immediately be clear where to look for the routine that is being called.
There is not much added value in naming a routine that is specific for one item, after that item. Such a name would easily become too large, leaving no room to express the purpose of the routine.
Example emp_increase_salary
dep_call_employee_report

Application Logic - Named Routine - Type
OMS-61645: Do not create packages as named routines in a module in Designer.
Rationale The only way to find out what is in such a package would be to open the PL/SQL logic editor.

Application Logic - Named Routine - Routine Name
OMS-61647: Name a package in a module specific library that is specific to a module component, after that module component. Name the package that contains the other routines after the form module.
Rationale By using this naming convention it will be easy to identify to what level, and in case of a module component specific package, to what module component a package relates to.
Example EMP package for the module component EMP
DEP package for the module component DEP
HSD0001F package for the form HSD0001F