Oracle Data Types
Each column within a table is defined as an Oracle data type and instructs Oracle how to store the data, the SQL data types can be categorized by the following
Character Data Types |
|
| char | can have a length specification after the data type (default = 1) once created they are fixed. The column will always use the amount of space specified and will use padding if the values are less than the specified amount. |
| varchar2 and varchar | can have a length specification after the data type, this data type is more efficient than a char as the space used will only be the length of the data entered, up to a maximum value that was stated during creation. varchar is the same as varchar2 varchar2, varchar have a limit of 2,000 characters |
| nchar and nvarchar2 | can be used to use character data from many different languages, including chinese or japanese, they require 2 bytes of storage for each character. Oracle will allocate the appropriate amount of storage required when you create the column. nchar and nvarchar2 have a limit of 4,000 |
| String | is a subtype of varchar2 and stores variable length strings in bytes and characters up to 4,000 characters in length. |
| long | The above character types have limits were the limit of a long is 2GB of data in a single column. However long has now been superceded by clob and nclob. |
| blob, clob and nclob | blob holds binary information (images), clob holds character information (text) and nclob holds double byte information (languages text like nvarchar2). They can hold upto 128TB's of data in a single column in oracle 10g. See Large OBjects for more information |
| bfile | a bfile data type points to a storage location outside the oracle database internal storage, it can point to jpg's, giff's ,etc See Large OBjects for more information |
Numeric Data Types |
|
| number | basic number data type, it has up to 38 digits of precision. the syntax is number(p,s) p = precision upto 38 |
| binary | You can used either binary_float or binary_double these data types have no limits however they may lose some accuracy. |
| float | data type for ANSI compliance. |
Date Data Types |
|
| date | Includes day, month, year, hour, minute and seconds |
| timestamp | includes the above and fractional seconds. |
| timestamp with local timezone | same as timestamp but includes the local timezone of either the database server or client. |
| interval year to month | can store an interval of years and months |
| interval day to second | can store an interval of days, hours, minutes and seconds. |
Raw Data Types |
|
| raw | used to keep the data in its raw format as oracle normally converts a value to an internal representation for storage. |
| long raw | used to keep the data in its raw format as oracle normally converts a value to an internal representation for storage. |
Row Data Type |
|
rowid |
hexadecimal string representing the unique address of a row in it's table |
urowid |
hexadecimal string representing the logical address of a row of an index-organized table |
Other Data Types |
|
| refcursor | stores a cursor returned by a PL/SQL block, which can contain an array of a structure. The structure can be dynamic and may implement a structure defined in the data catalog or in query. |
| own data types | you have the ability to create your own data types and object types |
| special data types | there a number of special data types that deal with XML, spatial data and media data. |