Oracle Data Types with Examples

HELLO Everybody!!!! Today, we will learn new and very useful topic from Oracle.This is fundamental one to know data types in Oracle. I hope you people aware of the term Data type in other programming languages such as C,C++,JAVA,.NET.But it is different from other programming languages.By the end of this article you people have complete knowledge on Oracle data type with examples.

Data Types:

When you identify  a column for a table,you need to provide a data type for the column.Since data stored in the database in the form tables.When you create a table,you must specify a data type for each of its columns.Table means the combination of rows and columns.Each column and constant in a SQL statement has a datatype which is associated with a specific storage format and a valid range of values. There are several data types available. They are

Example: 

1. VARCHAR2(SIZE)
2. CHAR(SIZE)
3. NUMBER(P,S)
4. DATE
5. LONG
6. CLOB
7. RAW(SIZE)
8. LONG RAW
9. BLOB
10. ROWID

1. VARCHAR2(size):

This data type is used to store variable-length character strings. A maximum size must be specified. The string range must be between 1 to 4000 bytes. To avoid possible changes in behavior, always use  varchar2 data type to store variable-length character.Minimum size : 1 and maximum size: 4000

2. CHAR(size):

This data type is used to store Fixed-length character strings.A default and minimum size is 1 and maximum size is 2000 bytes.When you create a table with a CHAR column,you must specify string length between 1 to 2000. If you give shorter value,then the blank-padded to the fixed length.If a value is too large, then oracle database returns error.

3. NUMBER(P,S):

This data types stores fixed and floating-point numbers that means numeric data. Number having precision p and scale s here precision means the total number of decimal digits and scale is the number of digits to the right of the decimal point.The precision can range from 1 to 38 and scale can range from -84 to 127.

For numeric columns,you can specify the column as:

column_name number;

4. DATE:

This data type stores data and time values to the nearest second  between January 1,4712 B.C., and December 31,9999 A.D. Oracle database uses its own internal format to store dates. The standard oracle date format is : DD-MON-YY.

5. LONG:

This data type is used to store variable-length character data up to 2GB. This is nothing but Text data.This data type mostly used in data dictionary to the text of view definitions. It is used along with Insert,update and select statements.

6. CLOB:

This data type is used for character data. This can store character data up to 4GB.

7. RAW(size):

The RAW data type is used  for data that is not converted when moving data between two different systems by Oracle database. These data type is intended for binary data of length size. A maximum size must be specified : maximum is 2000.

8. LONG RAW:

The RAW data type is used  for data that is not converted when moving data between two different systems by Oracle database. LONG RAW  can be used to store graphics,sound and documents. LONG RAW data can not be indexed but RAW data can be indexed. The maximum size is 2GB.

9.BLOB:

This data type stores unstructured binary data in the database. It can store up to 128 terabytes of binary data. This data type mostly used in transactions.

10. ROWID:

This is used to store the address of every row in the database. A base-64 number system representing the unique address of a row in its table.


NOTE: 

• A LONG column is not copied when a table is created using a subquery
• A LONG column can not be included in a GROUP BY or an ORDER BY clause
• Only one LONG column can be user per table.
• You might want to use a CLOB column rather than a LONG column.