Understanding Application Designer Field Length Semantics
The database character set determines the way that PeopleTools interprets the column length that is defined in Application Designer.
If you create a Unicode database, the field length, as shown in Application Designer, indicates the maximum number of Unicode BMP characters that are permitted in the field, regardless of the Unicode encoding that is used by the database. Some database platforms, such as Oracle with byte semantics, use byte lengths to measure column sizes when operating in a Unicode database, while others use character lengths.
When the database uses byte-sized column lengths, the PeopleSoft system sizes the database columns based on the worst-case ratio between bytes and characters in the Unicode encoding that is used by your database. For example, if the AL32UTF8 character set is used by Oracle with byte semantics, the worst-case character-to-byte ratio when running against an Oracle Unicode database is 1:3. So, column size is tripled when creating a Unicode database on Oracle. A field that is defined in Application Designer as a CHAR(10) is created on an Oracle Unicode database with a type of VARCHAR2(30). This tripling of the maximum column size does not affect the actual size of the database, because variable length character fields do not reserve space in the database.
Other database platforms use character-based column lengths whose sizes represent the maximum number of Unicode characters instead of bytes that may be stored. Examples of this implementation are the NCHAR data type in Microsoft SQL Server and the GRAPHIC data type in DB2 UDB for Linux, Unix, and Microsoft Windows.
If you create a non-Unicode database, the field length in Application Designer represents the number of bytes that are permitted in the field, based on the character set that you used to create the database. Therefore, a PeopleSoft Unicode database enables you significantly more space for character data within the database when dealing with ideographic languages, such as Japanese, that require one or more byte to store per character.
The following tables show some of the possible database encodings for database platforms that the PeopleSoft system supports in Unicode and non-Unicode and their effects on database column sizes. Each table shows the database representation and the worst case number of characters allowed in the character field for a character field defined in Application Designer with a length of 10.
This table shows the information for an Oracle database with byte semantics (used by PeopleSoft 8.9 applications and earlier):
| Database Character Set | Database Representation | Number of Characters |
|---|---|---|
|
Unicode (AL32UTF8) |
VARCHAR2(30) |
10 |
|
Any SBCS |
VARCHAR2(10) |
10 |
|
Shift-JIS (JA16SJIS or JA16SJISTILDE) |
VARCHAR2(10) |
5 |
This table shows the information for an Oracle database with character semantics (used by PeopleSoft 9.0 applications and later):
| Database Character Set | Database Representation | Number of Characters |
|---|---|---|
|
Unicode (AL32UTF8) |
VARCHAR2(10) |
10 |
|
*Any SBCS |
VARCHAR2(10) |
10 |
|
*Shift-JIS (JA16SJIS or JA16SJISTILDE) |
VARCHAR2(10) |
10 |
*: SBCS and Shift JIS are supported only when using BYTE length semantics, while Unicode is supported using CHAR length semantics. SBCS and Shift JIS are listed in the above table for comparison purposes only.
This table shows the information for an Microsoft SQL Server database with VARCHAR semantics:
| Database Character Set | Database Representation | Number of Characters |
|---|---|---|
|
Unicode (UCS-2) |
NVARCHAR(10) |
10 |
|
Any SBCS |
VARCHAR(10) |
10 |
|
Shift-JIS (CP932) |
VARCHAR(10) |
5 |
This table shows the information for a Microsoft SQL Server database with CHAR semantics:
| Database Character Set | Database Representation | Number of Characters |
|---|---|---|
|
Unicode (UCS-2) |
NCHAR(10) |
10 |
|
Any SBCS |
CHAR(10) |
10 |
|
Shift-JIS (CP932) |
CHAR(10) |
5 |
This table shows the information for all other databases:
| Database Character Set | Database Representation | Number of Characters |
|---|---|---|
|
Any SBCS |
CHAR(10) |
10 |