- Char
- Varchar2
- Date
- Number
- Long
- Raw
- Long Raw
- LOB
- CLOB
- BLOB
- BFILE
Note:
v The first 4 data types are very
important and regularly used ones.
Char Data type:
Ø The Char data type is used when
fixed length character string is required.
Ø It can store alphanumeric values.
Ø The maximum size of char data type is
2000 bytes.
Ø Data should enter in single quotes.
Note:
v In case, if the user enters a value larger than the specified length then the database would return an error.
Varchar2 Data type:
Ø The varchar2 data type supports a
variable length character string.
Ø It also stores alphanumeric values.
Ø The maximum size of char data type
is 4000 bytes.
Ø Data should enter in single quotes.
Ø It occupies only that space for
which the data is supplied.
Ø Using varchar2 saves disk space when
compared to char.
Note:
v In case, if the user enters a value larger than the specified length then the database would return an error.
v In case, if the user enters a value larger than the specified length then the database would return an error.
Number Data type:
Ø It stores zero, positive and
negative fixed and floating point numbers.
Ø The general declaration is
Number
(P, S)
P
à It specifies
the precision, i.e., the total number of digits (1 to 38).
S
à It specifies
the scale, i.e., the number of digits to the right of the decimal point, can
range from -84 to 127.
Date Data type:
Ø It is used to store DATE and TIME
information.
Ø It allocates fixed length of 7 bytes
each for century, year, month, day, hour, minute and second.
Ø The default date format is
“dd-mon-yy” or “dd-mon-yyyy”.
Ø The date range provided by oracle is
January 1, 4712 BC to December 31, 4712 AD.
Timestamp Data type:
Ø It is an extension of the DATE data
type.
Ø It stores the data in the form of
century, year, month, date, hour, minute and second.
Long data type:
Ø This data type stores variable
length character strings.
Ø It is used to store very lengthy
text strings.
Ø The maximum size is 2 GB.
Ø The length of long values may be
limited by the memory available on the computer.
Note:
v A single table can contain only one long column.
v Objects types cannot be created on long column Attribute.
v Long columns cannot appear in where clauses or in Integrity constraints.
v Indexes cannot be created on long columns.
v Long can be Returned through a function, but not through a stored procedure.
v Stored procedures cannot accept long data as arguments.
v It can be declared in a Pl/Sql unit but cannot be referenced in SQL.
v A single table can contain only one long column.
v Objects types cannot be created on long column Attribute.
v Long columns cannot appear in where clauses or in Integrity constraints.
v Indexes cannot be created on long columns.
v Long can be Returned through a function, but not through a stored procedure.
v Stored procedures cannot accept long data as arguments.
v It can be declared in a Pl/Sql unit but cannot be referenced in SQL.
Raw data type:
Ø It is used to store binary data like
photos, signatures, thumb impressions etc.
Ø The maximum size is 2000 bytes.
Ø While using this data type the size
should be mentioned because by default it does not specify any size.
Ø Only storage and retrieval of data
are possible, manipulations of data cannot be done.
Ø This data type can be indexed.
Long Raw data type:
Ø It is also used to store binary
data.
Ø The maximum size is 2 GB.
Ø This data type cannot be indexed.
Large Object (LOB)
data types:
Ø The built in LOB data types are:
BLOB, CLOB and BFILE.
Ø BLOB and CLOB stores data
internally.
Ø The BFILE is LOB which stores the
data externally.
Ø The LOB data type can store large
and unstructured data like text, image, audio and video.
Ø The Maximum storage size is up to 4
GB.
Ø BLOB stores unstructured binary
large objects.
Ø CLOB stores single byte and multi byte
character data.