Managing Tables   «Prev  Next»

Lesson 3 Column datatypes
ObjectiveWork with basic Oracle datatypes

Oracle Column Datatypes

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

Character 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 Large Objects.

Overview of Numeric Datatypes

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.

Date Datatypes

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 lessondemonstrates how to create a table using the Schema Manager.

Coin Database - Exercise

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

Table Creation Basics - Quiz

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