|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
There are two kinds of character datatypes:
Stores column values as a fixed number of characters and pads shorter entries with trailing spaces.
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
, 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 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:
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.
Coin Database - Exercise
Oracle stores all date columns in the same internal format.
You can specify that a date column contains
DATE, which includes a year, month, and day;
TIME, which includes hours, minutes, seconds, and a fractional component for milliseconds; or
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.
Table Creation Basics - Quiz
Click the Exercise link below to practice creating a script to create the tables for the
Coin Database - Exercise