Character Data Types

Character data types store character (alphanumeric) data either in the database character set or the UTF-16 format. Character data is stored in strings with byte values. The byte values correspond to one of the database character sets defined when the database is created. TimesTen supports both single and multibyte character sets.

The character types are as follows:

CHAR

The CHAR type specifies a fixed length character string. If you insert a value into a CHAR column and the value is shorter than the defined column length, then TimesTen blank-pads the value to the column length. If you insert a value into a CHAR column and the value is longer than the defined length, TimesTen returns an error.

By default, the column length is defined in bytes. Use the CHAR qualifier to define the column length in characters. The size of a character ranges from one byte to four bytes depending on the database character set. The BYTE and CHAR qualifiers override the NLS_LENGTH_SEMANTICS parameter setting. See "ALTER SESSION" for more information about NLS_LENGTH_SEMANTICS. Also see Setting Globalization Support Attributes in Oracle TimesTen In-Memory Database Operations Guide.

Note:

With the CHAR type, a zero-length string is interpreted as NULL. With the TT_CHAR type, a zero-length string is a valid non-NULL value. Both CHAR and TT_CHAR use blank padded comparison semantics. The TT_CHAR type is supported for backward compatibility.

The following example creates a table. Columns are defined with type CHAR and TT_CHAR. Blank padded comparison semantics are used for these types. 

Command> CREATE TABLE typedemo (name CHAR (20), nnme2 TT_CHAR (20));
Command> INSERT INTO typedemo VALUES ('SMITH     ','SMITH     ');
1 row inserted.
Command> DESCRIBE typedemo;
Table USER.TYPEDEMO:
  Columns:
    NAME                            CHAR (20)
    NAME2                           TT_CHAR (20)
1 table found.
(primary key columns are indicated with *)
Command> SELECT * FROM typedemo;
< SMITH     , SMITH      >
1 row found.
Command> -- Expect 1 row found; blank-padded comparison semantics
Command> SELECT * FROM typedemo WHERE name = 'SMITH';
< SMITH     , SMITH      >
1 row found.
Command> SELECT * FROM typedemo WHERE name2 = 'SMITH';
< SMITH     , SMITH      >
1 row found.
Command> -- Expect 0 rows; blank padded comparison semantics.
Command> SELECT * FROM typedemo WHERE name > 'SMITH';
0 rows found.
Command> SELECT * FROM typedemo WHERE name2 > 'SMITH';
0 rows found.

The following example alters table typedemo adding column name3. The column name3 is defined with character semantics. 

Command> ALTER TABLE typedemo ADD COLUMN name3 CHAR (10 CHAR);
Command> DESCRIBE typedemo;
Table USER.TYPEDEMO:
  Columns:
    NAME                            CHAR (20)
    NAME2                           TT_CHAR (20)
    NAME3                           CHAR (10 CHAR)
1 table found.

NCHAR

The NCHAR data type is a fixed length string of two-byte Unicode characters. NCHAR data types are padded to the specified length with the Unicode space character U+0020 SPACE. Blank-padded comparison semantics are used.

Note:

With the NCHAR type, a zero-length string is interpreted as NULL. With the TT_NCHAR type, a zero-length string is a valid non-null value. Both NCHAR and TT_NCHAR use blank padded comparison semantics. The TT_NCHAR type is supported for backward compatibility.

The NCHAR data type is encoded as UTF-16.

The following example alters table typedemo to add column Name4. Data type is NCHAR. 

Command> ALTER TABLE typedemo ADD COLUMN Name4 NCHAR (10);
Command> DESCRIBE typedemo;

Table USER.TYPEDEMO:
  Columns:
    NAME                            CHAR (20)
    NAME2                           TT_CHAR (20)
    NAME3                           CHAR (10 CHAR)
    NAME4                           NCHAR (10)
1 table found.

VARCHAR2

The VARCHAR2 data type specifies a variable length character string. When you define a VARCHAR2 column, you define the maximum number of bytes or characters. Each value is stored exactly as you specify it. The value cannot exceed the maximum length of the column.

You must specify the maximum length. The minimum must be at least one byte. Use the CHAR qualifier to specify the maximum length in characters. For example, VARCHAR2(10 CHAR).

The size of a character ranges from one byte to four bytes depending on the database character set. The BYTE and CHAR qualifiers override the NLS_LENGTH_SEMANTICS parameter setting. See "ALTER SESSION" for more information on NLS_LENGTH_SEMANTICS. Also see Setting Globalization Support Attributes in Oracle TimesTen In-Memory Database Operations Guide.

The NULL value is stored as a single bit for each nullable field within the row. An INLINE VARCHAR2(n) whose value is NULL takes (null bit) + four bytes + n bytes of storage, or n more bytes of storage than a NOT INLINE VARCHAR2(n) whose value is NULL. This storage principal holds for all variable length data types: TT_VARCHAR, TT_NVARCHAR, VARCHAR2, NVARCHAR2, VARBINARY.

Note:

  • Do not use the VARCHAR data type. Use VARCHAR2. Even though both data types are currently synonymous, the VARCHAR data type may be redefined as a different data type with different semantics.

  • With the VARCHAR2 type, a zero-length string is interpreted as NULL. With the TT_VARCHAR type, a zero-length string is a valid non-NULL value. VARCHAR2 uses nonpadded comparison semantics. TT_VARCHAR uses blank-padded comparison semantics. The TT_VARCHAR type is supported for backward compatibility.

The following example alters table typedemo, adding columns name5 and name6. The name5 column is defined with type VARCHAR2. The name6 column is defined with TT_VARCHAR. The example illustrates the use of nonpadded comparison semantics with column name5 and blank-padded comparison semantics with column name6: 

Command> ALTER TABLE typedemo ADD COLUMN name5 VARCHAR2 (20);
Command> ALTER TABLE typedemo ADD COLUMN name6 TT_VARCHAR (20);
Command> DESCRIBE typedemo;
Table USER.TYPEDEMO:
  Columns:
    NAME                            CHAR (20)
    NAME2                           TT_CHAR (20)
    NAME3                           CHAR (10 CHAR)
    NAME4                           NCHAR (10)
    NAME5                           VARCHAR2 (20) INLINE
    NAME6                           TT_VARCHAR (20) INLINE
1 table found.
(primary key columns are indicated with *)
Command> -- Insert SMITH followed by 5 spaces into all columns
Command> INSERT INTO typedemo 
         VALUES ('SMITH     ', 
                 'SMITH     ', 
                 'SMITH     ', 
         'SMITH     ',
         'SMITH     ',
         'SMITH');
1 row inserted.
Command> -- Expect 0; Nonpadded comparison semantics
Command> SELECT COUNT (*) FROM typedemo WHERE name5 = 'SMITH';
< 0 >
1 row found.
Command> -- Expect 1; Blank-padded comparison semantics
Command> SELECT COUNT (*) FROM typedemo WHERE name6 = 'SMITH';
< 1 >
1 row found.
Command> -- Expect 1; Nonpadded comparison semantics
Command> SELECT COUNT (*) FROM typedemo WHERE name5 > 'SMITH';
< 1 >
1 row found.
Command> -- Expect 0; Blank-padded comparison semantics
Command> SELECT COUNT (*) FROM typedemo WHERE name6 > 'SMITH';
< 0 >
1 row found.

NVARCHAR2

The NVARCHAR2 data type is a variable length string of two-byte Unicode characters. When you define an NVARCHAR2 column, you define the maximum number of characters. Each value is stored exactly as you specify it. The value cannot exceed the maximum length of the column.

Note:

With the NVARCHAR2 type, a zero-length string is interpreted as NULL. With the TT_NVARCHAR type, a zero-length string is a valid non-NULL value. NVARCHAR2 uses nonpadded comparison semantics. TT_NVARCHAR uses blank-padded comparison semantics. The TT_NVARCHAR type is supported for backward compatibility.

The NVARCHAR2 data type is encoded as UTF-16.

The following example alters table typedemo to add column name7. Data type is NVARCHAR2. 

Command> ALTER TABLE typedemo ADD COLUMN Nnme7 NVARCHAR2 (20);
Command> DESCRIBE typedemo;
Table USER1.TYPEDEMO:
  Columns:
    NAME                            CHAR (20)
    NAME2                           TT_CHAR (20)
    NAME3                           CHAR (10 CHAR)
    NAME4                           NCHAR (10)
    NAME5                           VARCHAR2 (20) INLINE
    NAME6                           TT_VARCHAR (20) INLINE
    NAME7                           NVARCHAR2 (20) INLINE
1 table found.