Lesson 3 | Column datatypes |
Objective | Work 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:
- character datatypes
- numeric datatypes
- 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
- a
DATE
, which includes a year, month, and day;
- a
TIME
, which includes hours, minutes, seconds, and a fractional component for milliseconds; or
- 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