Oracle Data types – Part III
Posted by Ravi Varma Thumati on October 12, 2009
When you create a table in Oracle, a few items should be important, not only do you have to give each table a name (e.g. employee, customer), you must also list all the columns or fields (e.g. First_name, Mname, Last_name) associated with the table. You also have to specify what type of information that table will hold to the database. For example, the column Empno holds numeric information. An Oracle database can hold many different types of data.
The following is a list of datatypes available in Oracle.
Character Datatypes
Data Type |
Oracle 9i |
Oracle 10g |
Oracle 11g |
Explanation |
char(size) | Maximum size of 2000 bytes. | Maximum size of 2000 bytes. | Maximum size of 2000 bytes. | Where size is the number of characters to store. Fixed-length strings. Space padded. |
nchar(size) | Maximum size of 2000 bytes. | Maximum size of 2000 bytes. | Maximum size of 2000 bytes. | Where size is the number of characters to store. Fixed-length NLS string Space padded. |
nvarchar2(size) | Maximum size of 4000 bytes. | Maximum size of 4000 bytes. | Maximum size of 4000 bytes. | Where size is the number of characters to store. Variable-length NLS string. |
varchar2(size) | Maximum size of 4000 bytes. | Maximum size of 4000 bytes. | Maximum size of 4000 bytes. | Where size is the number of characters to store. Variable-length string. |
long | Maximum size of 2GB. | Maximum size of 2GB. | Maximum size of 2GB. | Variable-length strings. (backward compatible) |
raw | Maximum size of 2000 bytes. | Maximum size of 2000 bytes. | Maximum size of 2000 bytes. | Variable-length binary strings |
long raw | Maximum size of 2GB. | Maximum size of 2GB. | Maximum size of 2GB. | Variable-length binary strings. (backward compatible) |
Numeric Datatypes
Data Type |
Oracle 9i |
Oracle 10g |
Oracle 11g |
Explanation |
number(p,s) | Precision can range from 1 to 38. Scale can range from -84 to 127. |
Precision can range from 1 to 38. Scale can range from -84 to 127. |
Precision can range from 1 to 38. Scale can range from -84 to 127. |
Where p is the precision and s is the scale.
For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal. |
numeric(p,s) | Precision can range from 1 to 38. | Precision can range from 1 to 38. | Precision can range from 1 to 38. | Where p is the precision and s is the scale.
For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal. |
float | ||||
dec(p,s) | Precision can range from 1 to 38. | Precision can range from 1 to 38. | Precision can range from 1 to 38. | Where p is the precision and s is the scale.
For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal. |
decimal(p,s) | Precision can range from 1 to 38. | Precision can range from 1 to 38. | Precision can range from 1 to 38. | Where p is the precision and s is the scale.
For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal. |
integer | ||||
int | ||||
smallint | ||||
real | ||||
double precision |
Date/Time Datatypes
Data Type |
Oracle 9i |
Oracle 10g |
Oracle 11g |
Explanation |
date | A date between Jan 1, 4712 BC and Dec 31, 9999 AD. | A date between Jan 1, 4712 BC and Dec 31, 9999 AD. | A date between Jan 1, 4712 BC and Dec 31, 9999 AD. | |
timestamp (fractional seconds precision) | fractional seconds precision must be a number between 0 and 9. (default is 6) | fractional seconds precision must be a number between 0 and 9. (default is 6) | fractional seconds precision must be a number between 0 and 9. (default is 6) | Includes year, month, day, hour, minute, and seconds.
For example: |
timestamp (fractional seconds precision) with time zone | fractional seconds precision must be a number between 0 and 9. (default is 6) | fractional seconds precision must be a number between 0 and 9. (default is 6) | fractional seconds precision must be a number between 0 and 9. (default is 6) | Includes year, month, day, hour, minute, and seconds; with a time zone displacement value.
For example: |
timestamp (fractional seconds precision) with local time zone | fractional seconds precision must be a number between 0 and 9. (default is 6) | fractional seconds precision must be a number between 0 and 9. (default is 6) | fractional seconds precision must be a number between 0 and 9. (default is 6) | Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone.
For example: |
interval year (year precision) to month |
year precision is the number of digits in the year. (default is 2) | year precision is the number of digits in the year. (default is 2) | year precision is the number of digits in the year. (default is 2) | Time period stored in years and months.
For example: |
interval day (day precision) to second (fractional seconds precision) |
day precision must be a number between 0 and 9. (default is 2)
fractional seconds precision must be a number between 0 and 9. (default is 6) |
day precision must be a number between 0 and 9. (default is 2)
fractional seconds precision must be a number between 0 and 9. (default is 6) |
day precision must be a number between 0 and 9. (default is 2)
fractional seconds precision must be a number between 0 and 9. (default is 6) |
Time period stored in days, hours, minutes, and seconds.
For example: |
Large Object (LOB) Datatypes
Data Type |
Oracle 9i |
Oracle 10g |
Oracle 11g |
Explanation |
bfile | Maximum file size of 4GB. | Maximum file size of 2^{32}-1 bytes. | Maximum file size of 2^{64}-1 bytes. | File locators that point to a binary file on the server file system (outside the database). |
blob | Store up to 4GB of binary data. | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage). | Stores unstructured binary large objects. |
clob | Store up to 4GB of character data. | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data. | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data. | Stores single-byte and multi-byte character data. |
nclob | Store up to 4GB of character text data. | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data. | Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data. | Stores unicode data. |
Rowid Datatypes
Data Type |
Oracle 9i |
Oracle 10g |
Oracle 11g |
Explanation |
rowid | The format of the rowid is: BBBBBBB.RRRR.FFFFF
Where BBBBBBB is the block in the database file; |
The format of the rowid is: BBBBBBB.RRRR.FFFFF
Where BBBBBBB is the block in the database file; |
The format of the rowid is: BBBBBBB.RRRR.FFFFF
Where BBBBBBB is the block in the database file; |
Fixed-length binary data. Every record in the database has a physical address or rowid. |
urowid(size) | Universal rowid.
Where size is optional. |
Apart from oracle internal data types, user can create their own data type, which is used in database and other database object.
colon cleanse said
I wanted to thank you for this great learn!! I definitely enjoying every little little bit of it I have you bookmarked to take a look at new stuff you post