Oracle Column Datatypes: Character, Numeric and Date

As stated in the previous lesson, all columns in a table must be defined by a datatype. There are three categories of datatypes:

1. character datatypes
2. numeric datatypes
3. date datatypes

There are two kinds of character datatypes:

CHAR	Stores column values as a fixed number of characters and pads shorter entries with trailing spaces.
VARCHAR2	Stores only the characters entered.

Both of these character datatypes have the maximum length specified for the column in parentheses after one of these datatype keywords.
Oracle also supports NLS datatypes. NLS stands for National Language Subset.
Either NLS datatype: NCHAR or NVARCHAR2 allows the user to include NLS characters for a column in a table. There are several other datatypes that can contain character values. These datatypes are referred to as LOBs, for

The CHAR datatype can store a maximum of 2,000 characters, while the VARCHAR datatype has a limit of 4,000 characters. If you want to store larger amounts of data, you can specify one of the following LOB datatypes: The character datatypes store character (alphanumeric) data in strings, with byte values corresponding to the character encoding scheme, generally called a character set or code page. The database's character set is established when the database is created. Examples of character sets are

1. 7-bit ASCII (American Standard Code for Information Interchange),
2. EBCDIC (Extended Binary Coded Decimal Interchange Code),
3. and Unicode UTF-8.

Oracle supports both single-byte and multibyte encoding schemes.

BLOB	Stores up to 4 gigabytes of binary data. Oracle does not interpret the values entered into a BLOB column.
CLOB	Stores up to 4 gigabytes of character data. Oracle interprets data in a CLOB as characters.
NLOB	Stores up to 4 gigabytes of NLS data.

Versions of Oracle prior to Oracle8 also support the LONG and LONG RAW datatypes, which served the same function. There are many restrictions on the use of LOBs, such as the inability to select or sort on the values within the LOB column. To store large objects and have more control over their content, you can use the Oracle interMedia^[1] option. Out-of-line data is also used to store large objects (LOBs) that are stored internally (as opposed to BFILEs, which are pointers to LOBs external to the database). In the next chapter, you willsee how to create and manipulate LOB values. The combination of object types, object views, collectors, and LOBs provides a strong foundation for the implementation of an object-relational database application.

Four types of LOBs are supported:

LOB Datatype	Description
BLOB Binary LOB.	Binary data stored in the database.
CLOB Character LOB.	Character data stored in the database.
BFILE Binary File.	Read-only binary data stored outside the database, the length of which is limited by the operating system.
NCLOB	A CLOB column that supports a multibyte character set.

You can create multiple LOBs per table. For example, suppose you want to create a PROPOSAL table to track formal proposals you submit. Your proposal records may consist of a series of word processing files and spreadsheets used to document and price the proposed work. The PROPOSAL table will contain VARCHAR2 datatypes (for columns such as the name of the proposal recipient) plus LOB datatypes (containing the word processing and spreadsheet files).
Note: You cannot create multiple LONG or LONG RAW columns in a single table.
The create table command in the following listing creates the PROPOSAL table:

create table PROPOSAL
(Proposal_ID NUMBER(10),
Recipient_Name VARCHAR2(25),
Proposal_Name VARCHAR2(25),
Short_Description VARCHAR2(1000),
Proposal_Text CLOB,
Budget BLOB,
Cover_Letter BFILE,
constraint PROPOSAL_PK primary key (Proposal_ID));

The numeric datatypes store positive and negative fixed and floating-point numbers, zero, infinity, and values that are the undefined result of an operation (that is, is "not a number" or NAN).
Oracle stores all numeric values in the same format, this format contains 38 significant digits. A numeric column is defined as a NUMBER. The NUMBER datatype can take one qualifier for the precision of the number and one for the scale of the number. The precision and scale of a NUMBER datatype are listed in parentheses following the keyword NUMBER, as in:

colname NUMBER(15,2)

If you do not specify a precision and scale, the numeric column is assumed to have a precision of 38 and a scale of 0. Defining a column as a numeric datatype does not limit the values that can be placed in the column. Keep in mind that the datatype defines how Oracle will interpret data in a column; it does not control the actions of users. If a user inserts a character value into a table, Oracle interprets the value as if it were a number. Oracle automatically converts character strings made up of digits into numbers. For instance, the character string of “123” will be properly evaluated as 123. However, if a user inserts a non-numeric string into a numeric column, such as “ABC,” the Oracle database will interpret the ASCII values for the string as a number, which is rarely meaningful and never correct.

Oracle stores all date columns in the same internal format. You can specify that a date column contains

1. a DATE, which includes a year, month, and day;
2. a TIME, which includes hours, minutes, seconds, and a fractional component for milliseconds; or
3. a DATETIME, which includes both of these values.

Each column in a table must be assigned a datatype which controls how the data is stored and interpreted.
The next lesson demonstrates how to create a table using the Schema Manager.

Click the Exercise link below to practice creating a script to create the tables for the COIN database.
Coin Database - Exercise

Click the Quiz link below to answer questions about table creation.
Table Creation Basics - Quiz

[1]Oracle interMedia: An option for the Oracle database designed for handling specific types of large data (such as images or spatial data) and text.