Technical data

Column Data Type and Constraints
3-6 Oracle Utilities Meter Data Management Database Administrator’s Guide
Column Data Type and Constraints
This section discusses the rules applied to column data type and constraints, and the attributes that
are associated with these objects.
User Defined Code
User Defined Codes are defined as CHAR type. The length can vary by the business requirements
but a minimum of eight characters is recommended. You will find columns defined in less than
eight characters but with internationalization in mind new columns should be defined as
CHAR(10) or CHAR(12). Also note that when the code is referenced in the application the
descriptions are shown to users in most cases.
System Assigned Identifier
System assigned random numbers are defined as CHAR type. The length of the column varies to
meet the business requirements. Number type key columns are used when a sequential key
assignment is allowed or number type is required to interface with external software. For example,
Notification Upload Staging ID is a Number type because most EDI software uses a sequential
key assignment mechanism. For sequential key assignment implementation, the DBMS sequence
generator is used in conjunction with Number Type ID columns.
Date/Time/Timestamp
Date, Time and Timestamp columns are defined physically as DATE in Oracle. Non-null
constraints are implemented only for the required columns.
Number
Numeric columns are implemented as NUMBER type in Oracle. The precision of the number
should always be defined. The scale of the number might be defined. Non-null constraints are
implemented for all number columns.
Fixed Length/Variable Length Character Columns
When a character column is a part of the primary key of a table define the column in CHAR type.
For the non-key character columns, the length should be the defining factor. If the column length
should be greater than 10, use VARCHAR2 type in Oracle.
Null Column Support
With Oracle Utilities Application Framework Single Fix 12874623 the application supports
nullable columns. The NULLABLE_SW on CI_MD_TBL_FLD can be turned ON for columns
which are intended to have null values. The framework will then be able to write null values into
those columns. Currently the support is only for Java based entities. Columns with DATE, TIME
or TIMESTAMP, are also supported as nullable.
Cache and Key Validation Flags
By default, the Cache Flag is set to NONE. For most of the admin tables the CACHE Flag should
be 'Cached for Batch'. This specifies that the table is cached as L2 cache to reduce database trips.
By default the Key Validation Flag is set to ALL. For tables which have the user defined keys, the
KEY_VALIDATION_FLG should be set as 'ALL'. This checks the existence of the key before
inserting a new one.