Data Types
The following is a list of datatypes available in Oracle/PLSQL, which includes character, numeric, date/time, LOB and rowid datatypes.
Character Datatypes
The following are the Character Datatypes in Oracle/PLSQL:
| Data Type Syntax | 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 32KB in PLSQL.
|
Maximum size of 4000 bytes.
Maximum size of 32KB in PLSQL.
|
Maximum size of 4000 bytes.
Maximum size of 32KB in PLSQL.
| 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
The following are the Numeric Datatypes in Oracle/PLSQL:
| Data Type Syntax | 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
The following are the Date/Time Datatypes in Oracle/PLSQL:
| Data Type Syntax | 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(6) |
| 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(5) with time zone |
| 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:
timestamp(4) with local time zone |
| 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 year(4) to month |
| 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:
interval day(2) to second(6) |
Large Object (LOB) Datatypes
The following are the LOB Datatypes in Oracle/PLSQL:
| Data Type Syntax | Oracle 9i | Oracle 10g | Oracle 11g | Explanation |
|---|---|---|---|---|
| bfile | Maximum file size of 4GB. | Maximum file size of 232-1 bytes. | Maximum file size of 264-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
The following are the Rowid Datatypes in Oracle/PLSQL:
| Data Type Syntax | 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;
RRRR is the row in the block; FFFFF is the database file. |
The format of the rowid is: BBBBBBB.RRRR.FFFFF
Where BBBBBBB is the block in the database file;
RRRR is the row in the block; FFFFF is the database file. |
The format of the rowid is: BBBBBBB.RRRR.FFFFF
Where BBBBBBB is the block in the database file;
RRRR is the row in the block; FFFFF is 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.
|
No comments:
Post a Comment