Data Types

  1.       Char
  2.       Varchar2
  3.       Date
  4.       Number
  5.       Long
  6.       Raw
  7.       Long Raw
  8.       LOB
  9.       CLOB
  10.       BLOB
  11.       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  If the user enters a value shorter than the specified length then the database adds blank-pads to the fixed length.
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.

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.

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.