Oracle Basic Data Types
Table Fundamentals:-
A table is a database objects that holds the data. The simplest way to think of a table is as a spreadsheet. Now column of the table has a specific data type associated with them. For e.g.:- if a column has a number data type than we can’t insert the character in that column.
Oracle Basic Data Types:-
| Data Type | Description |
| CHAR(size) | It is used to store the character string values of fixed length. The size in the bracket determines the number of the characters it can hold in a cell. The maximum size this data type can hold is 255 characters.
The major problem is that it statically allocates the space which means if you assign 40 spaces while you are using 20 characters only than your 20 spaces will be left and not used. However it has advantage as it is faster than variable character (varchar). Sometimes it can be faster than varchar , upto 50% even so in case you sure of characters it is wiser to use char data type. |
| VARCHAR/VARCHAR2(size) | It is used to store the variable length alphanumeric data. It is more flexible than char data type. The maximum value this data type can hold is upto 4000 characters. It represents the data of type string, yet stores this data in variable length format. Varchar is much wiser choice than char due to its variable length format characteristic. |
| DATE | It is used to represent the date and time. The standard format is DD-MON-YY i.e. 21-jun-10. You can change this standard by using the appropriate function. DateTime stores the time in 24 hours format. |
| Number(P,S) | The NUMBER data type is used to store numbers (fixed or floating point). Numbers of any magnitude can be stored upto a 38 digits of precision. Valid values are from 0, and positive and negative numbers with magnitude. There are two important terms used in number data type context these are:
1. PRECISION determines maximum length of data. 2. SCALE determines number of places to the right of the decimal. |
| LONG | It is used to store variable length character strings containing upto 2GB. LONG data can be used to store arrays of binary data in ASCII format. Only one LONG value can be stored per table. It cannot be used in subqueries, functions, and expressions. A table containing LONG value cannot be clustered. |
| RAW/ LONG RAW | This data type is used to store binary data, such as digitized picture of image. Data in these columns are stored without any further conversion. RAW data types can have a maximum of 255 bytes. It can contain upto 2GB data. Values stored in these columns cannot be indexed. |
| Timestamp | Includes year, month, day, hour, minute and seconds. Other similar data types are Timestamp with time zone which have time zone also with fractional seconds precision. |
| Blob, Clob | LOB locators what point to large binary object/ large character object in the database. These have capacities of upto 4 gigabytes. |
